How to calculate crosstab bucket measure values and how to set debug

Issue:

We are trying to work out how to calculate the crosstab bucket measure size for a Crosstab report designed in TIBCO Jaspersoft® Studio so that it can help in setting the net.sf.jasperreports.crosstab.bucket.measure.limit property.  How can this be achieved ?

Solution:

The formula to calculate the crosstab bucket measures is:

For JRXML Crosstab reports

(number of crosstab rows) x (number of crosstab columns) x

(number of user-defined measures)

For Ad Hoc Crosstab reports

(number of crosstab rows) x (number of crosstab columns) x

(number of user-defined measures + 1)

Note: The (measures + 1) formula applies to Ad Hoc Crosstab reports because it has an additional measure created internally.

In TIBCO JasperReports® Server 7.5.0 and above, there is a new logger component which can be added to the "Log Settings" page to enable the TIBCO JasperReports® Server engine to calculate the size of the crosstab bucket measures and log these counts in the jasperserver.log file.

In order to see the crosstab bucket measure values, debug logging needs to be enabled for the following component :

net.sf.jasperreports.crosstabs.fill.calculation.CrosstabBucketingService

Sample 1 - BucketLimit.jrxml:

The following is a sample of the entries that would be logged to the jasperserver.log:

08:02:38:913 [main] DEBUG calculation.CrosstabBucketingService - crosstab has 165 row buckets, 234 column buckets and 5 measures

08:02:38:913 [main] DEBUG calculation.CrosstabBucketingService - bucket measure count is 193050, limit 0

08:02:39:895 [main] DEBUG calculation.CrosstabBucketingService - crosstab has 165 row buckets, 234 column buckets and 5 measures

08:02:39:895 [main] DEBUG calculation.CrosstabBucketingService - bucket measure count is 193050, limit 0

08:02:40:646 [main] DEBUG calculation.CrosstabBucketingService - crosstab has 198 row buckets, 234 column buckets and 5 measures

08:02:40:646 [main] DEBUG calculation.CrosstabBucketingService - bucket measure count is 231660, limit 0

Note: The bucket measure value is computed per crosstab instance, and not for the whole report. If the report has a crosstab in a group footer that repeats three times, then there will be three crosstab instances, each with its own bucket counts.

Sample 2 - CrosstabBucket.jrxml :

For the sample report, CrosstabBucket.jrxml, the engine detected 15 crosstab instances. After setting debug, for the 15th (last) instance, the following values were computed:

2020-08-12T12:55:33,864 DEBUG CrosstabBucketingService,pool-6-thread-1:110 - crosstab has 3109 row buckets, 6 column buckets and 10 measures

2020-08-12T12:55:33,866 DEBUG CrosstabBucketingService,pool-6-thread-1:113 - bucket measure count is 186540, limit 100000

The 15th crosstab's output is attached after exporting to XLSX and you can see from the number of rows that the value 3109 is accurate. The number of columns is based on the months per SubGeography. 

Related Articles:

JS-34784: add debug log with crosstab bucket measure values

crosstabbucket.zip