Relative Date Parameters in Jasper Server – Usage with Examples

Relative Date Parameters in Jasper Server – Usage with Examples

This blog gives a brief introduction of how Relative Date Parameters can be defined and used in Jasper Server Reports. Relative dates allow us to filter report data based on a date range relative to the current system date.

We often come across the situation where we have a Jasper report which has date filters and we want these parameters to be able to accept relative dates instead of just fixed date values. This is often a requirement when we want to schedule such reports periodically.
For example, We want a sales report to be sent to the sales manager every end of week and to a area manager every end of month. For this, we would like to schedule 2 occurrences of this report, one which extracts sales for past 7 days on every weekend and the other that extracts sale for the current month at the month end.

To do something like this, we can design a report with a parameter of date range type. These are the kind of values a relative date range parameter would accept – MONTH-1, QUARTER , YEAR-2, DAY-7, WEEK etc

This is the excerpt from jasper documentation that explains the template for relative dates.

RelativeDateTemplate

A default value expression for a date range parameter would look like the example below:

new net.sf.jasperreports.types.date.DateRangeBuilder("QUARTER-1").toDateRange()

 

To use the “Date Range” parameters in the query, we need to use jasper’s $X{} notation. I have put up some examples which will help you implements relative dates with all different options in the $X{} notation.

So, we are going to use the below date as the current system date in all the examples below –

Today's date = '31-07-2016'
Current Quarter = Q3 (July 2016 - Sep 2016)
Current Month = July 2016

 
The name of the parameter we are using is p_order_date. In case of examples for ‘between’ clause, we are using 2 parameters p_start_date and p_end_date.

1. EQUAL Clause in $X{}

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{EQUAL, ORDERDATE, p_order_date} 

 
p_order_date = QUARTER-1 (Apr 2016 – June 2016)
Selects all orders in the previous quarter i.e. all orders from ’01-04-2016′ to ’30-06-2016′

p_order_date = MONTH (July 2016)
Selects all orders in the current month i.e. all orders from ’01-07-2016′ to ’31-07-2016′

2. GREATER Clause in $X{}

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{GREATER, ORDERDATE, p_order_date} 

 
p_order_date = QUARTER-2 (Jan 2016 – Mar 2016)
Selects all orders after the end of “QUARTER-2” i.e. all orders after ’31-03-2016′

p_order_date = MONTH-6 (Jan 2016)
Selects all orders after the end of “MONTH-6” i.e. all orders after ’31-01-2016′

3. LESS Clause in $X{}

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{LESS, ORDERDATE, p_order_date} 

 
p_order_date = QUARTER-1 (Apr 2016 – June 2016)
Selects all orders before the start of “QUARTER-1” i.e. all orders before ’01-04-2016′

p_order_date = MONTH-6 (Jan 2016)
Selects all orders before the start of “MONTH-6” i.e. all orders before ’01-01-2016′

4. BETWEEN Clause in $X{}

4.1 Non-Inclusive between

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{BETWEEN, ORDERDATE, p_start_date,p_end_date}  

 
p_start_date = QUARTER-3 (Oct 2016 – Dec 2015)
p_end_date = QUARTER-1 (Apr 2016 – June 2016)

The WHERE condition translates to ORDERDATE > QUARTER-3 and ORDERDATE < QUARTER-1
Selects all orders after the end of "QUARTER-3" and before the start of QUARTER-1
i.e. all orders after '31-12-2015' and before '01-04-2016'

4.2 Inclusive between

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{[BETWEEN], ORDERDATE, p_start_date,p_end_date} 

 
p_start_date = QUARTER-3 (Oct 2016 – Dec 2015)
p_end_date = QUARTER-1 (Apr 2016 – June 2016)

The WHERE condition translates to ORDERDATE >= QUARTER-3 and ORDERDATE <= QUARTER-1

Selects all orders from the start of "QUARTER-3" until the end of QUARTER-1
i.e. all orders from '01-10-2015' until '30-06-2016'

4.3 Partially inclusive between

4.3.1

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{BETWEEN], ORDERDATE, p_start_date,p_end_date}  

 
p_start_date = QUARTER-3 (Oct 2016 – Dec 2015)
p_end_date = QUARTER-1 (Apr 2016 – June 2016)

