Ad Hoc Crosstab displays Totals only when number of records reaches 100,000 or more

Issue:

In Ad Hoc Editor, we have an Ad Hoc Crosstab with a filter that filters on the number of records to display. 

When running a large Ad Hoc Crosstab View, the crosstab displays all the rows in "Full Data" mode when the number of records is 99,999 or less but it displays nothing more than "Totals" when the number of records reaches 100,000.

Working OK

Not Working

How can this be resolved so that the Crosstab displays the records for 100,000 or more ?

Solution:

The following DEBUG (or INFO) can be set under Server Settings -> Log Settings page: 

com.jaspersoft.commons.datarator.WorkingDataSet (Ad Hoc WorkingDataSet)  

to see whether the Ad Hoc Crosstab has reached the axis node limit.

The following message may (or may not) be captured in the log file: 

"Axis node limit has been reached. Crosstab will show only totals instead of all nodes. Please increase axis node limit to see more data."

The above the message is logged from TIBCO JasperReports® Server version 8.1 onwards.

Increase the axisNodeLimit value in the ..WEB-INFapplicationContext-datarator.xml file (line 238) :          

    <bean id="dimensionDataSetTemplate" parent="workingDataSetTemplate"
        class="com.jaspersoft.commons.dimengine.DimensionDataSetImpl" scope="prototype" >
        <property name="axisTransformerFactory" ref="axisTransformerFactory"/>
        <!-- absolute limit on how many nodes can be in any axis -->
        <property name="axisNodeLimit" value="100000"/>
    </bean>    

Note: The changes requires a restart of the TIBCO JasperReports® Server.