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

Capture1


Capture2

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

Capture3

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

Capture4

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

Capture5

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

Capture6

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

Capture7

Capture10

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

Capture8


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

Capture9

Displaying the groups in new page every time

1. add the table into Page Header

Capture11

2. Set the property Start New Page

Capture12

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 “http://helicaltech.com/” webpage.

RenderedReport1

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="http://192.168.2.141:8082/jasperserver-pro/client/visualize.js"></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"
visualize({
    auth: {
        name: "superuser",
        password: "superuser"
    }
}, function (v) {
   var report = v.report({ 
        resource: reportUri, 
        container: "#container", 
        params:{"startMonth": ['1'],"endMonth": ['12']},  //defaults
        linkOptions: {
       	   beforeRender: function (linkToElemPairs) {
                linkToElemPairs.forEach(showCursor);
            },
            events: {
                "click": function(ev, link){
                    console.log(link);
                  if(link.type == 'ReportExecution'){
                  		v("#container2").report({
                            resource: link.parameters._report,
                            params: {
                                monthNumber: [link.parameters.monthNumber]
                            }, 
                        });     
                  }
                  else if(link.type == 'Reference'){
                  	window.open(link.href);
                  }
                }
            }    
        },
        success:function () {
            alert('Report rendered successfully');
        },
        error: function (err) {
            alert(err.message);
        }  
    });
    
   function showCursor(pair){
           var elink = pair.element;
               elink.style.cursor = "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) {
    linkToElemPairs.forEach(showCursor);
}

  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){
				console.log(link);
				if(link.type == 'ReportExecution'){
					v("#container2").report({
						resource: link.parameters._report,
						params: {
							monthNumber: [link.parameters.monthNumber]
						}, 
					});     
				}
				else if(link.type == 'Reference'){
					window.open(link.href);
				}
			}
	}

  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 –

Hyperlink-Reference-report

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

Hyperlink-ReportExecution

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 –

Hyperlink-ReportExecution-report

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

Hyperlink-Reference

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 : http://jsfiddle.net/b42ax3m2/63/

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

References:
Visualize JS API reference

– Shraddha
Helical IT Solutions

Using Multi-select parameter in Jasper


Using Multi-select parameter in Jasper

Parameters can be the filter conditions which can be applied in the data-visualizations. This bog gives steps wise description of creating a multi-select parameter while developing report.

There are 2 different types of Parameters
1. Single select
2. Multi-Select

creating Parameter in Jasper Studio:

For Example there are employees in a department and a report generates the employee details department-wise. If the user wants to view employees from more than 1 department user can create multi-select parameter in that case.

Following are steps to create a parameter

step1: In the outline to the left side of the jasper studio we can find parameters. Right-clcik and create a new parameter.for eg: lets create a parameter with name “dept” and in the class definition we must give “java.util.Collection”, we can also give default values it is optional.

Capture1

step2: In case we are using sub-report or the table component we need to pass the same paraeter with same name to the sub-report/table. In the above example we are using table. Click on table in the table component and in the properties pane select dataset table u will find the following

Capture2

step3: Then add the parameter from main report to the table/subreport.
and click on finish.
Capture3

step4: Using the parameter in the query. following is the syntax for using multi-select parameter in a query.

where $X{IN,d.department_id,dept}
Capture4

Thanks
Asha Bidiyasar

Create A Custom Table Report using Helical Insight (Dynamically Picking the Columns Names and Data)

Create A Custom Table Report using HI (Dynamically Picking the Columns Names and Data)

If you have already had a Hands-On experience on the Helical Insight Tool [HI tool] then this blog would be helpful

For a creating a report there are 4 files required
1. EFW
2. HTML
3. EFWD
4. EFWVF

the Report Layout lies on the HTML page, the SQL queries lies within the EFWD file and the visualization lies in the EFWVF File.
Hence once the Query is fired it comes to the visualization file to create a table as that’s our goal. With the help of the following code below it can be created with ease.

The below code here is a template of our EFWVF looks like
<Charts>
<Chart id=”1″>
<prop>
<name>Table</name>
<type>Custom</type>
<DataSource>1</DataSource>
<script>

//Your Visualization Code Goes Here

</script>
</prop>
</Chart>
</Charts>

