Unexpected results and/or performance issues with group summaries in ad-hoc (v5.0 and later)

Issue

Some customers have reported unpredictable results when selecting "Distinct Count" as the summary function for field in Ad-Hoc reports and views. Additionally, they have reported performance lags when adding a distinct-count summary field to their reports.

This trouble behavior seems to be rare and the cause is to be determined. However, this seems to be isolated to cases where the default in-memory summary computations calculate these values incorrectly.

Solution

Inside the jasperserver-proWEB-INFapplicationContext-adhoc-dataStrategy.xml file, switching the "calcMethod" configuration from the default "sqlGroupBy" to the new "sqlUnionAll" setting solves this problem. This forces all summary calculations for "Distinct Count" summaries to happen at the database level via SQL (instead of calculation logic split between SQL and in-memory logic in JR server, which is the default). This change also provides the benefit of improved performance, since the calculations are performed at the database level rather than in memory during creation of the report on the machine hosting JasperReports Server.

To make this change:

1. open jasperserver-proWEB-INFapplicationContext-adhoc-dataStrategy.xml

2. set the calcMethod value to "sqlUnionAll".  See below.

   Replace this section:

   <bean>
       <property name="name" value="DistinctCount" />
       <property name="functionName" value="distinctCount" />
       <property name="calcMethod" value="sqlGroupBy" />
   </bean>
   

   with this:

   <bean>
       <property name="name" value="DistinctCount" />
       <property name="functionName" value="distinctCount" />
       <property name="calcMethod" value="sqlUnionAll" />
   </bean>
   

3. re-start your application server