The WHERE condition translates to ORDERDATE > QUARTER-3 and ORDERDATE <= QUARTER-1
Selects all orders after the end of "QUARTER-3" until the end of QUARTER-1
i.e. all orders after '31-12-2015' and before/equal to '30-06-2016'

4.3.2

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{[BETWEEN, ORDERDATE, p_start_date,p_end_date}  

 
p_start_date = QUARTER-3 (Oct 2016 – Dec 2015)
p_end_date = QUARTER-1 (Apr 2016 – June 2016)

The WHERE condition translates to ORDERDATE >= QUARTER-3 and ORDERDATE < QUARTER-1

Selects all orders from the beginning of "QUARTER-3" before the beginning of QUARTER-1
i.e. all orders starting from '01-10-2015' and before '01-04-2016'

These examples should help you implement most of the use cases that we come across.

References –
http://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/using-parameters-queries

http://community.jaspersoft.com/wiki/how-use-relative-dates-jrxml-static-reports

Shraddha Tambe | Helical IT Solutions

Organization Security In Jaspersoft

Organization Security In Jaspersoft

Security implied on the report based on the organization details of the logged in user can be brought into reality through the use of parameter “’$P!{LoggedInUserAttribute_organization}’”. Here we have called this parameter in the Input control which controls the output of the report and sets security on the report as per the logged in user and his role. The following steps are performed to implement the organization level security on report:

1) Go to repository, there in folders look for input control folder. In input control select organization-> right click->edit. There in “define query” step check if the parameter “’$P!{LoggedInUserAttribute_organization}’” is present. This parameter filters the content of report as per the value in the profile attribute set for the logged in user. This parameter can be used anywhere in the query especially in the where clause of query which will help in data filtration.
E.g: Here the value we used for attribute organization = ‘Apollo hospital’ and the query being used in input control is going to filter the content of report for the organization: ‘Apollo hospital’ through the parameter:-
“’$P!{LoggedInUserAttribute_organization}’” .

2) The parameter ‘$P!{LoggedInUserAttribute_organization}’ can be called anywhere whether in report or in the query of input control as it implements the security on the report based on the value set in profile attribute of logged in user.

3) Stored procedure like below can be written in the query of input control where parameter ‘$P!{LoggedInUserAttribute_organization}’ is called :

storedprocedure

In similar fashion you can customise the query to call the parameter as per the requirement.

Thankyou
Nisha Sahu

Date Field Format Customisation in Adhoc View on Japser UI

Date Field Format Customisation in Adhoc View on Japser UI

Often while working with Adhoc Reports on jasper we get limited option for the availble date format in jasper. There are number of ways to fix this but one I am going to discuss here is by adding date format options in the backend of Jasperserver.

How we can add the different date formats in the Jasper UI itself is what I am going to describe in this blog. Here we are going to add the date format for “06/24/2016 11:15:25 a.m”.

1.First navigate to the path:
apache-tomcat\webapps\jasperserver-pro\WEB-INF\bundles\adhoc_masks.properties
where jasperserver is installed.
2. Open the file adhoc_mask.properties.
3. Edit the file content which appear somewhat like this:-

blog 1

4. Add “ADH_100_MASK_timestamp_5 = MM/dd/yyyy HH:mm:ss a” at required location.
5. In similar fashion we can customise the UI for the required date formats.
6. After making the changes save the file and restart the server. As new changes get reflected only after restarting the server.
7. Once the server gets restarted in the jasperserver you can notice the new option for date format by clicking on the date field in adhoc view, which we have just added in the backend file.

blog 2

Similarly we can edit the formatting of integer and decimal. Adding the format options in this way gives you more generalised approach to handle data formatting related tasks in jasper adhoc.

Thanks
Nisha Sahu

Jaspersoft 6.2 Features and Upgrades

Dear Jaspersoft Users,

In this blog we would be talking about the latest version of Jaspersoft i.e. Jaspersoft 6.2 new features.

a. Advanced multi-tenant server administration capabilities

Jaspersoft in its latest release is providing more options to for administration of a multi-tenant envrionment. There is an option through which administrator can export resources for a particular user (which includes option to export data sources, domains, adhoc views, reports, dashboards, scheduled report job, other resource files, sub-organizations, dependencies, permissions, attributes and values).

