Sorting in Crosstab sort column/row group with Ord

问题:

I know there is a similar thread here already, but that doesn't work for me.

Background: Jaspersoft Studio 6

I have a data set like this:

Created a crosstab like this on this dataset:

How to sort the column group to prod c, prod a, prod b instead of current?

I tried put this expression in Order by expression of the column group.

$F{product}.equals("prod c")? 1 : $F{product}.equals("prod a")? 2:3

But it says

edit: added source, this is the version without sorting and it runs fine without error but without sorting of course.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-06-08T14:14:48 -->
<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="test3" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="0a3345e5-7859-4366-9ccf-b215cf60a3b0">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="vha"/>
    <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>
        <![CDATA[select 'channel a' as "sales channel", 'prod a' as product, 1 as amount
union all select 'channel a' as salesChannel, 'prod b' as product, 1 as amount
union all select 'channel a' as salesChannel, 'prod c' as product, 1 as amount
union all select 'channel b' as salesChannel, 'prod a' as product, 1 as amount
union all select 'channel b' as salesChannel, 'prod b' as product, 1 as amount
union all select 'channel b' as salesChannel, 'prod c' as product, 1 as amount]]>
    </queryString>
    <field name="sales channel" class="java.lang.String"/>
    <field name="product" class="java.lang.String"/>
    <field name="amount" class="java.lang.Integer"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <summary>
        <band height="224" splitType="Stretch">
            <crosstab>
                <reportElement x="91" y="24" width="459" height="200" uuid="66af64fd-5d36-48a9-9356-f1c2cd55f6a0">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                </reportElement>
                <rowGroup name="sales channel1" width="60" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{sales channel}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="60" height="20" uuid="27883596-a403-4b9a-a3d5-d18adced1ec1"/>
                                <textFieldExpression><![CDATA[$V{sales channel1}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="7e48fc9b-d619-4b04-8b50-4fbd6174509e"/>
                                <text><![CDATA[Total sales channel1]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="product1" height="20" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{product}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="60" height="20" uuid="81d54f8e-11ab-4841-9aef-ef224899c337"/>
                                <textFieldExpression><![CDATA[$V{product1}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="0925adf9-e1d5-4b68-a7c5-0650b7e72721"/>
                                <text><![CDATA[Total product1]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="amount_MEASURE1" class="java.lang.Integer" calculation="Count">
                    <measureExpression><![CDATA[$F{amount}]]></measureExpression>
                </measure>
                <crosstabCell width="60" height="20">
                    <cellContents mode="Opaque" style="Crosstab_CD">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" uuid="cdbc8bf6-e55f-4e69-9505-d40d01870add"/>
                            <textFieldExpression><![CDATA[$V{amount_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" columnTotalGroup="product1">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="3543f652-7dee-43fd-84ba-514613b8ac03"/>
                            <textFieldExpression><![CDATA[$V{amount_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="sales channel1">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="9657bd6f-c862-4462-b88f-6ea0e049a3ad"/>
                            <textFieldExpression><![CDATA[$V{amount_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="sales channel1" columnTotalGroup="product1">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="8311db87-f185-447a-b692-ce878b7fdff5"/>
                            <textFieldExpression><![CDATA[$V{amount_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </summary>
</jasperReport>

回答1:

As mentioned @tobi6 in his post you can use Measures in orderByExpression (you can additionaly use Buckets with $V{} expression but it may be a hard way)

In your case the simplest way is to add new Measure with sorting rules.

<measure name="productForSort" class="java.lang.Integer">
    <measureExpression><![CDATA[$F{product}.equals("prod b") ? 1 : $F{product}.equals("prod c") ? 2 : 3]]></measureExpression>
</measure>

After that you can use this Measure in orderByExpression expression:

<bucket class="java.lang.String">
    <bucketExpression><![CDATA[$F{product}]]></bucketExpression>
    <orderByExpression><![CDATA[$V{productForSort}]]></orderByExpression>
</bucket>

For my test csv datasource:

sales channel, product, amount
channel a, prod c, 5
channel a, prod a, 1
channel b, prod a, 3
channel a, prod b, 1
channel b, prod b, 1

the result was like this:

As you see the "product b" is going first.

---

Notes:

Question is similar (duplicates) a really great post Jasper Reports crosstab sorting with comparatorExpression. You can find more information in this post.

My answer is similar as the solution by @PetterFriberg. But in this case we can ommit the using of Comparator

Another two answers are also good, specially the R&D by @AndreasDietrich

回答2:

When using the Order By Expression, no Fields $F{} can be accessed, only measure variables (from here):

The expression is evaluated in the context of the crosstab group and can reference measure variables, which evaluate to group totals.

EDIT

Measure variables are predefined when creating a crosstab. They follow a pattern (from here, "Understanding Crosstab Total Variables"):

When you have multiple row or column groups, you can use crosstab total variables to combine data at different aggregation levels (for example, to calculate a percentage). The following built-in variables are available:

• <Measure>_<Column Group>_ALL: The total of all the entries in the specified column group and the current row.

• <Measure>_<Row Group>_ALL: The total of all the entries in the specified row group and the current column.

• <Measure>_<Row Group>_<Column Group>_ALL: The combined total of all the entries in the specified row and column groups.

You can also select these variables from the expression editor for the Expression field on the Text Field tab of the Properties view for a measure.

So as far as I understand it, you can use those variables to group and order your results. Those variables show up in the expression editor for the crosstab (there is a screenshot in the source).

回答3:

FYI, there's a buggy thing for me where i am unable (in Jasper Studio 6.4) to select Measures in the GUI from the Order By dropdown, but it works when i manually type it in either in the GUI expression box or directly into the xml.

1. Created variable to calculate conditional sorting

   <variable name="Data_Sort" class="java.lang.Double">
       <variableExpression><![CDATA[($P{Variance}=="Positive" ? $V{Vol_Diff}:(($P{Variance}=="Negative") ? $V{Vol_DiffInverse} :$V{Vol_AbsDiff}))]]>
       </variableExpression>
   </variable>
   

2. Created a Measure within the crosstab to access the sorting variable

   <measure name="DataSort_Measure" class="java.lang.Double">
       <measureExpression><![CDATA[$V{Data_Sort}]]></measureExpression>
   </measure>
   

3. Used the Measure variable in the orderby expression of the bucket of my rowgroup

   <rowGroup name="Well.WellName" width="150">
       <bucket order="Descending" class="java.lang.String">
           <bucketExpression><![CDATA[$F{Well.WellName}]]></bucketExpression>
               <orderByExpression><![CDATA[$V{DataSort_Measure}]]>
               </orderByExpression>
           </bucket>