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

Pivot Table in Jasperstudio

Pivot Table in Jasperstudio

Often there comes the requirement  to create pivot table while creating reports in  jasper. No doubt one can always use crosstab component. But sometimes crosstab doesn’t give data in required form as grouping is done on column and row basis and measure’s are always present in the end. One such limitation of crosstab I faced when I received the requirement to create report having row groups with calculation based fields(measure) added not in the end of all columns. There I solved the issue by using table component and creating row groups. In this blog I’ll share my approach to solve the issue discussed before by using table component in jasper.

Here are the steps to create pivot table:

  • Create the query for the report. Here I am using Foodmart database and the query I used is as follows:
    select brand_name, gross_weight,pc.product_family, pc.product_department, product_name,product_category
    From product
    join product_class pc on product.product_id= pc.product_class_id
    where gross_weight between 6 and 8.5
    group by 1,2,3
  • Create dataset using the above query. Using this dataset create the table. The table needs to have header and the detail rows.
  • Create group header in the table component and create all the columns. For calculation field, drag the field value to the desired column and the variable will be created having the field as the “variable expression”. Select the calculation on variable which can be sum, count, average etc.
    Screen like follows is going to appear:-

blog1

Make the evaluation time in variable properties as ‘Group 1’. Similarly you can create different variable which functions as measure at different places in the table. Please note that these variables work similar like the variables in crosstab which are generated internally in jasper.blog2

blog3

Thus, you can create your own customized pivot table as per the requirement. Here, I am sharing the final output and the jrxml of the report. Hope It helps!!

File: pivot_table.jrxml

Thanks,

Nisha Sahu
BI Developer

 

 

Converting TimestampRange to Timestamp with default value expression and passing it into the Dataset .

 

Here when we are using our parameter class as net.sf.jasperreports.types.date.TimestampRange

with default value expression like

new java.sql.Timestamp((new Date()).getYear(), (new Date()).getMonth(),1,0,0,0,0)

then it will return (2016-01-01 00:00:00) format / Year /Month / Week/ Day.

new java.sql.Timestamp((new java.sql.Timestamp((new Date()).getYear(), (new Date()).getMonth(),(new Date()).getDate(),0,0,0,0)).getTime() + 86399000)

then it will return(2016-01-01 23:59:59) format / Year /Month / Week/ Day.

In this casse the default value will be in timestamp format so while extracting date from date range parameter

$P{P_StartDate}.getStart()

$P{P_EndDate}.getEnd()

we will get error.

So in this case we can get this timestamp format date in the variable of ireport or jasperstudio

we can create a variable and pass it to the SubDataset parameter by maping the variable of main report to subdataset or subreport and there we can use this timestamp value and pass it to our Query.

variable

Sql Query passing in jasper

column between $P{P_StartDate} and $P{P_EndDate}

If there are no default date ranges then we can pass a

$X{[BETWEEN], column, startParam, endParam}

If you want to set a relative date as a default value expression of a JasperReports parameter, use the following relative date-builder pattern:

  • new DateRangeBuilder(“DAY-1”).toDateRange()
  • new DateRangeBuilder(“WEEK”).set(Timestamp.class).toDateRange()
  • new DateRangeBuilder(“2016-01-01”).toDateRange()
  • new DateRangeBuilder(“2016-01-01 12:34:56”).toDateRange();

 

Thanks & Regards,

  Prasanth Kumar

Return Values from Sub Dataset to Main Dataset in Jasper Reports

Return Values from Sub Dataset to Main Dataset in Jasper Reports

Sometime it is required to return values from sub dataset to main report dataset but due to bug in the jasper studio we are unable to it. Please refer this bug link
http://community.jaspersoft.com/jaspersoft-studio/issues/4746

Steps to return values from sub dataset to main report dataset

1) Create two variable one in subreport dataset (lets say SubDataset_Var) and one in main report dataset (lets say MainDataset_Var).

2) Suppose this sub dataset is used by table component. Click on table component and goto properties window (dataset tab) it will show you below screen

returnvalue