Below snapshot can be referred to see the UI for the above functionality.

Jaspersoft 6.3

Jaspersoft 6.2

While exporting and then importing for some other user, the other user may/may not have certain accesses and privileges. Based on that proper warning messages appear like shown below

Jaspersoft 6.3 Import Warnings

Jaspersoft 6.2Import Warnings

 

b. Dashboard Improvements

There are certain small incremental improvements which Jaspersoft is making to make their dashboarding experience better (even though there is still a long way to go). In the latest release, Jaspersoft has added the option to add images to the dashboard. This image can also be fetched from a certain web link as well.

Also with the latest release, it is possible to export a dashboard in pdf/image/ODT format. Phantomjs libraries have been used for achieving this functionality. The input filters option can also appear as a popup options on top of the dashboard.

Jaspersoft 6.3 Dashboard

Jaspersoft 6.3 Dashboard

also with latest version, it is possible to drill down into the same panel by clicking on any portion of the chart.

 

c. Advanced charting customization options

Jaspersoft enterprise version uses highcharts. High charts provides a lot of customization option of the charts via APIs. In the latest release of Jaspersoft, they have created certain UI to trigger and have customization option of the charts. Using this some of the customizations which can be done like color, orientation, labels, reset option and position, title text, subtext, legend, panning, hover etc.

There is also a hyperlink of more information which opens a wiki page of Jaspersoft with more details regarding customization.

Jaspersoft 6.3 Charts Customization

Jaspersoft 6.2 Charts Customization

 

d. New charts

Treemap new chart has been added inside Jaspersoft adhoc reporting feature. Below snapshot is a treemap chart.

Jaspersoft Treemap Chart

e. Master scheduler view

With the latest version, Jaspersoft has a page wherein the loggedin user can see how many jobs have been scheduled and he is also having the option to selectively enable/disable those jobs. One user can not see the details of other users email scheduled jobs.

f. Reporting Improvements

There have been certain improvement on the jaspesoft studio or report designing level as well. In Jasper studio now there is an option to add a custom visualization component, thus any javascript (like D3 charts also) can be added inside and used.

– Also there is geojson support inside tibco geoanalytics and geomaps

In a frame multiple elements can be added, and then on resizing the frame the other elements present inside it gets resized. The same can also be done for tables so that columns fit the table element. Previously resizing was a very tedious job and this is a welcome improvement.

Jaspersoft 6.3 studio

 

Please get in touch with us at nikhilesh@helicaltech.com for any Jaspersoft, Pentaho, ETL related requirements or queries.

Thankyou

 

Beginner’s Guide to Crosstab in Business Intelligence Using Jaspersoft IReport

What is Cross Tab?

A Table is just of 2 Dimension but at times we do need our table to be represented in a 3 dimensional manner or in a Dynamic Manner.

Why?

Take for example a table with 2 data set i.e. in the row we have countries and in the column we have months of the year

Now at times our data set will have an extra data which needs to be represented in the table where then can we keep these column or even row?

So here Cross tab Helps in those situation to make the report more meaningful

Now we can have Rows as “Country” and a sub row “State” grouped by the country.

To get a better understanding I’ll be taking an Example to represent my data in-form of a cross-tab.

From the following

Tables :

  • sales_fact_1997 sf7
  • customer
  • time_by_day

Fields like country, state, month, store_sales and etc if required,
should be represented in Cross Tab.

 

Open Jasper-soft I Report

-> Click on File->New->Report->Select Any Template of your choice

->Remove the Bands you don’t require

in My case i just need the Title and Summary Band so i wont remove them

Connect to the required Tool/Database where your Query will be retrieved from

After connection

Enter your query by clicking on ->

Crosstab_blog

and then

Crosstab_blog_6

After entering your query select Read Fields and Click OK.

->Go to the palette section and Drag and Drop the cross tab to which ever band you want
(In my case “Summary” Band)

Now select Main Report Data-set and Click Next

Now In Your Row Group 1 select what do you want as the Group i.e. the 1st Dimension (in My Case Country)

Now in Row Group 2 select what do you want as the Group i.e. the 3rd Dimension which is a Sub-Group of the First Group (in My Case State_province)

