How to use relative dates in TIBCO Jaspersoft Studio Report

Problem:

I am trying to create a report with relative dates that I want to deploy to TIBCO JasperReports Server later. How do I define the relative date parameters, how do I use them in a query, how do I create input controls for those parameters on a server.

Solution:

Create a parameter of type net.sf.jasperreports.types.date.DateRange. If you need a default value expression this parameter can have it defined like this:

<parameter name="Parameter1"
           class="net.sf.jasperreports.types.date.DateRange">
    <defaultValueExpression>
        <![CDATA[new net.sf.jasperreports.types.date.DateRangeBuilder.DateRangeBuilder("YEAR-100").toDateRange()]]>
    </defaultValueExpression>
</parameter>

This parameter can then be used in the query but it needs to be used in $X clause. For example in my sample the query is defined like this:
```
select birth_date from public.employee where $X{LESS, birth_date, Parameter1}
```
$X{} clause requires you to define three or four arguments. First argument is the filtering function. Second argument is the column name in the database which you would like to filter. Third and fourth arguments are the parameter names in the report that you want to use as the filtering criteria. In this case the $X clause will resolve to select birth_date from public.employee where birth_date < parameter1.
Functions can be the following: LESS, GREATER, EQUAL, NOTEQUAL, IN, NOTIN, BETWEEN. Only between requires you to specify two parameter names which will define the boundaries. For more information about this please refer to the JasperReports SourceForge.net Sample Reference
Once you have a parameter and a query you can run the report locally in iReport or Jaspersoft Studio. Once ran you will be prompted for a value. Here you can put either the actual date (7/29/2015, 2/2/1914 etc.) or the relative date (YEAR-100, MONTH+2). This functionality mirrors the functionality in ad hoc designer. Possible values for relative dates are:
DAY, WEEK, MONTH, QUARTER, SEMI, YEAR
When you deploy the report to JasperReports Server you will need to create an input control for this parameter. The input control needs to have the same id as the parameter name, needs to be a Single Value selection control and needs to have Date data type. In JasperReports Server you will see a calendar for this input control and will be able to choose date. However you can write in the field the relative date keyword and operation and it will be a valid input.

relative.jrxml

Sign In

How to use relative dates in TIBCO Jaspersoft Studio Report

Problem:

Solution:

Table of contents

User Feedback

Recommended Comments

jbarger.consultant 0

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Activity

Products

Explore