3) Click on return values button it will open one pop up window , see the below image

return_values

In above image, you can see instead of MainDataset_Var it is showing SubDataset_Var in “To Variable”. So now in “From Variable” just put the MainDataset_Var name. then click ok.

It will add follow line in JRXML file

<returnValue fromVariable="MainDataset_Var" toVariable="SubDataset_Var" incrementerFactoryClass=""/>

4) Open source of JRXML and replace the fromVariable value to toVariable value and vice versa and remove the incrementerFactoryClass=””.

<returnValue fromVariable="SubDataset_Var" toVariable="MainDataset_Var"> 

Now you can use MainDataset_Var in your main report.

Note : Change the Evoluation Time of component in which you are going to use main dataset variable.

The Print When Expression

The Print When Expression

The print when expression in Jasper Studio or Jasper iReport is very useful in so many requirements and it is defined as the name itself implies
i.e Print /Show a String, Column and so on Based on a condition passed.
Now here are some requirements on how/when we can implement The Print when expression.

Requiremnt: Show a particular column when a parameter is selected
Solution:
Here we made a parameter Called ShowLocation with values either ‘Y’ or ‘N’/blank space
now when ‘Y’ is selected then a Location column should show and when ‘N/blank space then it doesn’t show.

2 ways i did approach this:

1. I made a 2 Table Components one with the Location Column added and the other without the Location Column added.
one on top the other.
so when my parameter calls a ‘Y’ value it picks the Table with the Location Column and vice versa.

This approach is good but can be time and performance consuming

the 2nd approach was:

2. Only one Table Component was used, Opened the Table Component and selected the Column i want to restrict/show based on the parameter passed, in my scenario or requirement it is the Location column as
shown in the image below:

TableLayout

now i would select the cell button

Cellin the properties Tab and then Select the
Print When Expression.

Now here in the print when expression box i would write :
PrintExperssion
$P{showlocation}.equals(“Y”) as shown above

 

and you are good to go for a preview.
Now Let’s have to put a N or blank space in the parameter showlocation we get the following output
before
and now let’s pass ‘Y’ in the showlocation parameter

before-Y
So Now with this condition it will show the column when the parameter is checked in with a ‘Y’ Value.
and voila the output:

after

Thanks
Sohail Izebhijie.

How to Replace Various Unwanted Characters during Reporting Using Jasper Studio

How to Replace Various Unwanted Characters during Reporting Using Jasper Studio

Reporting in Business Intelligence is the representation of Data in a more meaningful manner
so at times we need to follow some few formats to make our data to the End-User or Clients more meaningful to their Business

Now in some cases
characters like

commas (,), asterisk (*) and periods (.) are not permitted
and permitted characters can be Ampersand (&) , hyphens (-)

so what do we do here?

since jasper has various text functions one in particular that can be used here will be

the replaceAll() method

so this can be done on the particular columns you want make the required changes.

Now Let’s go to the field column you want and edit the Expression.

In my case:
I would be editing the First Name Column to the required format
so now we use the replaceAll() method

My requirement is to remove all commas and replace it with a [space]
so a simple

$F{firstname}.replaceAll(“,”,” “).trim()

can be used
Note: if you are wondering and new to Jasper why i used trim(), I used a trim() here because at times a name can be in the form

Helical,IT Solutions,

Now with the $F{firstname}.replaceAll(“,”,” “) it gives us the result Helical IT Solutions [space] this hereby create an extra space at the End so
a trim() method is used to remove unwanted spaces at the End.

OK now you have an idea on how to replace unwanted characters with the required characters
Now go ahead and replace * with –

and . with [space]

Did you get and Error?

Indeed yes you will get an Error if you
wrote your expression as

 $F{firstname}.replaceAll(“,”,” “).replaceAll(“*”,” “).replaceAll(“.”,” “).trim()

and if you didn’t well skip this part 😉

ok now if “.” is kept it actually treats it as a . and not as “.” the character same goes with the “*”

so the appropiate expression would be

$F{firstname}.replaceAll(“,”,” “).replaceAll(“[*]”,” “).replaceAll(“[.]”,” “).trim()

 

