JasperServer Toggle Month/Year Grouping

[isaach_1]

Hello,

I am a beginner to Jasper and am working with a database storing dates in datetime format. In Jasper Server, datetime can be grouped by Year/Month/Day in the column/row. Is there a way in Jasper Server to toggle dashboard and report charts to dynamically group by Year or by Month for an end user?

[anish.rai]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[Raphaël Peguet]

Hello,
This is a good question. When you change the grouping level in AdHoc a lot is happening behind the scene and if you'd like to recreate it in Jaspersoft Studio you'll have to build it yourself. There is no built-in feature that would make it a 1 step configuration.
The HTML 5 charts in Jaspersoft commercial editions are based on the popular highcharts library and I couldn't find a property that we could use to change that grouping dynamically in time series charts. Apparently HighStocks would do that but it's not currently available in Jaspersoft and you would need to adapt to the way the data is presented there.
So that leaves us with lines or bar charts that are not ideal -although not that hard- with displaying time series. Note that this is also the chart types also used in the server AdHoc features.
So we need to dynamically group the data by year/quarter/month/day/... ourselves and ensure that the order of columns or dots in line charts respect the normal time order (not alphabetically) so that months for example remains in the right order.

I attach a report here which -when imported in the server- will show the same chart toggling between the year, the month and the day views. 
Click on a dot in the chart or on 'drill up' to navigate through the levels.
In this report I modify the SQL query dynamically but I could have done the logic in the chart's measure property with if-then-else logic for each level.

Few tips on importing the report attached:
• you need to be logged as a jasperadmin
• Navigate to Manage > Organizations
• Right click on an organization
• import
• Make sure you use the legacy key
• It uses the foodmart data source which comes with the samples of the trial so you may not update and overwrite the one you have if you modified it.
• Find the report in the public folder
• The report is a bit complex and you may want to print the parameter values above the chart to understand what is changing between each view.

[Raphaël Peguet]

An alternative and simpler option may be to keep using the AdHoc editor to create your report.
It has some limitations but I post it here in case this wouldn't be an issue in your case.

You may apply a Jaspersoft Studio AdHoc Report template on reports generated with the AdHoc designer.
The steps are described in the server ultimate guide at https://community.jaspersoft.com/documentation/tibco-jasperreports-server-ultimate-guide/v80/creating-new-report-template

Only 1 chart can be used per report in that case and some alignment can be odd but it's so simple you should probably give it a try.

Please let me know which option works best for you with the green tick mark and and upvote.