Passing parameters from crosstab to subreport in jasper

Passing parameters from Crosstab report to drilldown report in Jasper Studio

Scenario: I have created a crosstab report and When user clicked on measure it should open a another details report by passing the same parameters being selected in crosstab report

Jasper Studio Workbench
Jasper Server
Database: Postgres – Foodmart

Main Report

  1. Created a cross tab report having Region and area as row groups, month wise store cost, sales units as measures with Region and Area as parameters


  1. Created Region and Area parameters in main report section


  2. Create same parameters inside the cross tab


  3. While creating parameters in crosstab, if you are facing any issue then add parameters from source code

    <crosstabParameter name=”Region” class=”java.util.Collection”/

    <crosstabParameter name=”Area” class=”java.util.Collection”/>

    Drill Down Report

    1. Create a detailed report having few columns and add region, area and month as parameters

      drilldownCase: Whenever user clicked on a measure in the main report, it should take respective region, area and month values and it should render drill down report with selected values. 

      1. Add Hyperlink properties to measures in the cross tab main report

      Click on element, in properties pane click on hyperlink, change values of link target to blank and link type to reportExecution. Click on parameters and add parameters.

      Add drill down report path in _report and region, area and month parameters


    2. Run the report in interactive mode and check click icon will be displayed on the measures and click on that. It should open drill down report

      crosstabhyperHyperlinks are displayed in blue color. Click on measure it will open drilldown report but selected input control parameters values are not passing from main report to subreportTo handle this case, we have to add one property in main cross tab report

      Click on crosstab element in outline pane, Open properties and click on crosstab

      There is one attribute named as Parameters Map Expression.

      Enter $P{REPORT_PARAMETERS_MAP} inside the expression


      Save and run the report.

      Now Click on measure hyperlink, it will load drill down report

      Main Report – Cross tab

      – Clicked on Sales column in January Month, CA area and USA Region


      Drill down report

      – Getting January, CA and USA as parameters from the main report


Ramu Vudugula
BI Developer

Anchors in JasperReport

Anchors in JasperReport

What is anchor?
Anchor provides easy access to different sections of the  report through link. One can implement this functionality in jasper using anchor expression in hyperlink. Anchor should always return ‘java.lang.String’ values.
While creating jasperreports we can use anchors in text field or image element component in the irpeort. Most important thing about these anchors is that they are also present in the pdf export of the report.

There are different types of anchor expression in ireport:

1. Local anchor: using this type of anchor expression one can tag the specific content in the report .

Creating a Local Anchor Example

<reportElement x="4" y="4" width="450" height="20"/>
<font size="14" isBold="true"/>
<textFieldExpression class="java.lang.String">"TITLE section"</textFieldExpression>

The <anchorNameExpression> tag was used here in order to associate a local anchor, named title, with this text field.

Creating a Local Anchor Hyperlink Example

<textField hyperlinkType="LocalAnchor">
<reportElement x="4" y="40" width="400" height="20" forecolor="#FF0000"/>
<textFieldExpression class="java.lang.String">">> Click here to go to the title section."</textFieldExpression>

The hyperlink created using the <hyperlinkAnchorExpression> tag points to the local anchor created above.

2. Local page: using this type of anchor expression one can tag the specific page in the report .

Creating a Local Page Hyperlink Example

<textField hyperlinkType="LocalPage">
<reportElement x="5" y="65" width="300" height="15" forecolor="#008000"/>
<textFieldExpression class="java.lang.String">"  >> Click here to go to the second page."</textFieldExpression>

The hyperlink created using the <hyperlinkPageExpression> tag points to the second page of the current document.

3. Reference URL in the anchor: Using this anchor we can also put the URL in the report.

Creating a Reference Hyperlink Example

<textField hyperlinkType="Reference">
<reportElement x="5" y="95" width="300" height="15"/>
<textFieldExpression class="java.lang.String">"  >> Click here to go to"</textFieldExpression>

The hyperlink created using the <hyperlinkReferenceExpression> tag points to the site.

4. Remote anchor hyperlink: The hyperlink created using the <hyperlinkReferenceExpression>and <hyperlinkAnchorExpression> tags points to an anchor named ‘title’ found in the HyperlinkReport.pdf document, saved in the current directory.

