Crosstab Sorting in JasperReports

There may be much easier and straight forward solutions:

I am using Jasper Reports Studio (Eclipse Plugin).

1. did not finally work for me :-( ... (see 1. comment below my answer - may be a bug) you just can check [x] Data Pre Sorted in the Crosstab Properties. Of course this only works if you do not need another ordering somewhere else in the report based on the pre-sorted result set.

2. using an invisible crosstab group header, which is rather tricky:

   1. create a new row/column group (e.g. via Outline view), leave the initial name Row Group1 or similar as is for now
   2. set its Total Position to None (we do not want to have a total column per row/column generated)
   3. move the group in the XML before the old one
   4. rename the group to some speaking name, e.g. name="invisible sort column ..."
      • (no further references to this group should exist in the XML anymore)

   5. if you are using group totals on your group (Total Position != None) then you have to basically move these totalling elements/settings to the first dummy sort group, because otherwise the totals will be no totals anymore (= per 2nd group totals) displayed after each group column/row, e.g. (here only shown with column group, but row group follows the same principle)

      a|b|c|sum      a|sum|b|sum|c|sum
      =========  =>  =================
      1|2|3|6        1|1  |2|2  |3|3
      

      • easiest may be to do this in the XML similar to this transformation (do not forget to move the totalPosition=... and columnTotalGroup=... attributes and to change the sum if applicable to your scenario $V{SomeSum_..._ALL}):

        ...
        <columnGroup name="OrderXDummy" height="0">
          ...
          <crosstabTotalColumnHeader>
            <cellContents/>
          </crosstabTotalColumnHeader>
        </columnGroup>
        ...
        <columnGroup name="X" ... totalPosition="End">
          ...
          <crosstabTotalColumnHeader>
            <cellContents ...>
              ...
            </cellContents>
          </crosstabTotalColumnHeader>
        </columnGroup>
        ...
        <crosstabCell ... columnTotalGroup="X">
          ...
           <textFieldExpression><![CDATA[$V{SomeSum_X_ALL}]]></textFieldExpression>
          ...
        </crosstabCell>
        

        =>

        ...
        <columnGroup name="OrderXDummy" height="0" totalPosition="End">
          ...
          <crosstabTotalColumnHeader>
            <cellContents ...>
              ...
            </cellContents>
          </crosstabTotalColumnHeader>
        </columnGroup>
        ...
        <columnGroup name="X" ... >
          ...
          <crosstabTotalColumnHeader>
            <cellContents/>
          </crosstabTotalColumnHeader>
          ...
        </columnGroup>
        ...
        <crosstabCell ... columnTotalGroup="OrderXDummy">
          ...
           <textFieldExpression><![CDATA[$V{SomeSum_OrderXDummy_ALL}]]></textFieldExpression>
          ...
        </crosstabCell>
        

   6. (may be skipped:) remove unnecessarily generated <crosstabCell ... column/rowTotalGroup="..."> cells

      • its maybe best to compare the report with a previous version to reliably and fast identify these spots in the XML
      • maybe this is not a crucial step, but looking at the existing XML is confusing enough ;-)
   7. add the (bucket) expression of your sort column, e.g. $F{ORDER_FOR_X}
      • don't forget to assign Value Class Name to java.lang.Integer or whatever fits for your values here (have a look at your dataset which type is assigned there if your are using it via some column)

   8. add some variable expression to the Order By Expression of the original group, e.g. $V{ORDER_FOR_X}

      • $V{...} is the trick, do not use $F{...}!
      • (the editor says it's invalid, but it will work)

      • meaning if you can provide some field that defines the sort and relates to your to-be-sorted column value, e.g. (Oracle SQL)

        select            1 as order_for_x,  'foo' as x,  'bla blu' as y  from dual
        union all select  2,                 'bar',       'ta tu'         from dual
        union all select  2,                 'bar',       'na na'         from dual
        union all select  1,                 'foo',       'check it'      from dual
        union all select  3,                 'queue',     'sap'           from dual
        

      • otherwise you can of course use something else here as well

      • if you should get some

        ...
        Caused by: java.lang.NullPointerException
        at org.apache.commons.collections.comparators.ComparableComparator.compare(ComparableComparator.java:92)
        at net.sf.jasperreports.crosstabs.fill.BucketExpressionOrderer.compareOrderValues(BucketExpressionOrderer.java:70)
        ...
        

        you can simply change the expression to $V{ORDER_FOR_X} == null ? 0 : $V{ORDER_FOR_X} which should do the trick

   9. set all high/width fields of the dummy group to 0 and the text fields Print When Expression to false

   10. (maybe check via compare with the previous report version that nothing else has changed to make sure you didn't mess up something else)

3. using an order by based on measure totals (as described in the bottom link)

4. using a custom Java Comparator class (as described in the answer from Pieter VN 2011-11-16)

further maybe helpful links I found:

• http://community.jaspersoft.com/wiki/custom-crosstab-ordering-ireport-designer
• http://community.jaspersoft.com/questions/506284/combined-crosstabs
• http://jasperreports.sourceforge.net/schema.reference.html

I had a similar problem. I wanted to display a "Heading" from the database, but I did not want it to sort alphabetically since the order of the headings were user determined.

I had a HeadingOrder field that I wanted to sort by. To use it in the sort expression it has to be in the cross tab.

I had heading in my column so made 2 column groups: 1) HeadingOrder 2) Heading.

I removed the text field that displayed the heading order number and collapsed that area so it did not display. This allowed the cross tab to display the Heading while using the HearingOrder field as the dominate sort.