well there are various way also to express the . as character and [.] is one of them
and now you are good to go

– Izebhijie Sohail Ehizogie

Note: Quotations are in the form of quotation open and quotation close

Steps to migrate oracle with pentaho

Step 1:-

Run script as DB admin.

Script is available in location:- biserver-ce\data\oracle10g.

Modify configuration file:-

  1. applicationContext-spring-security-hibernate.properties.

Location:-

pentaho-solutions\system\applicationContext-spring-security-hibernate.properties.

original code:-

jdbc.driver=org.hsqldb.jdbcDriver

jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.HSQLDialect

Modified code:-

dbc.driver=oracle.jdbc.OracleDriver

jdbc.url=jdbc:oracle:thin:@localhost:1521/sysdba

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

  1. hibernate-settings.xml

Location:- pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code

<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>

Modified code:-

<config-file>system/hibernate/oracle10g.hibernate.cfg.xml</config-file>

oracle10g.hibernate.cfg.xml:-

Location:-

pentaho-solutions\system\hibernate\ oracle10g.hibernate.cfg.xml

Do not need to change any code in this file.. Just need to check everything is perfect or not

 

   <!– Oracle 10g Configuration –>

<property name=”connection.driver_class”>oracle.jdbc.driver.OracleDriver</property>

<property name=”connection.url”>jdbc:oracle:thin:@localhost:1521/sysdba

</property>

<property name=”dialect”>org.hibernate.dialect.Oracle10gDialect</property>

<property name=”connection.username”>hibuser</property>

<property name=”connection.password”>password</property>

<property name=”connection.pool_size”>10</property>

<property name=”show_sql”>false</property>

<property name=”hibernate.jdbc.use_streams_for_binary”>true</property>

<!– replaces DefinitionVersionManager –>

<property name=”hibernate.hbm2ddl.auto”>update</property>

<!– load resource from classpath –>

<mapping resource=”hibernate/oracle10g.hbm.xml” />

 

  1. quartz.properties:-

Location:-

pentaho-solutions\system\quartz\quartz.properties

Original Code

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Modified Code:-

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.oracle.OracleDelegate

4.context.xml:-

Location:-

tomcat\webapps\pentaho\META-INF\context.xml

Original Code

<Resource name=”jdbc/Hibernate” auth=”Container” type=”javax.sql.DataSource”

factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″

maxWait=”10000″ username=”hibuser” password=”password”

driverClassName=”org.hsqldb.jdbcDriver” url=”jdbc:hsqldb:hsql://localhost/hibernate

validationQuery=”select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES” />

 

<Resource name=”jdbc/Quartz” auth=”Container” type=”javax.sql.DataSource”

factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″

maxWait=”10000″ username=”pentaho_user” password=”password”

driverClassName=”org.hsqldb.jdbcDriver” url=”jdbc:hsqldb:hsql://localhost/quartz

validationQuery=”select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES”/>

 

Modified Code:-

<Resource validationQuery=”select 1 from dual”

url=” jdbc:oracle:thin:@localhost:1521/sysdba

driverClassName=”oracle.jdbc.OracleDriver” password=”password”

username=”hibuser” maxWait=”10000″ maxIdle=”5″ maxActive=”20″

factory=”org.apache.commons.dbcp.BasicDataSourceFactory”

type=”javax.sql.DataSource” auth=”Container” name=”jdbc/Hibernate”/>

                       

<Resource validationQuery=”select 1 from dual”

url=” jdbc:oracle:thin:@localhost:1521/sysdba

driverClassName=”oracle.jdbc.OracleDriver” password=”password”

username=”quartz” maxWait=”10000″ maxIdle=”5″ maxActive=”20″

factory=”org.apache.commons.dbcp.BasicDataSourceFactory”

type=”javax.sql.DataSource” auth=”Container” name=”jdbc/Quartz”/>

6. repository.xml

Location of the file: pentaho-solutions\system\jackrabbit\repository.xml.

Comment this code means (<! – – every thing here – -> )

