Jump to content
Changes to the Jaspersoft community edition download ×
  • Using Derived Hidden Parameter In Report Design To Ensure User Selected Date For The Report Is Not In The Future


    tchen
    • Version: v5.0.1 Product: JasperReports® Library

    Report Requirement:

    User is using JR 5.0.1 version of which the Relative Date had not been implemented. User wants to select report data based on user's input date DATE_END as the following:

    A. If the date provided <= NOW(), run the report as is based on user's selected date;
    B. If the date provided > NOW(), run the report selecting data based on Today's date at midnight.

    Solution:

    We need to create a secondary "hidden" (non-prompting) parameter in the report design template to derive the date based on user input from DATE_END parameter and use it in the SQL query. This secondary date DATE_END2 parameter is based on user's input date and today's date, whichever is smaller, to select report data. The parameter is constructed in the following exact order in the report design to have it work correctly: 

        <parameter name="DATE_FORMAT" class="java.text.SimpleDateFormat" isForPrompting="false">
            <defaultValueExpression><![CDATA[new SimpleDateFormat("dd/MM/yyyy")]]></defaultValueExpression>
        </parameter>
        <parameter name="DATE_TODAY" class="java.util.Date" isForPrompting="false">    
            <defaultValueExpression><![CDATA[$P{DATE_FORMAT}.parse($P{DATE_FORMAT}.format(new java.util.Date()))]]></defaultValueExpression>
        </parameter>
        <parameter name="DATE_END" class="java.util.Date">
            <defaultValueExpression><![CDATA[$P{DATE_TODAY}]]></defaultValueExpression>
        </parameter>
        <parameter name="DATE_END2" class="java.util.Date" isForPrompting="false">
            <defaultValueExpression><![CDATA[$P{DATE_END}.compareTo($P{DATE_TODAY})<0?$P{DATE_END}:$P{DATE_TODAY}]]></defaultValueExpression>
        </parameter>


    This derived date parameter $P!{DATE_END2 then can be used in the report SQL query in the following fashion:
     

    SELECT
    ........
    WHERE
    "datetime" > '$P!{DATE_END2}'::timestamp - interval '1 $P!{INTERVAL}'
    AND "datetime" <= '$P!{DATE_END2}'



    ==================================

    TTC-20160531-67712


    User Feedback

    Recommended Comments

    There are no comments to display.



    Guest
    This is now closed for further comments

×
×
  • Create New...