CODEWITHSUNDEEP
jasper-reportscrosstab
Rathi Rao
Rathi Rao

Reputation: 297

How to format data in cells of crosstab using measures of different types

With regard to using crosstabs, is there a way to add any kind of formatting to the measures?

I have the data as follows.

enter image description here

I need to display col2, col4 as per the formatting specified for any geographical location. For example, in India, we show the date as 11/11/1986 and number as 1,00,000. Another country may show the data as 11.11.1986 and number as 1.00.000

How do I achieve this?

Also, while creating the crosstab, I do not add any calculated function to the crosstab, yet, it takes up the highest values for that combination of row group and column group. Why is this? Here is the picture of the same

enter image description here

And the jrxml for the same is as below

<?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="crosstabmeasure" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="e47b3f7d-1e00-4702-a900-be50a229f085">
    <style name="Crosstab_CH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Crosstab_CG" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Crosstab_CT" mode="Opaque" backcolor="#005FB3">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Crosstab_CD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <parameter name="Locale" class="java.lang.String">
        <defaultValueExpression><![CDATA["en-IN"]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[select * from internatioinlization]]>
    </queryString>
    <field name="col1" class="java.lang.String"/>
    <field name="col2" class="java.lang.Integer"/>
    <field name="col3" class="java.sql.Timestamp"/>
    <field name="col4" class="java.lang.String"/>
    <summary>
        <band height="211" splitType="Stretch">
            <crosstab>
                <reportElement x="0" y="0" width="555" height="211" uuid="6c5ccb8a-50fc-4158-b2ba-88d864aa1e43"/>
                <rowGroup name="col11" width="60">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{col1}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="60" height="20" uuid="86c09a7f-46b5-4d2f-ba87-715f99d832a8"/>
                                <textFieldExpression><![CDATA[$V{col11}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents>
                            <staticText>
                                <reportElement x="0" y="0" width="-2147483648" height="-2147483648" uuid="94fca0ea-1572-4d42-85d9-b06f92c73b6e"/>
                                <text><![CDATA[Total col11]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="col41" height="20">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{col4}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                            <textField>
                                <reportElement x="0" y="0" width="120" height="20" uuid="137ef406-6a2e-4629-9d38-4858c4a5ae2c">
                                    <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                                </reportElement>
                                <textFieldExpression><![CDATA[$V{col41}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents>
                            <staticText>
                                <reportElement x="0" y="0" width="-2147483648" height="-2147483648" uuid="5c71211d-f1db-4911-8923-2bdb208ddb99"/>
                                <text><![CDATA[Total col41]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="col2_MEASURE1" class="java.lang.Integer">
                    <measureExpression><![CDATA[$F{col2}]]></measureExpression>
                </measure>
                <measure name="col3_MEASURE1" class="java.sql.Timestamp">
                    <measureExpression><![CDATA[$F{col3}]]></measureExpression>
                </measure>
                <crosstabCell width="120" height="20">
                    <cellContents mode="Opaque" style="Crosstab_CD">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" uuid="861d0d3a-d774-4b0c-ae7d-ace315bf305c"/>
                            <textFieldExpression><![CDATA[$V{col2_MEASURE1}]]></textFieldExpression>
                        </textField>
                        <textField>
                            <reportElement x="60" y="0" width="60" height="20" uuid="163ed502-e753-444a-942d-832d84da0a5f">
                            </reportElement>
                            <textFieldExpression><![CDATA[$V{col3_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" columnTotalGroup="col41">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="2576b9e2-3bba-40e3-aa15-5f4f0aada549"/>
                            <textFieldExpression><![CDATA[$V{col2_MEASURE1}]]></textFieldExpression>
                        </textField>
                        <textField>
                            <reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="a111181f-b60f-4afd-8292-9e260c1b9ce2"/>
                            <textFieldExpression><![CDATA[$V{col3_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="col11">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="9adf6c9c-acee-4673-8b70-a376e7d1cbb4"/>
                            <textFieldExpression><![CDATA[$V{col2_MEASURE1}]]></textFieldExpression>
                        </textField>
                        <textField>
                            <reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="9baedeed-4770-4153-b639-afc0965e9467"/>
                            <textFieldExpression><![CDATA[$V{col3_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="col11" columnTotalGroup="col41">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="a2805e0a-080d-42be-ad86-a7e8e666f571"/>
                            <textFieldExpression><![CDATA[$V{col2_MEASURE1}]]></textFieldExpression>
                        </textField>
                        <textField>
                            <reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="c485f69d-91e1-4b19-a8ab-bdbfa3cafcc6"/>
                            <textFieldExpression><![CDATA[$V{col3_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </summary>
</jasperReport>

Upvotes: 2

Views: 2331

Answers (1)

Alex K
Alex K

Reputation: 22857

It is better to stay measureExpression AS IS and apply formatting for textFieldExpression.

The textFieldExpression in crosstab can operate with crosstab's parameters values.

  • If the main Main dataset is using at crosstab we can use the crosstab's parameters in cells of crosstab.
  • If the another dataset is using at crosstab we can this dataset's parameters in cells of crosstab.

Example

Datasource

The csv datasource is using in example.

col1,col2,col3,col4
a,a123,10,12/02/2016
a,a123,20,30/01/2016
a,a123,44,21/01/2016
a,b456,5,28/03/2016
b,a123,15,23/03/2015
b,b456,100,13/05/2015
b,b456,200,10/09/2015
b,b456,130,09/09/2017

The name of data adapter for this datasource in the example below is values.csv. The first line from the file is skipped - it is contains the column's name.

Report template

Col1 is using in rowGroups, col2 is using at columnGroups. With help of measureExpression the crosstab showing the Sum of col3 for each group and the Highest date (col4).

The formatting will be applied for textFields. The parameter will be used for Date formatting.

In this example the Main dataset is using for showing data at crosstab. We need to pass the value of report's parameter to the crosstab's parameters, because we can't use report's parameters in crosstab (with rare exceptions).

<crosstabParameter name="dateFormat" class="java.text.SimpleDateFormat">
    <parameterValueExpression><![CDATA[$P{REPORT_PARAMETERS_MAP}.get("SIMPLE_DATE_FORMAT")]]></parameterValueExpression>
</crosstabParameter>

- this is initialization of crosstab's parameter (dateFormat) with help of REPORT_PARAMETERS_MAP report's parameter. The value of REPORT_PARAMETERS_MAP parameter can be accessed from crosstab.

The value of report's parameter SIMPLE_DATE_FORMAT can be passed to the report for example with help of Java code.

The jrxml code:

<?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="Formatted measures" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="values.csv"/>
    <style name="Crosstab_CH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Crosstab_CD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <parameter name="SIMPLE_DATE_FORMAT" class="java.text.SimpleDateFormat" isForPrompting="false">
        <defaultValueExpression><![CDATA[new java.text.SimpleDateFormat("dd.MM.yyyy")]]></defaultValueExpression>
    </parameter>
    <field name="col1" class="java.lang.String"/>
    <field name="col2" class="java.lang.String"/>
    <field name="col3" class="java.lang.Integer"/>
    <field name="col4" class="java.util.Date"/>
    <summary>
        <band height="270">
            <crosstab>
                <reportElement x="20" y="70" width="525" height="200" >
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                </reportElement>
                <crosstabParameter name="dateFormat" class="java.text.SimpleDateFormat">
                    <parameterValueExpression><![CDATA[$P{REPORT_PARAMETERS_MAP}.get("SIMPLE_DATE_FORMAT")]]></parameterValueExpression>
                </crosstabParameter>
                <rowGroup name="col1" width="60">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{col1}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="60" height="20"/>
                                <textFieldExpression><![CDATA[$V{col1}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                </rowGroup>
                <columnGroup name="col2" height="20">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{col2}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="180" height="20"/>
                                <textFieldExpression><![CDATA[$V{col2}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                </columnGroup>
                <measure name="col3_MEASURE" class="java.lang.Integer" calculation="Sum">
                    <measureExpression><![CDATA[$F{col3}]]></measureExpression>
                </measure>
                <measure name="col4_MEASURE" class="java.util.Date" calculation="Highest">
                    <measureExpression><![CDATA[$F{col4}]]></measureExpression>
                </measure>
                <crosstabCell width="180" height="40">
                    <cellContents mode="Opaque" style="Crosstab_CD">
                        <textField pattern="#,##0.00">
                            <reportElement x="0" y="0" width="180" height="20"/>
                            <textFieldExpression><![CDATA[$V{col3_MEASURE}]]></textFieldExpression>
                        </textField>
                        <textField>
                            <reportElement x="0" y="20" width="180" height="20"/>
                            <textFieldExpression><![CDATA[$V{col2}.equals("a123") ? $P{dateFormat}.format($V{col4_MEASURE}) :
 new SimpleDateFormat("dd/MM/yy").format($V{col4_MEASURE})]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </summary>
</jasperReport>

The expression for showing data can be complex as in this example. With help of expression:

<textFieldExpression><![CDATA[$V{col2}.equals("a123") ? $P{dateFormat}.format($V{col4_MEASURE}) : new SimpleDateFormat("dd/MM/yy").format($V{col4_MEASURE})]]></textFieldExpression>

we are showing dates in different formats for different columns (col2).

Output result

The result in Jaspersoft Studio will be:

Preview in JSS

The sum of Integer values formatted with help of pattern attribute and the max value of Date values formatted with help of Java expression.

Upvotes: 2

Related Questions