How to sort column header values of crosstab in jasper?

Question

My column values in jasper crosstab has month names.

Problem is they are sending from backend in various objects, so I can't sort month name coming from backend. <

Because of that this issue has occurred.

I want to sort these columns in Jan, Feb, Mar, Apr, May Aug order.

Can I do that setting a if else if else condition in column value in jasper?

Answer 1

To sort your crosstab header you have two options:

1. Create a Comparator class in java and pass that to the columnGroup's bucket expression in comparatorExpression

2. Create a measure that is sortable and pass that to orderByExpression.

In this example I will use method 2 and presume that the backend returns the month in date format MMM, hence I create a date object and get the time from this. If the month name is not in MMM format you would need to implement a ternary expression as suggested by Alex K.

new java.text.SimpleDateFormat("yyyy-MMM-dd").parse("2000-" + $F{Month} + "-01").getTime()

Create the measure:

<measure name="monthSort" class="java.lang.Long">
    <measureExpression><![CDATA[new java.text.SimpleDateFormat("yyyy-MMM-dd").parse("2000-" + $F{Month} + "-01").getTime()]]></measureExpression>
</measure>

Add the measure to the orderByExpression in the columnGroup (avoiding null pointer if variabile is not instanced yet)

<columnGroup name="Month" height="20" totalPosition="End">
    <bucket class="java.lang.String">
        <bucketExpression><![CDATA[$F{Month}]]></bucketExpression>
        <orderByExpression><![CDATA[($V{monthSort}==null?0:$V{monthSort})]]></orderByExpression>
    </bucket>
    ....
 </columnGroup>

Full example

Data

+------+-------+-------+
| Name | Month | Value |
+------+-------+-------+
| cat1 | Jan   |   121 |
| cat1 | Feb   |    52 |
| cat1 | Aug   |    73 |
| cat1 | Mar   |    14 |
| cat2 | Feb   |    54 |
| cat2 | Apr   |    80 |
| cat2 | Dec   |    10 |
| cat2 | Jan   |    20 |
+------+-------+-------+

jrxml

<?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="SortValue" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="0ba3ded0-a5d4-435b-a2b1-c61ecd71ac00">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Months"/>
    <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>
    <queryString language="csv">
        <![CDATA[]]>
    </queryString>
    <field name="Name" class="java.lang.String"/>
    <field name="Month" class="java.lang.String"/>
    <field name="Value" class="java.lang.String"/>
    <summary>
        <band height="300" splitType="Stretch">
            <crosstab>
                <reportElement x="0" y="0" width="520" height="70" uuid="13bf3aea-b677-4389-bbd5-60cb98bad0c6">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                </reportElement>
                <rowGroup name="Name" width="60" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{Name}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="60" height="20" uuid="f9becd93-ee0a-443b-bf24-d3c5f903d2b7"/>
                                <textFieldExpression><![CDATA[$V{Name}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="87613864-7410-44f6-a39f-7a727545db9d"/>
                                <text><![CDATA[Total Name]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="Month" height="20" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{Month}]]></bucketExpression>
                        <orderByExpression><![CDATA[($V{monthSort}==null?0:$V{monthSort})]]></orderByExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="60" height="20" uuid="f8957674-bebb-4c29-9799-ff0b1756d910"/>
                                <textFieldExpression><![CDATA[$V{Month}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="50a78979-ea87-457d-aced-36a474659b62"/>
                                <text><![CDATA[Total Month]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="Value_MEASURE" class="java.lang.String" calculation="First">
                    <measureExpression><![CDATA[$F{Value}]]></measureExpression>
                </measure>
                <measure name="monthSort" class="java.lang.Long">
                    <measureExpression><![CDATA[new java.text.SimpleDateFormat("yyyy-MMM-dd").parse("2000-" + $F{Month} + "-01").getTime()]]></measureExpression>
                </measure>
                <crosstabCell width="60" height="20">
                    <cellContents mode="Opaque" style="Crosstab_CD">
                        <textField isBlankWhenNull="true">
                            <reportElement x="0" y="0" width="60" height="20" uuid="f53a65f4-1f08-4856-92a4-09267c6c073c"/>
                            <textFieldExpression><![CDATA[$V{Value_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" columnTotalGroup="Month">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="0a17d733-1388-4df9-b754-182eaa09eca8"/>
                            <textFieldExpression><![CDATA[$V{Value_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="Name">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="2bcf8a9c-8315-4508-9397-4850b08957f9"/>
                            <textFieldExpression><![CDATA[$V{Value_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="Name" columnTotalGroup="Month">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="5431126e-8ed1-4c20-be7b-f88df55039c2"/>
                            <textFieldExpression><![CDATA[$V{Value_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </summary>
</jasperReport>

Output