'Filter' Data of Crosstabs Based On The Same Dataset

Use Case

User developed a JRXML report with 2 Crosstab elements ponting to the same main dataset.

Now, the user has a requirement 'to filter' the data for each Crosstab, so that Crosstab1 displays data for a specific column value, while the second Crosstab2 - for another value.

Using SubDataset for the second Crosstab is not an option for the user.

Resolution

A possible solution to achieve the requirement is to specify 'Increment When Expression' property for each Crosstab Dataset.

The 'Increment When Expression' is a flag to determine whether to add a record to the record set that feeds the Crosstab.

This expression must return a Boolean value. A blank string means to 'add all the records':

Attached is a sample report based on SugarCRM database, Orders table, which demonstrates the idea

The main report query returns records for all countries (shipcountry field):

SELECT * FROM orders LIMIT 1000

Setting 'Increment When Expression' to value

$F{shipcountry}.equals("USA")[/code]

processes records with USA country only for the first Crosstab.

Setting 'Increment When Expression' to value

$F{shipcountry}.equals("Canada")[/code]

processes records with Canada country only for the second Crosstab.

Solution tested with  TIBCO JasperReports® 7.1.0.

AS-20190405, case #01736277

support_crosstabfilters_solution.jrxml