Creating a Remote Anchor Hyperlink Example

<textField hyperlinkType="RemoteAnchor">
<reportElement x="5" y="125" width="350" height="15"/>
<textFieldExpression class="java.lang.String">"  >> Click here to go to another PDF file."</textFieldExpression>

5.  Remote page hyperlink:
The hyperlink created using the <hyperlinkReferenceExpression> and <hyperlinkPageExpression> tags points to the second page of the HyperlinkReport.pdf document, saved in the current directory.

Creating a Remote Page Hyperlink Example

<textField hyperlinkType="RemoteAnchor">
<reportElement x="5" y="125" width="350" height="15"/>
<textFieldExpression class="java.lang.String">"  >> Click here to go to another PDF file."</textFieldExpression>

Nisha Sahu

Python – Get data from CSV and create chart

Python – Get data from CSV and create chart

Hello everyone, this blog shows how we can import data from csv and plot as mesg grid chart in python libraries, through the help of Pycharm IDE (Community Version)

Prerequisite :

The following are the steps for creating mesh-grid in pycharm :
1. Installing NumPy,Pandas and Matplotlib libraries in Pycharm IDE :

  • 1. Create a scratch file in Pycharm.
  • 2. Click on “File” and goto settings.
  • 3. Goto “Project Interpreter” and click on ‘+’ symbol to add new libraries.
  • NumPy

2. Writing python code for importing csv data and creating meshgrid :

  • 1. Create a csv file containing these data “-5,5,0.01”.
  • 2. Use this python code in your scratch file .
  • import csv
    import numpy as np
    import matplotlib.pyplot as plt

    point = ""
    with open('C:/Users/Helical/Desktop/Blog/Python/MeshGrid.csv','r') as csvfile:
    meshreader = csv.reader(csvfile, delimiter=',')
    for row in meshreader:
    point = point + ', '.join(row)
    meshpoints = point.split(',')
    points = np.arange(float(meshpoints[0]),float(meshpoints[1]),float(meshpoints[2]))
    dx, dy = np.meshgrid(points,points)
    z = (np.sin(dx)+np.sin(dy))
    plt.title('plot for sin(x)+sin(y)')

  • If you are able to sucessfully run the code, then you will generate mesh grid in an new Pycharm window.
  • GraphMatplotLib

Loops in Pentaho Data Integration

Loops in PDI


If only there was a Loop Component in PDI *sigh*.

But we can achieve Looping Easily with the Help of few PDI Components. Let’s take a requirement of having to send mails. Now if the mails id and files to be sent where fixed we could just use a property file and send the mail id’s as a parameter.

but here mail ids will be coming in from a Table source and then mail id’s can never be the same.
So how did i approach this?

let’s say mail id’s are from Table: mail_address so now my aim here is to first add a row number to each record line and a Maxcount of all the records at each line, *Keep In Mind of the Maxcount*

and then pass it as a filter clause

where row_number = 1

now my assumption is the query should execute and return one row and then the column values like sender mail, receiver mail will be set into a variable and then passed to a Mail Transformation Component;

But how can we increase the value of the Where clause i.e how can we make

where row_number = 1 to

where row_number = 2

where row_number = 3 and so on.


So here is were Looping is required tp we take a variable and increment the value.

1. Take a Set Variable at the Job Level [Initialize Loop] and then set a variable loop and assign the value to your initial value as shown below: In My case loop value = 1



Now next take a Transformation to get the variables and set the variables as shown below:

resultop1Now in my scenario I have a Modified JS because few conversions were required hence after that set the variable.

Next We need Transformation where we pass our variable of loop into our Query
as shown below