Chart ID here is 1 which is unique
Type is of custom
DataSource 1 is the Unique ID defined in the EFWVF File. ie (<DataMap id=”1″ )
Now within the <script> </script>
we Paste the following:

<![CDATA[
//The If Block Does Return A Message NO Data when there is No Data
if(data.length == 0)
{
$(‘#chart_1′).html(“<div ><h4 style=’text-align:CENTER;color:black; padding-top:60px;’>No Data Available For Current Selection</h4></div>”);
return;
}
//The Else Block Returns The Table if there is a Table
else
{
//Here the funtion Tabluate Returns the Data in Tabular Form
function tabulate(elem, data, columns)
//Function Start
{
var table = d3.select(elem).append(“table”)
.attr(“class”,” table display compact width:100%;cellspacing:1 “)
.attr(“id”,”table”)
thead = table.append(“thead”),
tbody = table.append(“tbody”);

//Append the header row
thead.append(“tr”)
.selectAll(“th”)
.data(columns)
.enter()
.append(“th”)
.text(function(column) { return column; })
.attr(‘class’, function(d, i){ return “colH_” + i; })
.style(‘background-color’,’#ededed’)
.style(‘color’,’black’)
.style(‘@media print’,’display:none’)
.style(‘padding-left’,’25px’);

// create a row for each object in the data
var rows = tbody.selectAll(“tr”)
.data(data)
.enter()
.append(“tr”);

// create a cell in each row for each column
var cells = rows.selectAll(“td”)
.data(function(row) {
return columns.map(function(column) {
return {column: column, value: row[column]};
});
})
.enter()
.append(“td”)
.text(function(d) { return d.value; })
.attr(‘class’, function(d, i){ return “col_” + i; })
.attr(‘align’, ‘left’)
return table;
//Function END
}

//Render the table
//Object.keys(data[0]) is the Data to fetch the Column Header
//data has the data of the Table
console.log(Object.keys(data[0]));
var subjectTable = tabulate( ‘#chart_1’, data, Object.keys(data[0]));
}
]]>
Save it in the same Directory with rest of the file

Run your report on HI and you get a table.

Keep in mind you can change the query and still get the new columns from the new query.

Thanks
Sohail Izebhijie

Beginner’s Guide to E.T.L (Extract, Transform and Load) – Connection Set-Up

Connection Setup for connection type: PostgreSQL

[We are now setting up a connection to A database i.e if your source is a database]
There are 3 ways of access provided for Connections using PostgreSQL
a. Native (JBDC)
b. ODBC
c. JNDI

a. Native (JBDC)
I. Enter the Connection Name: Anyname
II. Select the connection type: PostgreSQL
III. Host Name: localhost [This can be an ip address]
Database Name: LearnETL[Name of the database you are using]
Port Number: 5432 or your required Port Number
User Name: Your database user name
Password: Your database password
IV. Test Connections and OK.

b. JNDI
Here we need to go to the data-integration folder and open up the sub-folder “simple-jndi” and edit jdbc.properties

Here we need to write the following code:

ETL_Connection/type=javax.sql.DataSource
ETL_Connection/driver=org.postgresql.Driver
ETL_Connection/url=jdbc:postgresql://localhost:5432/LearnETL
ETL_Connection/user=postgres
ETL_Connection/password=postgres

ETL_Connection: name of the connection
localhost:5432/LearnETL: localhost is the host name, 5432 is the port number and LearnETL is the Database name.
user: username
password: Password

Save and back to the Database connection
Restart your PDI.

and in the Setup, select JNDI and for
JNDI Name : name of your connection [ETL_Connection]

c. ODBC

This is not commonly used but what will be needed

are as follows:

1.Install the PostgreSQL ODBC driver which can be downloaded.
2.Select the PostgreSQL ODBC Unicode and then
3. Setup
enter Data source Source, Name, Server, Username, Password and Port. Test and Save if Connection is OK.!
4. a bundle of JAR files to be copied in your Java folder as this ODBC bundle files has been discontinued in JAVA 8 Bridge

Thank You
Sohail Izebhijie

Beginner’s Guide to E.T.L (Extract, Transform and Load) – A Basic Process

Loading Data from Source to Target

Before we proceed it’s important to identify the tool you might need to accomplish the process of ETL, in my case i would be using the Pentaho Data Integration Application (keep in mind irrespective of the tool you use the steps or procedures are similar but the approach might be differ).

The following Steps can be followed:

1. Identify your Source, it could be the following:
a. C.S.V file
b. Text file
c. A database
d. and so on

in my scenario a C.S.V (comma separated file)file.

2. Open Up your Spoon.bat
Select a new transformation and select a “Input” then select what type of input you require
we have Text File Input, C.S.V file input, SAS Input, Table Input and so on. In My case since i’ll be using C.S.V file as a Source i’ll select C.S.V file Input Component.

3. Set Up your Connection Based on your preferred connection type in my case i’ll be using the postgreSQL.

[Read my next Blog on setting Up a Connection using Connection type: PostgreSQL]

4. Once Connection has been Established you can Right-Click on the Connection and Select Share if that’s a common Connection to all your transformations will be using this will share the Transformation Connection to other transformation.

5. So we will be Sending data from Source to a Target to we need to to have a “Input” as the source
and an “Output” as the Target.

6. Input

download a C.S.V file from the internet

or even Create a TXT/C.S.V input file
as shown below.

Create a source if required
Text_Source (comma delimited)

Employee_Number,First_Name, Last_Name, Middle_Initial, Age, Gender, Title
101,Ehizogie,Izebhijie,Sohail,24,Male,Developer
102,Fahad,Anjum,WithClause,23,Male,Developer
103,Gayatri,Sharma,A,24,Female,Accountant

Save as txt or csv and this can be your input.

Here since our input is from a csv file
we open Up or C.S.V File Input component
Step Name: Anyname
File Name: Browse the selected path
Delimiter: , (comma)
Enclosure: ”

and then Select Get Fields and Select OK
Preview your Data

7. Output
Open Up Table output component
Select Target Schema
Select The Target Table

[Keeping in mind a Table exist in the DB]

Select OK!

Right-Click on the table output to MAP the columns from Source to Target

Now this is important as the to get the right data from the source to the column in the target
and Then Run

As a Beginner keep in mind that
Errors are a bound to occur
Such as Type of data from
Source Does not Match your Target Table format.
and so on.

Here a we can Have some little transformation step to convert and take care of such format errors
[In my next blog we can look into handling that]

Now Go to your Target Database and Run the SELECT * FROM table_name

there you go!

Thanks
Sohail Izebhijie

How to avoid duplication of values in the columns in PDI if column names are same?

How to avoid duplication of values in the columns in PDI if column names are same?

There can be requirements where we want same column names in the transformation (PDI). In such case the value of field is overwritten on another field.

For ex: If for a company we want 3 pairs of Loan Id and Loan Amounts , if we give the same name for the columns as per requirement then there will be duplication of data. If we don’t handle this scenario properly this can happen.

Suppose: We have 3 Loan Amounts.

Loan Amount 1: 10$

Loan Amount 2: 20$

Loan Amount 3: 30$

The column names are same i.e Loan Amount, Loan Amount, Loan Amount.If we directly pass the names in the text output then there will be replication , all three amounts will be coming 10$.

In this case we cannot using Select Values step (Renaming) will not solve the replication issue.

To get rid of such situation we can create a separate header transformation and check append in text file output  in the next transformation.

In this case we can keep the name of column like “Loan Amount” for n no of times in the header and for the differentiation purpose in the next step we can keep Loan Amount 1, LoanAmount 2, Loan Amount 3.

Note: For checking append in the text file output follow these steps:

  • Double click on text file output
  • Go to content
  • Check append and uncheck header

Thanks

Charts in BIRT – A Simple Chart Example

Charts in BIRT – A Simple Chart Example

In this tutorial, we will create a BIRT report with a simple Bar chart showing the monthly sales in 3 different states of the US. We will be using the same food mart database in MySQL for a datasource. So, we are going to have 3 fields, the Month (on category axis), the States (Series) and the Total Sales (on the Value axis).

(Note: As in the previous tutorials, we are using the Eclipse based BIRT report designer to develop this report)

Let’s begin –

Define the Data Set

As we have seen in previous tutorials, first step would be to create a data source and then define a data set which will pull the data for my chart. Below is the data set we need –

DS_BIRT_Chart

There is a particular reason why I chose to select “first date of the month” as a column instead of the month column, though the grouping is by month column. This is to ensure that my category axis could be a date/time axis and would plot my months in the correct order.

As we are not using any parameters for our report, we have nothing more to specify.

Adding the Chart component

Drag and drop the “Chart” component from the palette onto the report. The Chart Editor will automatically pop up.

There are 3 tabs, the first one is to select the “Chart Type”. In this case we select the Bar Chart, the first one which a Grouped Bar Chart.

Chart-Edit-1

Then, we click on Next to go to the next tab, to select the data for the chart. This involves selecting the source of the data and then binding the columns to the chart.

We select the data set that we defined

Chart-Edit-2(1)

And then we bind the columns to the chart i.e. we select what columns to bind with the category, value and series axes.

Chart-Edit-2

As seen above, we selected the “the_date” to be used as category, “store_state” to be used as a series and “sales” as a value.

In the Next tab, we can format the chart, the different aspects of it, the axis, the title, legend, tooltips, colors, font, back ground and so on.

We will first click on “X-Axis” in the Left hand side of the panel, to change our Category Axis Type.

Chart-Edit-3(1)

Firstly, change the Type to “DateTime” and then click on the “Format” button right next to it. Set the format to Month (MMM) using “Pattern” option under “Advanced”. Though “MMM” is not listed in the drop down menu, it is a valid format.

Then we will go and add a Title to the chart

Chart-Edit-3(2)

There is a lot more formatting that we can do, but for now, we will go ahead and click on “Finish”.

Save the report and Preview.

BIRTChartReport

There you go, we have our Monthly sales chart ready.

More on the charts to follow in upcoming posts, so stay tuned!

Shraddha Tambe | Helical IT Solutions

Parameterized BIRT Reports – Multi-Select Parameter

Parameterized BIRT Reports – Multi-Select Parameter

This blog will help you add a multi-select parameter in BIRT report and filter the data. In the last blog, we parameterized a “sales report” by adding a single select parameter “store city” to it. (You may refer it here). We will now convert this same parameter to a multi select, allowing you to select more than one cities, instead of just one.

Below images, show the single select parameter and the report.

Parameter_Prompt

Report_After

To reiterate our steps for adding parameter,

1. We first created a dataset for the parameter to get its values from.

2. Then we defined a Report Parameter.

3. We updated the report Query to use this parameter. We added the clause “where s.store_city = ?” in the query and then created a query parameter , linked to the report parameter.

HOW TO make the parameter a Multi-select ?

There are just 2 things we need to change.

1. Configure Report Parameter as Multi-select:

This is easy. All we need to do is to edit the parameter and check the “Allow Multiple Values” option in its properties. You can see that the default value option also now allows setting multiple values

Multiselect parameter - Edit

 

2. Updating the query to use a multi-select parameter:
This is not so straight forward.
We change the query to use the “in” clause – s.store_city in (?)

 

Query_Multiselect-Issue

 

But if you go to the query parameter definition, it does not work with the multi-select parameter, you see this message. By the default BIRT behavior, the query parameter only uses the first value among the select values. This will not give us the results we expect.
 
query-parameter_Multiselect-Issue
 

Below is the work around for our problem:

Change the query to look something like
 
Query_Multiselect
 

Now, we are going to write a small script to replace the string ‘999’ in the query string with a list of values selected from the multiselect report parameter.

Click on the Report dataset and Go to the “Script” tab on the main window. Select “beforeOpen” in the dropdown. This is because; we got to do the substitution before the dataset is opened.
 
DataSet_BeforeOpen
 
It will basically take the array of parameter values for “store_city”, say [Beverly Hills, Los Angeles, Merida] and give you – Beverly Hills’,’Los Angeles’,’Merida

This string will replace the string ‘999’ and you get this clause in your query –

s.store_city in (‘Beverly Hills’,’Los Angeles’,’Merida’)

Save the report now and we are done!!

Run the report in web viewer and we get a parameters window with the multi-select input for “Store City”

 
multiselect parameter

 
Report_WithMultiselect
 

Shraddha Tambe | Helical IT Solutions

You may also like to read –

Introduction to BIRT (Business Intelligence and Reporting Tools)

Creating a Simple BIRT Report

DATA VISUALISATION USING D3 – JAVASCRIPT CHARTING LIBRARY

D3 stands for Data-Driven Documents. D3.js is a JavaScript library for manipulating documents based on data. D3 helps you bring data to life using HTML, SVG and CSS. D3’s emphasis on web standards gives you the full capabilities of modern browsers. D3.js is written in JavaScript and uses a functional style which means you can reuse code and add specific functions.

Below is demo of how to create Bar chart in D3.

To set a D3 to your webpage you need only one file:

  • D3 JavaScript file

You can choose solution to link to external services which host these file for you.

<!-- These belongs to the HTML file where you want D3 to work - put these lines into your <head> tag -->
<head>
<script type="text/javascript"src="//cdnjs.cloudflare.com/ajax/libs/d3/3.4.11/d3.v2.min.js"></script>
</head>

We need to include style and javascript code inside HTML file itself.

<style>
body {
  font: 10px sans-serif;
}

.axis path,
.axis line {
  fill: none;
  stroke: #000;
  shape-rendering: crispEdges;
}

.bar {
  fill: orange; 
}
.x.axis path {
  display: none;
}
</style>

We have to take data as input file in .json or .csv or .tsv format.

One of the key feature of D3 charts is SVG (Scalable Vector Graphics). Scalable Vector Graphics (SVG) is a family of specifications for creating two-dimensional vector graphics. 

SVG comes with a basic set of shape elements:

  • Rectangle
  • Circle
  • Ellipse
  • Straight Line
  • Polyline
  • Polygon
<!---  Below is example of creating Rectangle in SVG -->
<svg width="50" height="50">
 <rect x="0" y="0" width="50" height="50" fill="green" />
</svg>
<!-- This is how we add SVG element -->
d3.select("body")
.append("svg")
.attr("width", 50)
.attr("height", 50)
.append("rectangle")
.attr("x", 0)
.attr("y", 0) .style("fill", "purple");
<!-- This is how we bound to DOM element -->
var theData = [ 1, 2, 3 ]
var p = d3.select("body").selectAll("p")
.data(theData)
.enter()
.append("p")
.text("hello ");

Inside script tag we will append SVG and javascript code.

<script>

var margin = {top: 40, right: 20, bottom: 30, left: 40},
    width = 960 - margin.left - margin.right,
    height = 500 - margin.top - margin.bottom;

var formatPercent = d3.format(".0%");

var x = d3.scale.ordinal()
    .rangeRoundBands([0, width], .1);

var y = d3.scale.linear()
    .range([height, 0]);

var xAxis = d3.svg.axis()
    .scale(x)
    .orient("bottom");

var yAxis = d3.svg.axis()
    .scale(y)
    .orient("left")
    .tickFormat(formatPercent);

var svg = d3.select("body").append("svg")
    .attr("width", width + margin.left + margin.right)
    .attr("height", height + margin.top + margin.bottom)
    .append("g")
    .attr("transform", "translate(" + margin.left + "," + margin.top + ")");


d3.tsv("data.tsv", type, function(error, data) {
  x.domain(data.map(function(d) { return d.letter; }));
  y.domain([0, d3.max(data, function(d) { return d.frequency; })]);

  svg.append("g")
      .attr("class", "x axis")
      .attr("transform", "translate(0," + height + ")")
      .call(xAxis);

  svg.append("g")
      .attr("class", "y axis")
      .call(yAxis)
      .append("text")
      .attr("transform", "rotate(-90)")
      .attr("y", 6)
      .attr("dy", ".71em")
      .style("text-anchor", "end")
      .text("Frequency");

  svg.selectAll(".bar")
      .data(data)
      .enter().append("rect")
      .attr("class", "bar")
      .attr("x", function(d) { return x(d.letter); })
      .attr("width", x.rangeBand())
      .attr("y", function(d) { return y(d.frequency); })
      .attr("height", function(d) { return height - y(d.frequency); })
});

function type(d) {
  d.frequency = +d.frequency;
  return d;
}

</script>

At the end this is how Bar chart will look like.

bar

Thanks,

Krupal Barot