Jasper Reports crosstab sorting with comparatorExpression

Question 1

Jasper Reports crosstab sorting with comparatorExpression

sorting jasper-reports comparator crosstab

JayL · Feb 22, 2010 · Viewed 14.6k times · Source

Answer

Answer

There may be much easier and straight forward solutions:

I am using Jasper Reports Studio (Eclipse Plugin).

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.
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)
using an order by based on measure totals (as described in the bottom link)
using a custom Java Comparator class (as described in the answer from Pieter VN 2011-11-16)

further maybe helpful links I found:

Question 2

I'm trying to sort my dynamic columns in a cross tab according to some custom scheme.

In the docs I found mention of comparatorExpression: Crosstab group bucket comparator expression. The result of this expression is used to sort the buckets, in ascending or descending order. If no comparator expression is specified, the natural order will be used.

but I don't understand what the expression should look like. Can I somehow use a regular java comparator? Can someone share an example?

Jasper Reports crosstab sorting with comparatorExpression

Answer

Related questions