SELECT *,ROW_NUMBER() OVER (Order by createfolderpath) as row_num,COUNT(dummy)           OVER (partition by dummy) as maxcount FROM (SELECT * FROM
SELECT DISTINCT replace(createfolderpath ,’/’,’\’) as createfolderpath,1 as dummy
FROM reportpaths
Cross JOIN [TEST_SLSDMBKP].[dbo].[job_mailing_address]
scheduled_flag = ‘Y’
row_num = ${loop}
ORDER BY row_num,receivers_email,createfolderpath

Now here in my table input, 1 row will be the output so hence I pass the required columns into the Modified JS and then Set them as Variables.


Next at our Job Level we use a JavaScript Component to increment the loop value to 2 as shown above.

Next at our Job Level we add a new transformation which does our requirement of Sending Mails as shown below:


And we pass all the required variables into the Mail Component as shown below:resultop8

RECEVIERSEMAIL and all are values from our DB we Set as Variables.

After that we go to our JOB Level and we use a Simple Evaluation Component

To stop the Loop after a particular condition


Now ${MAXCNT} is the  Maxcount of all the records at each line from our Query we sent as variable

Now once loop number is 6 and Maxcount is  5 then it stops the loop and ends else

It continues and goes to the next Component which is Wait Component which in a previous blog it was specified the importance of a WAIT Component.

So that’s it we went through looping in PDI.

Hence if we have any other approach, please feel free to drop it in the comment below

Sohail Izebhijie

Analytic Query in Pentaho Data Integration

Analytic Query in Pentaho Data Integration [LAG & LEAD]

The could be a time we get a requirement as follows:

Fetch Every Purchases Sum for each Customer in a year, having the following columns

customerid, customername, productcost, purchasemonth

Now this looks easy, but the tricky part would be fetching the


he/she made a purchase

If your using a PostgresDB then this will be easy because there are various windows functions that will help you in acquiring your result with ease

But if your DB doesn’t support Windows Functions then it could be a problem.

So now Pentaho will help you reach your result in very less hustle with the help of the Analytic Query Component

Now lets build our query for one customer

sf97.customer_id as CustomerID,
co.fullname as CustomerName,
SUM(sf97.store_sales) as ProductCost,
tbd.the_month as PurchaseMonth
from sales_fact_1997 sf97
JOIN time_by_day tbd on tbd.time_id = sf97.time_id
JOIN customer co on co.customer_id = sf97.customer_id
WHERE sf97.customer_id = 4873

ORDER BY sf97.customer_id,tbd.month_of_year

Now we Open Up our PDI,  Create a new Transformation,

  • Select Table Input as your Source Table
  • Next Select the Analytic Query


  • Open up Analytic query
  • Now from our requirement we want to fetch the PreviousPurchaseMonth he/she made a purchase for each customer (What month he/she previously made a purchase)
    so the unique item here is the customerID
  • Then in the Group Field Select the customerid
  • In the Analytic Functions
    Enter the name of your new field
    Subject will be the purchasemonth Column because our new column is dependent on that

Type: This can be Lag (Behind) or Lead (Forward)
Click ok

Now right click on Analytic Query Component and Select Preview -> Quick Lunch


There you go.

Note make Sure in your query you have and order by based on your requirement.

Ehizogie Sohail Izebhijie

Get historical Twitter data using Twitter4j libraries in java

Get historical Twitter data using Twitter4j libraries in java

Pre-requistes :
1. Valid twitter account
2. Some knowledge of core Java.
3. Any IDE for running Java programs and knowledge of using them. (Eclipse or InteliJ)
This blog covers the following steps for getting twitter historical data :
1. Create app in twitter.
2. Download Twitter4j libraries (Jar files).
3. Write a program in Java to get historical twitter data using particular query string.

Steps are as follows :

    • Step 1: Creating App in twitter :
      1. Visit the twitter developer’s site. (
      2. Sign-in with twitter account.
      3. Go to “My Applications” area and click on “Create a new application”.
      4. Fill in the application details. You can ignore the “Callback URL” field but you have to provide your website URL.
      5. After you have filled the form and submitted it, you will be directed to a page which has option to generate tokens.
      6. Click on “Create Your Access Tokens” button and choose your preferences.
      7. Make a note of/copy the following values :
      i.Consumer Key
      ii.Consumer Secret
      iii.OAuth Access Token
      iv.OAuth Access Token Secret

Step 2: Downloading “Twitter4J” libraries :
1. Download twitter4j libraries from this site
2. Extract the downloaded zip file in your PC.

Step 3: Write Java program for fetching historical data from twitter using Twitter4J libraries.

1. Create a project in your IDE, and include all the jar files you have in the twitter4J zip file.

2. Java Code  :

Main Class : TwitterMain

package com.helical.twitter;

import java.util.ArrayList;
import java.util.HashMap;

import twitter4j.Twitter;

public class TwitterMain {
	private static String AccessToken = "8xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxo";
	private static String AccessSecret = "sxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxC";
	private static String ConsumerKey = "Uxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
	private static String ConsumerSecret = "rxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

	private static HashMap authTokenMap = new HashMap();

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		authTokenMap.put("AccessToken", AccessToken);
		authTokenMap.put("AccessSecret", AccessSecret);
		authTokenMap.put("ConsumerKey", ConsumerKey);
		authTokenMap.put("ConsumerSecret", ConsumerSecret);

		TwitterAuth twitterAuth = new TwitterAuth();
		TwitterData twitterData = new TwitterData();
		Twitter authObject = null;
		String topic = args[0];
		ArrayList tweets = new ArrayList();
		try {
			authObject = twitterAuth.authenticate(authTokenMap);
		} catch (Exception e) {
			// TODO: handle exception
		try {
			tweets = twitterData.getTwitterData(authObject, topic);
		} catch (Exception e) {
			// TODO: handle exception
		System.out.println("Tweets related to " + topic + " : " + tweets);



Authentication class : TwitterAuth

package com.helical.twitter;

import java.util.HashMap;

import twitter4j.Twitter;
import twitter4j.TwitterFactory;
import twitter4j.conf.ConfigurationBuilder;

public class TwitterAuth {
	public static Twitter authenticate(HashMap<String, String> authTokenMap) {

		ConfigurationBuilder cb = new ConfigurationBuilder();

		Twitter twitter = new TwitterFactory(;
		return twitter;



Fetch twitter data class : TwitterData

package com.helical.twitter;

import java.util.ArrayList;
import java.util.List;

import twitter4j.Query;
import twitter4j.QueryResult;
import twitter4j.Status;
import twitter4j.Twitter;

public class TwitterData {

	public static ArrayList getTwitterData(Twitter authObject, String topic) {
		System.out.println("Inside tweet data method: "+authObject + " , "+topic);
		ArrayList tweetList = new ArrayList();
		try {
			Query query = new Query(topic);
			QueryResult result = null;
			do {
				result =;
				List tweets = result.getTweets();
				for (Status tweet : tweets) {
			} while (result.hasNext() == true);
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("Failed to search tweets: " + e.getMessage());
		return tweetList;

3. The program takes the search topic in the form of command line argument.After providing command line argument, this code will use twitter4J methods to fetch data related to the topic entered, and give output in JSON format.

Feel free to play with the code and please comment if you find any issues with the code.
So, that’s it.. go knock your self off…!!!!

Latest features available in JasperReport Server 6.4

Latest features available in JasperReport Server 6.4

Following are the different features available in JasperReport Server 6.4
1. Export options for organization admins.
In JasperReport Server 6.3 only ROLE_SUPERUSER has export options but in JasperReport Server 6.4 export option is available for organization admins which is the User with ROLE as ROLE_ADMINISTRATOR.Now ROLE_ADMINISTRATOR have option to export.
2. Superusers have new option to export resources and folders with full resource path.
In JasperReport Server 6.4 superusers have new option to export resources and folders with full resource path.We have option to check/uncheck option for full source path while export.You can see the folder structure with check/uncheck full resource path after export shown below :

For example : If we export Adhoc Component with full resource path the folder sturcture will be :


Without full resource path, in export will get path as below :


3. Organizational admins have access to include/exclude dependencies.
In JasperReport server 6.4 for Organizational admins, we have option to include/exclude dependency option while export this will help us if you dont want to override the previous resource changes while import.Let us assume
if you have your own datasource and you want to import same Resource having its own datasource which you dont want to override in such cases this will help you.

4. Dashlets have hyperlink control.
In JasperServer 6.4 dashboard , new functionality of hyperlink is provided for each dashlet shown below :
You use hyperlink control for dashlet we need to enable the hyperlink control
Hyperlink control provides different actions :
1. Update Page : On dashlet hyperlink(click), dashlet will get update.
2. Open new page : On dashlet click, hyperlinked report/resource get open in new page.

3. Replace page : On dashlet hyperlink click , dashlet get replace with another hyperlinked report.
5. Undo-Redo action buttons changes in input control values.
In JasperServer 6.4 dashboard , undo-redo action buttons changes to input control values which we have set in dashboard input control.

6. More formatting options for text dashlets.
In JasperServer 6.4 dashboard , text dashlets have more formatting options.You can change the font type ,font size, alignments etc for text dashlets shown below :
We can set hyperlinks on text dashlets.To set hyperlink on text dashlet we need to enable the hyperlink as shown below :
In this way , we can do more formatting changes for text dashlets.
7. Include theme option while import option.
In JasperReport Server 6.4 include theme option is provided while import , if user dont want to include the theme he/she can uncheck the include theme option as shown below :
Hope it helps 🙂

Thanks ,
Sayali | Helical IT Solutions

Hide logout option for externally authenticated users in JasperReport Server

Hide logout option for externally authenticated users in Jasper Report Server

  • An external user is a user outside of an organization who does not directly login to Jasper Reports Server.
  • JasperReports Server does not store the passwords of external users, and is not impacted by changes to user passwords or user password policies on the external authority only the login name is stored in an external user account.
  • In JasperReports Server if you want to hide logout option for externally authenticated users, following are the steps to achieve it :

Used Jasper Report Server Version : 6.4.0

  1. Goto to path : \WEB-INF\decorators\
  2. Open the decorator.jsp file
  3. Comment below code :
  4.         <authz:authorize ifNotGranted="ROLE_ANONYMOUS">
            <li id="main_logOut" class="last" role="menuitem">
            <a id="main_logOut_link" tabindex="-1">
            <spring:message code="menu.logout"/>

  5. Add below code before commented code which check for externally authenticated user and hides the logout option for it :

  6. <c:set var="isExternallyDefinedUser" value="<%= ((com.jaspersoft.jasperserver.api.metadata.user.domain.User)SecurityContextHolder.getContext().getAuthentication().getPrincipal()).isExternallyDefined()%>"/>
    <c:if test="${!isExternallyDefinedUser}">
    <authz:authorize ifNotGranted="ROLE_ANONYMOUS">
    <li id="main_logOut" class="last" role="menuitem">
    <a id="main_logOut_link" tabindex="-1">
    <spring:message code="menu.logout"/>

  7. Save decorator.jsp file

To test hide logout option for externally authenticated user :

  1. Custom authentication
  2. Manually update “externallydefined” flag in database and test.

We can test hide logout option for externally authenticated user by second mentioned way :

  1. Connect to Jasperserver postgres database.
  2. Check users present in jasperserver by executing below query :

Select * from jiuser;

  1. You will get all users details which are present in jasper server , check the id of user to which you want to make it as externally defined user.
  2. Let us assume if you want to make testuser as externally defined user and its id is 65874 then to make is as externally defined user execute below query which set the “externallydefined” flag/value to “t”.

Verify the set flag value by executing Select * from jiuser;

  1. Now restart the jasperserver and its repository(postgres).
  2. Login to jasper server with superuser access through browser.
  3. Goto->Mange->Users and select testuser.
  4. Click on Login as User button.


9.You will directly logged with testuser which don’t having the logout option .





Sayali Mahale | Helical IT Solutions

Grouping in Jasper Report/Table

Grouping in Jasper Report/Table

Grouping in a canned report which can be developed in Jasper Studio is easily done in Cross-tab. Cross-tab can be used when a measure has to be calculated on more than one dimension and in that case it is simpler to use cross-tab. Limitation of using Cross-tab is that it has fixed lay-out and customization of look and feel gets complex. One can use grouping available in reports and tables in special cases like.

1. Displaying the groups in new page every time
2. when Header/ Footers need customization

Grouping in table

1.Right click on the dataset and click on add group



2. create variables and store calculated measure by aggregating(sum, count, etc…,) and set the property to ResetType as the Group created.


3. Add row in the Group Header of the table.


If required we can group the cells and make a single cell of the group as shown in the following


4. Add row in the Group Footer of the table, drag and drop the variables in to respective cells.


Grouping in Report

1.Right click on the report and click on add group, create group by giving the expression or selecting the fields.
NOTE: fields must be pulled in the main dataset query



2. create variables and store calculated measure by aggregating(sum, count, etc…,) and set the property to ResetType as the page.


3. Add row in the Group Footer of the table, drag and drop the variables in to respective cells.


Displaying the groups in new page every time

1. add the table into Page Header


2. Set the property Start New Page


Visualize.js – Hyperlinks API

Visualize.js – Hyperlinks

In the post, we will discuss how we can handle hyperlink execution in jaspersoft reports that are embedded using visualize.js.

If you have not worked on rendering reports through visualize js , this post may not be the best place to start. You may check this one out.

As we all know, jasper hyperlink’s are of various types – report execution, reference, local / remote anchor or pages. We will look into what information do we get about the hyperlinks in the embedded reports when we click on them and how we can utilize this information to take appropriate action on the event.

As an example, we will take one of the sample report from the jasper samples that come with the installation – SalesByMonthReport. I have made a small change to this report and added a hyperlink in the page footer of the type “reference” which opens “” webpage.


Below is the HTML and the Java Script Code for embedding the report using Visualize JS with hyperlink handling –
—- HTML —–

<!----- JQuery,underscore js is required for this code, has been included from external resources in jsfiddle--->
<script type='text/javascript' src=""></script>
<div id="container"> Main report Appears here</div>
<div id="container2">Drilldown Appears here</div>

 I have created 2 place holder divisions, “container” to render the “SalesByMonthReport” report. This report has a drilldown report which opens when we click on any of the months in the table. When user clicks on any of these hyperlinks, we will display the drilldown report in “container2”

— Java Script —

var reportUri = "/public/Samples/Reports/SalesByMonthReport"
    auth: {
        name: "superuser",
        password: "superuser"
}, function (v) {
   var report ={ 
        resource: reportUri, 
        container: "#container", 
        params:{"startMonth": ['1'],"endMonth": ['12']},  //defaults
        linkOptions: {
       	   beforeRender: function (linkToElemPairs) {
            events: {
                "click": function(ev, link){
                  if(link.type == 'ReportExecution'){
                            resource: link.parameters._report,
                            params: {
                                monthNumber: [link.parameters.monthNumber]
                  else if(link.type == 'Reference'){
        success:function () {
            alert('Report rendered successfully');
        error: function (err) {
   function showCursor(pair){
           var elink = pair.element;
      = "pointer";

  Let’s concentrate on the “linkOptions” portion of the call to the Report API. It allows you to control the behaviour of the Hyperlinks.

beforeRender: function (linkToElemPairs) {

  In “beforeRender”, we are just trying to change the cursor type when mouse moves over the element which has the hyperlink. I am not going into details of this code. However, we can see that there is a way to add any logic that might be required before the links are rendered.

events: {
            "click": function(ev, link){
				if(link.type == 'ReportExecution'){
						resource: link.parameters._report,
						params: {
							monthNumber: [link.parameters.monthNumber]
				else if(link.type == 'Reference'){;

  In “events”, we can define the behaviour in case of various events, mainly the click event. Inside the click event callback function, we get access to the definition of the hyperlink on which click has happened. You basically get access to all the hyperlink properties that we define at design time.
In our example, we have 2 types of hyperlinks – report execution & reference. Lets see what we get for each of these types and what action we have taken on them using the information.

Report Execution : When user would click on the link on months in the table –


Below is the JSON we get in the “link” parameter of the click callback (“click”: function(ev, link))


As you see, in our code, we check for “link.type” value and then in case it is “ReportExecution”, we use “link.parameters._report” & “link.parameters.monthNumber” values to call the Report API again and display the drilldown report in “container2”.

Reference : When user would click on the hyperlink in the page footer –


Below is the JSON we get in the “link” parameter of the click callback (“click”: function(ev, link))


As you see, in our code, we check for “link.type” value and then in case it is “Reference”, we use “link.href” to open the hyperlink in a new window.

So, with this, we have successfully handled 2 different types of Hyperlinks that we come across in reports. Similarly, you can also write your logic to handle the other types as per your requirement.

JS fiddle of the example :

You may also check the below blog links for learning about – authentication, report rendering, input controls API of visualize JS.
First encounter with Visualise.js
Visualize.js – The Input Controls API

Visualize JS API reference

– Shraddha
Helical IT Solutions