As shown below:

Crosstab_blog_1

Then Click Next

Now in Your Column Group 1 Select what should be your Column Group which is your 2nd Dimension (month of year)

As shown below:

Crosstab_blog_2

Then click Next

Now in measure what will you like to calculate in your table? So you input in the Measure Field (in my case Store Sales)

And what function will you like to perform (in my case SUM of store sales)

As shown below

Crosstab_blog_5

Then click Next.

The Next Dialogue Box is of your Choice

Then click Finish

For any formatting select Cross-tab

Crosstab_blog_4

As shown above

And then perform your formatting techniques.

 

final output-> Crosstab_blog_3

By Sohail Izebhijie

Passing values as parameters to Subreport from main report in Jasper

Tool used: ireport

What is subreport?

Before going into details, let me tell you what is subreport and what is the relation between a main report and subreport. A subreport is a report which is used inside another report.  This functionality helps us during complex designs where the data comes either from different portions of a single document or from different datasources.

In some cases, there are some values which needs to be used both on the main report and the subreport. The values can be either the parameters used to call the report, variables or can be the fields generated from the query. In those cases we need to pass those values as parameters to the subreport which in turn can be used in the subreport. The main things that we need to take care of are the datatype and the naming convention.

How to pass values to subreport?

The following are the steps:

  1. First thing is to create the subreport and link the subreport with the main report. For example: Let the name of the main report be Main.jrxml and the subreport be Subrpt.jrxml.

subrptElement

 

You now have to link the subreport element that you just added to the main report, to your subreport file (the one you created in step 1) In order to do that, select the subreport element in the main report, and modify its parameters as follows:

  • Subreport Expression: “repo:/public/MainReport/Subrpt.jrxml”               à Subreport path
  • Expression Class: java.lang.String
  • Connection Type: Use a connection expression

Connection Expression: $P{REPORT_CONNECTION}

subrpt1

2. Now the link has been established between the main report and the subreport. We will get these parameters by passing them from the main report to the subreport. The rule is,for every field(be it either a variable/a query field/ a parameter) we want to pass to a subreporta similar parameter (with another name) has to exist in the subreportso that we can make the mapping. So we have to create a parameter on the subreport side which will have the same name as the name of the field/variable on the main report and the datatype should be the same also. For example: If the name of main report variable is X with datatype java.lang.Integer then the subreport should contain the same parameter of X with datatype java.lang.Integer. This is how Jasper understands and therefore allows the data to flow into the subreport.

3. Go to the main report and click on the subreport band. In the properties section on the right hand side corner of the report, go to parameters option(2nd last option in ireport). Click on the expression tab and a pop up window named Subreport Parameters will open. See below screenshot for better understanding.

subrpt_parameters1Untitledsubrpt_parameters

4.While passing the parameter to the report, the Name section is the section where the name of the parameter present in the subreport is specified and in the expression section is the value you want to pass from the main report. In the above screenshot, I am just passing the parameter of the main report as my passing parameter to the subreport.

Note: It is advisable to keep the name of the parameter in subreport and the parameter’s name in the main report same. However if you want to change the name of the subreport parameter then we have to first mention over here and then mention the same name in the subreport. But we can’t change the data type of the parameter unless we are changing the type in the connection expression.

Hence, this is how we pass values(parameters/variables/fields) of main report as parameters to the subreport. Now we use that parameter anywhere in the subreport.

Thanks, Nitish

 

 

Calling JRXML inside HDI(Helical Dashboard Insights) : –

             Calling  JRXML  inside  HDI(Helical Dashboard Insights) : –

This blog will teach you how to call JRXML inside HDI(Helical Dashboard Insights) : –

To call JRXML inside Helical Dashboard Insights , we need 4 files required for HDI and one JRXML file.

I have created one folder named “JRXML” and kept all the above mentioned files in this folder.

Files : [ callJrxml.EFW , template.html , tabular.efwvf , dataConn.efwd , getEmployeeInfo.jrxml ]

Database : mysql

Table : emp

Query : select empno,ename,job,sal fromemp;

#1 : using the above query , first created a report in iReport.

#2 : then removed the <queryString></queryString> contents from jrxml.

#3 : Added the query in “dataConn.efwd” file.

