Table of contents in Jasper Community 6.3

Table of contents in Jasper Community 6.3

Here I’ll discuss how to meet requirement when in jaspersoft you have been asked to create index. Index as it is available in many books, exactly in same way. That is, in index there will be list of contents in the report along with page number. Not many knows that jasper has this feature available : report book, which one can used to publish report containing table of contents. Thus, proceed as follows :

How to create report and add it to the report book:

  1. Click to open the selection window.
    Jasperstudio->jasper Report->next->Blank A4 ->next
  1. Select the root folder->give suitable file name jrxml-> next
  2. In the Data Source window, select foodmart DB – Database JDBC Connection, and enter the following query:

SELECT * FROM customer ORDER BY state_province limit 200

  • Add the required data fields in the subreport from the list.
  • State_province
  • Occupation
  • Education
  • Phone1
  • Lname
  • Customer_id

Add the state_province field in the group by field.
Compile the Report.

Add report  to the report book:

  1. Click to open the selection window.
    Jasperstudio->jasper Report-> report books->abstract book-> next->report book-> query->finish.
  1. Drag Content_Page.jasperfrom the Project Explorer into the Content group.
  2. In the Properties view of Content_Page.jasper, -> data->click Edit Parameters.
  3. In the Report Part Parameters window, click Add to open the Parameter Configuration dialog.
  4. In the Parameter Name field, enter REPORT_CONNECTION.
  5. Click to open the Expression Editor.
  6. In the first column, select Parameters.
  7. From the center column, double-click REPORT_CONNECTION parameter connectionto add it to the editor field, and click Finish. The expression appears in the Parameter Configuration field.
  8. Click OK and confirm the parameter has been added to the Part Parameters list, then click Finish.

Similar way add as many content page in the report.
Preview the report to see the report book.
Also, we can add number of subreports in the main report and add that report in content page. In this case while previewing report we ll be able to see the subreport name and the respective page number in which it got rendered.

For further queries and help please comment.

Refer:http://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/report-books

Thanks,
Nisha Sahu
BI Developer

Fixed series color for specific labels in Jasper Jfree charts

Fixed series color for specific series labels in Jasper jfree charts

We often face the situation where it has been asked to implement specific series color for each particular series in the different charts of jasper. Thus,here I am trying to provide solution for same:

Create two jasper reports one to publish pie chart and other to publish stacked bar chart.

In pie chart,

There are series and count values required in pie chart:

1

Let us suppose for the above mentioned pie chart we need following color customisation for each series label i.e,

  • Series1 – green
  • Series2 –orange
  • Series3- purple
  • Series4- dark blue

 

In order to achieve this, we can write custom code in java as follows:

public class PiePlotEnclosingReport implements JRChartCustomizer {

             public void customize(JFreeChart chart, JRChart jasperChart) {

plot.setSectionPaint(“seies1”, new Color(63, 156, 53));

plot.setSectionPaint(“seies2”, new Color(0, 152, 219));

plot.setSectionPaint(“seies3”, new Color(140, 24, 154));

plot.setSectionPaint(“seies4”, new Color(233, 131, 0));

}

}

Create jar file of the java code and add it in chart customiser property of chart in the format: ‘packagename.classname’ as follows:-

Refer the screenshot:

2

3

Check the result, on executing report. It will be shown as in the above screenshot. One can very well  identify how the colors for each series type got changed through the chart customiser code we have used.

Similarly, we can customise the series color in stacked bar chart using following piece of code:

public class StackedBarChart implements JRChartCustomizer {

       public void customize(JFreeChart chart, JRChart jasperChart) {
       CategoryItemRenderer renderer = chart.getCategoryPlot().getRenderer();

              CategoryDataset catDS = chart.getCategoryPlot().getDataset();

              Color seriesColor = null;

              for (int i = 0; i < catDS.getRowCount(); i++) {

                     if (catDS.getRowKey(i).toString().equalsIgnoreCase(“series1”)) {

                           seriesColor = new Color(255, 175, 175);

                     } else if (catDS.getRowKey(i).toString()

                                  .equalsIgnoreCase(“series2”)) {

                           seriesColor = new Color(166, 25, 46);

                     } else if (catDS.getRowKey(i).toString()

                                  .equalsIgnoreCase(“series3”)) {

                           seriesColor = new Color(63, 156, 53);

                     } else if (catDS.getRowKey(i).toString()

                                  .equalsIgnoreCase(“series4”)) {

                           seriesColor = new Color(16, 24, 32);

                     }

       renderer.setSeriesPaint(catDS.getRowIndex(catDS.getRowKey(i)),

                                  seriesColor);

              }

}

}

Hope it helps!!

Thanks,

Nisha Sahu

BI Developer

 

 

 

How to use Google maps in Jaspersoft Studio

How to Use Google Maps in Jaspersoft Studio

The report in this example is developed on Jaspersoft Studio version: 6.3.1 final and deployed on Jaspersoft server version: 6.3.0 Enterprise edition.

Step 1: Create a report in jasper studio. In this example report is called GoogleMapsTest.

Screenshot (193)

1- Delete all the bands except Title and summary band. Use the query “Select 1 as one” in main data-set query.

Step 2: Adding the jasper map component to report.

Screenshot (173)

1- Add the text box to Title band and insert any relevant text into it.

2- Drag the Map component from Palette on to the Summary band of the report.

3- Add a data-set named “Markers” to the report, and use this query in it.

Query:

select ‘NorthCharleston’ as CityName,’-80.02842′ as Longitude,’32.92896′ as Latitude
union
select ‘GREENVILLE’ as CityName ,’-82.33626515′ as Longitude ,’34.79360263′ as Latitude
union
select ‘ZIBO’ as CityName,’117.8351787′ as Longitude,’37.17124911′ as Latitude
union
select ‘Mumbai’ as CityName, ‘72.9227767’ as Longitude, ‘19.10662726’ as Latitude

Step 3: Add markers to the map.

Screenshot (175)      Screenshot (176)

1- Click on the Map and select the Markers tab from the Properties section.

2- Add the created data-set to the marker by selecting the Data-set tab.

Step 4:Add markers properties.

Screenshot (177)

1- For adding marker properties, click the add button on right side, and insert the following details.

i.) Longitude & Latitude : For adding Longitude & Latitude, click the the properties icon on the right hand corner of the input text field. Check the “Use Expression” box and provide the latitude and longitude fields in their appropriate input boxes.

Screenshot (179)

ii.) Adding Mouse-over(Information appearing when hovered on marker) : For mouse-over information, insert data into the “Title” box in the window.

Screenshot (181)

iii.) Adding Pop-up window content(Content shown after clicking the marker) : For adding pop up window content, use the “Info Window”  text boxes.

Screenshot (183)

Step 5: Deploying the report in jaspersoft server

Publish the report on jasper server.

Screenshot (192)

………. and Voila..!!!!!!!!!!!

Thank You

Ravi Bhatta

 

 

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 [email protected] 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