Setting Up Custom Report Date Range

This is a guide to properly setup date ranges for your Custom Report. Modifications to your Custom Report’s template file (.jrxml file) are required to query the correct results from your Flex database. A summary is provided at the end.

Overview (UI → Backend → Report Engine)

In this scenario, my warehouse location is in Sydney, Australia, so my timezone is AEST (UTC +10). My goal is to fetch quotes summary by my user id for the whole month of May (May 01, 2021 12:00AM AEST to May 31, 2021 11:59PM AEST). When I want to launch the report, I fill in the following report parameters (date/time format is in DD/MM/YYYY).

When I click Generate Report, the Start Date sent to the Flex backend is 2021-04-30T14:00:00 and the End Date sent to the Flex backend is 2021-05-30T14:00:00. This is mostly correct because these date/time values are converted to UTC, so May 01, 2021 12:00AM AEST is April 30, 2021 2:00PM UTC and May 31, 2021 12:00AM AEST is May 30, 2021 2:00PM UTC. All date/time values sent to the server are in UTC because the date/time values in the database are UTC.

What is not correct is the End Date’s time is set to May 31, 2021 12:00AM AEST. We really want the time to be 11:59PM. As long as your End Date’s parameter name ends with END, it will automatically be converted by our backend code to your timezone’s 11:59pm. In this scenario, the Flex server has now converted the End Date to May 31, 2021 11:59PM AEST, which the server will interpret as May 31, 1:59 PM UTC.

The following date/time range is sent to the JasperReports engine:

Will the date range used in your report template work? No.

Problem

The report template file’s SQL WHERE clause looks like the following:

planned_start_date >= $P{range_start} AND planned_start_date <= $P{range_end}

After enabling MySQL logging, the following SQL WHERE clause is translated and executed by JasperReports

planned_start_date >= '2021-04-30' AND planned_start_date <= '2021-05-31'

This is incorrect. The time is being cut off by JasperReports. This is a known bug in JasperReports.

Workaround

The workaround, mentioned in the link at the bottom, is to add java.sql.Timestamp versions of the range_start and range_end parameters:

<parameter name="range_start_timestamp" isForPrompting="false">
    <defaultValueExpression><![CDATA[new java.sql.Timestamp($P{range_start}.getTime())]]></defaultValueExpression>
</parameter>
<parameter name="range_end_timestamp" isForPrompting="false">
    <defaultValueExpression><![CDATA[new java.sql.Timestamp($P{range_end}.getTime())]]></defaultValueExpression>
</parameter>

Then use these new parameters in your SQL WHERE clause:

planned_start_date >= $P{range_start_timestamp} AND planned_start_date <= $P{range_end_timestamp}

After updating your Custom Report’s template, rerunning the report now looks correct:

planned_start_date >= '2021-04-30 14:00:00.0' AND planned_start_date <= '2021-05-31 13:59:00.0'

Summary

Due to this JasperReports bug, you must add a Timestamp variation of your Date parameter if you wish to use it in your SQL WHERE clause.