callJrxml.efw :

efwTemplate.html :

htmltabular.efwvf :

efwvf

dataConn.efwd :

efwdOutput :

outputThanks,

Rupam Bhardwaj

I-Report Bands

                                                                                                        I-Report Bands

 

A jasper report is composed by a set of sections named as “bands”.  Every band contains few properties such as height. Band height should be greater than 0, otherwise it will never be visible to user.  The band height can grow if elements inside it are stretched.

Default Bands in Report:

Title Band

Printedonly one time and it’s the first band. It can be printed on a separate page. You can print report title in this band.

 

Page Header

Printed on each page.

 

Column Header

It prints on each page if page contains detail band. If page splits it prints on each page. You can use this to crate manual table and put column name in this band.

 

Detail

It prints for each record in the source.

 

Column Footer

It is same as column header band, but it prints in footer instead of header.

 

Page Footer

Same as Page header, but it is footer section.

 

Summary

It prints only once at the end of the report. It can be printed on separate page also.

 

Background

It can be used to define a page background. You can also use image in this band.

 

The position of bands cannot be modified. For each band, you can modify only band height.

This is the sequence of bands in any jasper report (Fig 1.0). You can hide/unhide bands according to your needs.

If you click on any band, you can see the properties of that band in your right hand side panels (Fig 2.0). (If properties panel is not visible, Click on menu Window > Properties.

 

FIg 1.0

FIg 1.0

Fig 2.0

Fig 2.0

 

 

 

 

Thanks

Sharad Sinha

How to restrict Multiselect input control values in jaspersoft :

How to restrict Multiselect input control values in Jaspersoft 

This blog will teach the reader , how to restrict multiselect input control values in jasper without hampering the performance .

DB : Postgres (foodmart)

Table : customer

Reporting tool : iReport

Server : Jasperserver 5.6

I/P Parameter : country

Requirement : Get All the informations from customer table where country name is either USA or Mexico or Canada

Condition :

  1. you can not select more than 2 value in “country” input parameter
  2. if more than 2 values are selected in “country” input parameter then the main report query should also not get executed. This will unnecessarily hamper the performance.

Solution :

  1. create multiselect input control named as “country”

select distinct country from customer

  1. create a sub report which will display all the informations from customer table on selection of “country” parameter

and give its print when expression as :

$P{country}.size() <= 2

  1. create a text box in the header , write a message init as :

“You can not select more than 2 values in country parameter”

And give its print when expression as :

$P{country}.size() > 2

  1. in the sub report , query will be :

select * from customer

where $X{IN,country,country}

limit 20

  1. publish the report on jasper server.

O/P

# 1 : when 2 values selected in country parameter

lenlt2#2 : when more than 2 values selected in country parameter

lengt3

Thanks,

Rupam Bhardwaj

Remove Repeated Column header, Page Header, Page Footer in CSV Export (JASPER)

In CSV export, we want only column header once and data should be printed. We do not want page header, page footer and repeated columns header. So in order to achieve this do the following.

    1. Remove Page Header:
 
<property name="net.sf.jasperreports.export.csv.exclude.origin.band.(uniqueValue)" value="pageHeader"/>
    1. Remove Page Footer:

<property name="net.sf.jasperreports.export.csv.exclude.origin.band.(uniqueValue)" value="pageFooter"/>
    1. Remove Title Band:

<property name="net.sf.jasperreports.export.csv.exclude.origin.band.(uniqueValue)"value="title"/>
    1. Remove Repeated Column Header:

<property name="net.sf.jasperreports.export.xlsx.exclude.origin.keep.first.band.(sameValue)" value="columnHeader"/>
<property name="net.sf.jasperreports.export.xlsx.exclude.origin.keep.first.report.(sameValue)" value="*"/>

Some times we are using sub report to display page header and page footer. Do the following to remove the it in CSV Export.

    1. Remove Sub Report:

<property name="net.sf.jasperreports.export.xlsx.exclude.origin.report.(sameValue)" value="Actual Report Name not the sub report name"/>
<property name="net.sf.jasperreports.export.xlsx.exclude.origin.band.(sameValue)" value="detail"/>

This is not working for crosstab.

Thanks & Regards,
Fahad Anjum