2

Do anyone have hints, if I want to create crosstab columns from array, then insert the correction information into the crosstab?

For example, I have a table like below in my DB:

customer ---- activity_date ---- activity|

customer1 ---- 01-01-2016 ---- A|

customer1 ---- 01-03-2016 ---- B|

customer2 ---- 01-01-2016 ---- A|

When users request report from JAN 16 to MAR 16, the report should be shown like below:

customer ---- 01-2016 ---- 02-2016 ---- 03-2016 |

customer1 ---- Activity count: 1 ---- Activity count:0 ---- Activity count:1

customer2 ---- Activity count:1 ---- Activity count:0 ---- Activity count:0

total ---- Activity sum:2 ---- Activity sum:0 ---- Activity count:1

The current problem is that, as there is no data within Feb 2016, the column 02-2016 is missing in the report.

Is there any way (like scriptlet) to create an array as column, then tell the JasperReport the insert correct data when activity_date == column_date?

I'm using Jaspersoft Studio.

Alex K
  • 22,315
  • 19
  • 108
  • 236
Mora Christine
  • 23
  • 1
  • 3

1 Answers1

5

To show dates without dates (without activities in your case) you should pass the data with datasource. The JasperReports does not know anything about any ranges of dates or something else. It is just require a data.

The problems

  • The first problem is to get data in date range even the data is absent
  • And the second one - is to avoid showing in crosstab 'null' data

Solution

  • In case using DB you can use outter join and some date range 'generator' to show the data.

    We should solve this tasks:

    1. Getting the list of all dates (days) in some period of time. For different RDBMS the syntax will be different.

      For PostgreSQL you can find the solution in Getting date list in a range in PostgreSQL post
      For MySQL - MySQL how to fill missing dates in range & generate days from date range For SQL Server - SQL Server: How to select all days in a date range even if no data exists for some days

    2. Using left or right outter join.

    3. Sorting data by date and data you want

  • In case using JavaBean datasources you hould do the same - add dates you need (without data) and sort data. We can skip implementing the data sorting and ask the JasperReports engine to do it for us.

  • The crosstab has one 'feature' - we can't hide the row with condition. Even we set all properties for hiding all textFields - the empty row will be drawn. If we try to use a filter on Crosstab our additional rows will be disappear. I think the good idea (in this pitiful situation) is to have special name for such row (we can also replace 0 with something better). In my sample it will be 'Not set'.

The sample

I've used the PostgreSQL in this sample.

The report's template

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="crss_dates_group" pageWidth="842" pageHeight="595" orientation="Landscape" whenNoDataType="AllSectionsNoDetail" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true" uuid="6886d70f-dbf3-4dfa-bbee-d5bc70b1c45d">
    <style name="Crosstab Data Text" hAlign="Center"/>
    <subDataset name="dsDates" uuid="9b8d3c04-25f5-40f0-b116-a77f8d2f7445">
        <queryString language="SQL">
            <![CDATA[SELECT  activityName, to_char(generate_series, 'YYYY-MM-DD') AS activityDate
FROM myTable RIGHT OUTER JOIN (SELECT (generate_series('2010-07-18', '2010-07-29', '1 day'::interval))::date) fake
ON to_char(activityDateFromMyTable, 'YYYY-MM-DD')=to_char(generate_series, 'YYYY-MM-DD') ORDER BY 2, 1]]>
        </queryString>
        <field name="activityName" class="java.lang.String"/>
        <field name="activityDate" class="java.lang.String"/>
        <group name="activityDateGroup">
            <groupExpression><![CDATA[$F{activityDate}]]></groupExpression>
        </group>
    </subDataset>
    <title>
        <band height="79" splitType="Stretch">
            <crosstab>
                <reportElement x="0" y="0" width="802" height="79" uuid="d39eef3f-aada-406f-99ee-1d2ce2bde5c8"/>
                <crosstabDataset>
                    <dataset>
                        <datasetRun subDataset="dsDates" uuid="619c0498-512a-4f23-9f1e-6a5d7cfa986d">
                            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                        </datasetRun>
                    </dataset>
                </crosstabDataset>
                <rowGroup name="activityName" width="95" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{activityName}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents backcolor="#F0F8FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <textField>
                                <reportElement style="Crosstab Data Text" x="0" y="0" width="95" height="25" uuid="c25490b6-a836-41fb-a36c-a7ebb211bf03"/>
                                <textFieldExpression><![CDATA[$V{activityName} == null ? "Not set" : $V{activityName}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents backcolor="#BFE1FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <staticText>
                                <reportElement x="0" y="0" width="95" height="25" uuid="12efa463-c4a3-4120-b0e2-0664856cc616"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <text><![CDATA[Total by Date]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="activityDate" height="30" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{activityDate}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents backcolor="#F0F8FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <textField>
                                <reportElement style="Crosstab Data Text" x="0" y="0" width="61" height="30" uuid="5b931464-5a7a-4e57-a51a-3d687c0a4130"/>
                                <textFieldExpression><![CDATA[$V{activityDate}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents backcolor="#BFE1FF" mode="Opaque">
                            <box>
                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <staticText>
                                <reportElement x="0" y="0" width="50" height="30" uuid="227c77a6-b1c1-485f-95cf-95b43bc95920"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <text><![CDATA[Total by Activity]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="activityNameMeasure" class="java.lang.Integer" calculation="Count">
                    <measureExpression><![CDATA[$F{activityName}]]></measureExpression>
                </measure>
                <crosstabCell width="61" height="25">
                    <cellContents>
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="61" height="25" uuid="b8a8aacb-58d1-447a-9628-7f045b039f9f"/>
                            <textFieldExpression><![CDATA[$V{activityNameMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="61" height="25" rowTotalGroup="activityName">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="61" height="25" uuid="02e88c9a-e9cc-4674-9301-21676d3f33bc"/>
                            <textFieldExpression><![CDATA[$V{activityNameMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="50" columnTotalGroup="activityDate">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25" uuid="d39d1353-61a6-4041-96d6-2065bae0041b"/>
                            <textFieldExpression><![CDATA[$V{activityNameMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell rowTotalGroup="activityName" columnTotalGroup="activityDate">
                    <cellContents backcolor="#BFE1FF" mode="Opaque">
                        <box>
                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                        </box>
                        <textField>
                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25" uuid="09aa0d57-5cfa-4e78-af85-0e718c0fee44"/>
                            <textFieldExpression><![CDATA[$V{activityNameMeasure}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </title>
</jasperReport>

The result in iReport

The result via preview in iReport

Notes:

I've tried to hide empty row (with null) with help of "Remove Line With Blank", "Blank When Null", "Print When Expression" properties without any success. The digging in source codes does not help me too.

You can find more info about similar attempts to hide empty records in crosstab here:

Maybe it is a time to vote for this "new old" feature (hiding empty row) at Jaspersoft/Tibco community :)

Community
  • 1
  • 1
Alex K
  • 22,315
  • 19
  • 108
  • 236