Jasper Reports "Error evaluating expression for source text:DATEFORMAT($P{p_startdate},"yyyy-MM-dd HH:mm:ss")

Products

• CA Service Management - Service Desk Manager

Issue/Introduction

Customer is trying to generate a custom report in Jasper Reports.

Error found when recreating the error:

net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression for source text: DATEFORMAT($P{p_startdate},"yyyy-MM-dd HH:mm:ss")
<omitted>
Caused by: java.lang.ClassCastException: java.util.Date cannot be cast to java.sql.Timestamp
<omitted>

Environment

Release : 17.3

Component : JASPERSOFT REPORTS FOR SERVICE MANAGEMENT

Cause

Customer may be missing some files (attached to the case) and/or may be following incorrect steps. 

Resolution

Using Date Parameters in Jasper Report

This example will show how to add a date parameter to a TIBCO Jaspersoft Studio Professional report, and publish the report in Jasper Reports.

Add ca_customCASMMethod.jar to your build path

There are built in methods that you can leverage to build your report.  We will add the ca_customCASMMethods.jar to our build path in order to use a pre-set date search parameter.  

Instructions:

Check your install for the ca-customCASMMethods.jar file.  It will reside in the jasperserver-pro install directory.  For example:

C:\Program Files\apache-tomcat-8.0.33-windows-x64\apache-tomcat-8.0.33\webapps\jasperserver-pro\WEB-INF\lib\ca-customCASMMethods.jar

If it does not exist, copy the attached zip file to the Jaspersoft Studio server.

 

Extract the files.

Copy the jar file to the jasperserver-pro install directory.  For example:

C:\Program Files\apache-tomcat-8.0.33-windows-x64\apache-tomcat-8.0.33\webapps\jasperserver-pro\WEB-INF\lib\ca-customCASMMethods.jar

Open Jaspersoft Studio and open the Project Explorer tab

Right Mouse click on MyReports and select: Build Path à Configure Build Path…

Under the Libraries tab, select Add External JARs…

Browse to the ca_customCASMMethods.jar file

Click Apply and OK

Main Report

Add Scriptlet: ca-customCASMMethods.jar

Open up the jrxml report that you want to use the scriplet in, in this case Main.jrxml. 

Under the Outline tab, expand Scriptlets.

Right click on Scriptlets and select Create Scriptlet:

In the right pane, under Scriptlet Properties, select the button next to Class:

Start typing: CustomFunctions and select it from the list when it appears:

Click OK

Enter a Name for the Scriptlet: ca-customCASMMethods

Variables

Validate that the Main report Variables are defined.  This way you can pass the time stamp sting representation to the supreport.  In this case the variables are defined as v_Start_Date, and v_End_Date:

 

Validate Subreport Name

This will only work when a subreport is used. 

Under the Detail band, select the existing Subreport.  Validate that the name of the subreport Expression matches the name of the jrxml subreport. 

Validate Subreport Parameters

Select Subreport under the Detail band.

On the right pane, under Subreport, select Edit Parameters.

Make sure your variables map to parameters:

Subreport

Double click on the Subreport to open it.

Date Parameters

Check the report parameters that are used.  In this case it is “StartDate” and “EndDate” which will be timestamps to capture the Scheduled Start Time.

Update the Class from java.lang.String to java.sql.Timestamp:

P_predeftimeperiod

The p_predeftimeperiod works as follows:

1 = Previous Year

2 = Current Year

3 = Last 30 Days

4 = Last 60 Days

5 = Custom Dates (User entered start date and last date)

 

When you specify 1,2,3 or 4 both the v_StartDate and v_EndDate get populated and there is no need to enter Custom Start/End Dates.