Active means: Remove comment

i)                    FileSystem part

Comment this code

<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>

     <param name=”path” value=”${rep.home}/repository”/>

</FileSystem>

Active this code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.db.OracleFileSystem”>

   <param name=”url” value=”jdbc:oracle:thin:@localhost:1521“/>

   <param name=”user” value=”jcr_user”/>

   <param name=”password” value=”password”/>

   <param name=”schemaObjectPrefix” value=”fs_repos_”/>

   <param name=”tablespace” value=”pentaho_tablespace”/>

</FileSystem>

ii)                  DataStore part

Comment this code

<DataStore class=”org.apache.jackrabbit.core.data.FileDataStore”/>

Active this code:-

<DataStore class=”org.apache.jackrabbit.core.data.db.DbDataStore”>

<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>

<param name=”driver” value=”oracle.jdbc.OracleDriver”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”databaseType” value=”oracle”/>

<param name=”minRecordLength” value=”1024″/>

<param name=”maxConnections” value=”3″/>

<param name=”copyWhenReading” value=”true”/>

<param name=”tablePrefix” value=””/>

<param name=”schemaObjectPrefix” value=”ds_repos_”/>

</DataStore>

iii)                Security part in the FileSystem Workspace part

Comment this code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>

<param name=”path” value=”${wsp.home}”/>

</FileSystem>

Active this code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.db.OracleFileSystem”>

<param name=”url” value=”jdbc:oracle:[email protected]:1521/sysdba”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”schemaObjectPrefix” value=”fs_ws_”/>

<param name=”tablespace” value=”pentaho_tablespace”/>

</FileSystem>

iv)       PersistenceManager part

Comment this code:-

<PersistenceManager class=”org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager”>

<param name=”url” value=”jdbc:h2:${wsp.home}/db”/>

<param name=”schemaObjectPrefix” value=”${wsp.name}_”/>

</PersistenceManager>

Active This Code:-

<PersistenceManager class=”org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager”>

<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>

<param name=”driver” value=”oracle.jdbc.OracleDriver”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”schema” value=”oracle”/>

<param name=”schemaObjectPrefix” value=”${wsp.name}_pm_ws_”/>

<param name=”tablespace” value=”pentaho_tablespace”/>

</PersistenceManager>

v)       FileSystem Versioning part

Comment This Code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>

<param name=”path” value=”${rep.home}/version” />

</FileSystem>

Active This Code:-

<PersistenceManager class=”org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager”>

<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>

<param name=”driver” value=”oracle.jdbc.OracleDriver”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”schema” value=”oracle”/>

<param name=”schemaObjectPrefix” value=”pm_ver_”/>

<param name=”tablespace” value=”pentaho_tablespace”/>

</PersistenceManager>

 

 

Stopping HSQL db start up

In web.xml file

Comment or delete this code (Commenting is preferable)

<!– [BEGIN HSQLDB DATABASES] –>

<context-param>

<param-name>hsqldb-databases</param-name>

<param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>

</context-param>

<!– [END HSQLDB DATABASES] –>

 

Also comment this code

<!– [BEGIN HSQLDB STARTER] –>

<listener>

<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>

</listener>

<!– [END HSQLDB STARTER] –>

You have done with integrating pentaho 5.0.1 CE with Oracle

Now login to the Pentaho server .

URL:  http://localhost:8080/pentaho

Username/Password : Admin/password

D3 Sankey Chart Integration with Jaspersoft

In this blog we will be discussing about D3 Sankey Chart Integration with Jaspersoft using HTML method of integration.

All the reports are develop using ireport 5.5 professional and jasper server 5.5

As html component of jasper server does not load any scripts in the html component, we loaded the script in one of the decorator page(jsp page). The page is located at the location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\decorators\decorator.jsp

In the page we included the scripts which we want to load. We added the following code in the jsp page at line no 46:

<script type="text/javascript" language="JavaScript"
src="${pageContext.request.contextPath}/scripts/d3.v3.min.js"></script>

The script to be added should be kept at location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\scripts

