Using Relative Dates with jrxml report in SQL Stored Procedure

[katherine.vierra]

We are implementing the use of relative dates (JasperServer 5.6) with our jrxml reports so that customers can schedule reports to run daily with date parameters that are relative to the current date.

In order to do this, per (http://community.jaspersoft.com/wiki/how-use-relative-dates-jrxml-static-reports)  we have changed all of our date input parameter classes to 'Timestamp Range' (previously used java.sql.Timestamp).  And have begun using the syntax $X{BETWEEN, field, begindate, enddate} in our queries.

We have some reports which call a SQL stored procedure to get the report dataset.  The problem is, I cannot seem to get those reports to accept the new date class.  If I try to send it via a standard: 

EXEC storedproc $P{begin_date}, $P{end_date}, param3, param4, etc...

I get an error that $P is not supported for that class (which is true).  I just don't know how else to get it to my stored procedure. 

In the case of these reports the sql is quite complex and built dynamically based on the various input parameters.   We may use different tables/joins depending on what  the user chooses and a stored procedure seemed to be the way to go with that.  Also, we need to be able to set the dbase to READ UNCOMMITTED and Jasper won't accept that in their query window.

I did attempt to copy the value of the date params to two new params of a different class(java.sql.timestamp) that could be sent to the stored proc, but I always get a null value in the second set of params (See Below)

DATE PARAMETERS ENTERED BY USER

<parameter name="begin_date" class="net.sf.jasperreports.types.date.TimestampRange">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <parameter name="end_date" class="net.sf.jasperreports.types.date.TimestampRange">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>

MY FAILED ATTEMPT TO COPY THE VALUE OF THOSE PARAMETERS TO SOMETHING I CAN SEND TO THE STORED PROC

    <parameter name="beginTime" class="java.sql.Timestamp" isForPrompting="false">
        <defaultValueExpression><![CDATA[$P{begin_date}.getStart()]]></defaultValueExpression>
    </parameter>
    <parameter name="endTime" class="java.sql.Timestamp" isForPrompting="false">
        <defaultValueExpression><![CDATA[$P{end_date}.getStart()]]></defaultValueExpression>
    </parameter>
    <queryString>

Any suggestions would be greatly appreciated.

 

[hozawa]

I've found there was a bug in pre-5.6.1 version of JasperReports Server with relative dates which was fixed in 5.6.1.

[Ectomorph]

Hi, would this cause scheduled reports not to use the relative date each scheduled run? - This is what is happening with mine, they keep using the same date from first time they were run