Sankey Chart

Sankey D3 Jaspersoft

Sankey D3 Jaspersoft

For this chart we need to include one more js script file in the decorator page as described in the start of the document.
The js file is sankey.js and can be downloaded from
https://github.com/d3/d3-plugins/blob/master/sankey/sankey.js

Sankey diagrams visualize the magnitude of flow between nodes in a network. Sankey diagrams are a specific type of flow diagram, in which the width of the arrows is shown proportionally to the flow quantity. They are typically used to visualize energy or material or cost transfers between processes.
The diagram will contain two quantities, 1) All the nodes. 2) Which nodes are connected and with what value.

The json object should be like : http://bost.ocks.org/mike/sankey/energy.json

    Integration with JasperServer:

 
The data which we use for developing the calendar view can be fetched from any database. The data fetched from database is stored in a variable and is then accessed in the html component using the same variable. Applying this of process makes the report dynamic instead of static. Few parameters can also be added in the report which can be used in query and/or html component.
Generally for these type of charts we pass a variable which contains required data containing list of quantity1(employees or people or students, etc.) and a value associated with different type of skills. The string is created according to JSON format, so that when accessed in script tag, can be easily converted to JSON object using eval function.

Any variable/parameter can be accessed as shown below:
" var arr ="+$V{variable1}+" "

Parameter in query:
Select * from table_name
where date between $P{parameter1} and $P{parameter2}

The sample code of static chart is shown below at:

 

#chart {
height: 500px;
}

.node rect {
cursor: move;
fill-opacity: .9;
shape-rendering: crispEdges;
}

.node text {
pointer-events: none;
text-shadow: 0 1px 0 #fff;
}

.link {
fill: none;
stroke: #000;
stroke-opacity: .2;
}

.link:hover {
stroke-opacity: .5;
}

var margin = {top: 1, right: 1, bottom: 6, left: 1},
width = 960 – margin.left – margin.right,
height = 500 – margin.top – margin.bottom;

var formatNumber = d3.format(“,.0f”),
format = function(d) { return formatNumber(d) + ” TWh”; },
color = d3.scale.category20();

var svg = d3.select(“#chart”).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 + “)”);

var sankey = d3.sankey()
.nodeWidth(15)
.nodePadding(10)
.size([width, height]);

var path = sankey.link();
var energy = {“nodes”:[ {“name”:”Energy”}, {“name”:”Industrial Processes”}, {“name”:”Electricity and heat”}, {“name”:”Industry”}, {“name”:”Land Use Change”}, {“name”:”Agriculture”}, {“name”:”Waste”} ], “links”:[ {“source”:0,”target”:1,”value”:12}, {“source”:1,”target”:2,”value”:20}, {“source”:2,”target”:3,”value”:30}, {“source”:4,”target”:1,”value”:8}, {“source”:4,”target”:2,”value”:10}, {“source”:4,”target”:5,”value”:5}, {“source”:5,”target”:6,”value”:5} ]};

sankey
.nodes(energy.nodes)
.links(energy.links)
.layout(32);

var link = svg.append(“g”).selectAll(“.link”)
.data(energy.links)
.enter().append(“path”)
.attr(“class”, “link”)
.attr(“d”, path)
.style(“stroke-width”, function(d) { return Math.max(1, d.dy); })
.sort(function(a, b) { return b.dy – a.dy; });

link.append(“title”)
.text(function(d) { return d.source.name + ” → ” + d.target.name + “\n” + format(d.value); });

var node = svg.append(“g”).selectAll(“.node”)
.data(energy.nodes)
.enter().append(“g”)
.attr(“class”, “node”)
.attr(“transform”, function(d) { return “translate(” + d.x + “,” + d.y + “)”; })
.call(d3.behavior.drag()
.origin(function(d) { return d; })
.on(“dragstart”, function() { this.parentNode.appendChild(this); })
.on(“drag”, dragmove));

node.append(“rect”)
.attr(“height”, function(d) { return d.dy; })
.attr(“width”, sankey.nodeWidth())
.style(“fill”, function(d) { return d.color = color(d.name.replace(/ .*/, “”)); })
.style(“stroke”, function(d) { return d3.rgb(d.color).darker(2); })
.append(“title”)
.text(function(d) { return d.name + “\n” + format(d.value); });

node.append(“text”)
.attr(“x”, -6)
.attr(“y”, function(d) { return d.dy / 2; })
.attr(“dy”, “.35em”)
.attr(“text-anchor”, “end”)
.attr(“transform”, null)
.text(function(d) { return d.name; })
.filter(function(d) { return d.x < width / 2; })
.attr(“x”, 6 + sankey.nodeWidth())
.attr(“text-anchor”, “start”);

function dragmove(d) {
d3.select(this).attr(“transform”, “translate(” + d.x + “,” + (d.y = Math.max(0, Math.min(height – d.dy, d3.event.y))) + “)”);
sankey.relayout();
link.attr(“d”, path);
}

 

The steps on how to integrate it with jasperserver was discussed in my previous blog(D3 Integrating with Jasperserver).

Avi Dawra
Helical IT Solutions

Date Calculation using Joda Time Library In iReport

Dear readers, this blog talks about Date Calculation using Joda Time Library In iReport

By using Joda Time Library , we can give default value expression for any datetype parameter .

Steps for using joda time library in iReport  :

  1. Download joda time library ,

2. Put its jar file “joda-time.jar” in the path of iReport : Tools/Options/Classpath/.

joda ireport

3. In the main report section properties , set import property to “org.joda.time.DateTime

Without  double quotes.

joda ireport 2

4. Now with the above configuration , we will be able to do any type of date calculation

 

5. Example :

   a. Start Day Of Current Month :

Take two parameters : now and startday respectively

For ‘now’ parameter give its class name as ‘java.util.Date’ and default value expression as new DateTime.now().dayOfMonth().withMinimumValue().withTimeAtStartOfDay()

 

b. Last day of previous Month :

Take two parameters : now and endday respectively

For ‘now’ parameter give its class name as ‘java.util.Date’ and default value expression as new DateTime.now().minusMonths(1).dayOfMonth().withMaximumValue()

In the similar way we can do any type of date calculation for default value expression.

Rupam Bhardwaj

Helical IT Solutions

D3 Skill Charts integration with Jaspersoft

This blog talks about D3 Skill Charts integration with Jaspersoft

All the reports are develop using ireport 5.5 professional and jasper server 5.5

As html component of jasper server does not load any scripts in the html component, we loaded the script in one of the decorator page (jsp page). The page is located at the location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\decorators\decorator.jsp

In the page we included the scripts which we want to load. We added the following code in the jsp page at line no 46:

<script type=”text/javascript” language=”JavaScript” src=”${pageContext.request.contextPath}/scripts/d3.v3.min.js”></script>

The script to be added should be kept at location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\scripts

Meaning of Skill Chart:-

Skill chart represents the mapping of an entity to some other entity on a particular parameter. For example it can show mapping of employees to various technologies they know and the level of their understanding.

Integration with JasperServer:

The data which we use for developing the calendar view can be fetched from any database. The data fetched from database is stored in a variable and is then accessed in the html component using the same variable. Applying this of process makes the report dynamic instead of static. Few parameters can also be added in the report which can be used in query and/or html component.

SkillChart Jaspersoft Integration

Generally for these type of charts we pass a variable which contains required data containing list of quantity1(employees or people or students, etc.) and a value associated with different type of skills. The string is created according to JSON format, so that when accessed in script tag, can be easily converted to JSON object using eval function.

Integration with JasperServer:

Any variable/parameter can be accessed as shown below:

“<script> var arr =”+$V{variable1}+” </script>”

Parameter in query:

Select * from table_name

where date between $P{parameter1}  and $P{parameter2}

 

The sample code of static chart can be found at:

http://bl.ocks.org/derplau/8642419

The steps on how to integrate it with jasperserver was discussed in my previous blog(D3 Integrating with Jasperserver).

 

Avi Dawra

Helical IT Solutions