Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Wednesday, December 04, 2013

Tip - Find jasper server version using fire bug in Mozilla...

Generally we do customization on jasper server and disable the jasper server version for security reasons..
But we can track it using fire bug in Mozilla ..

Once you log into the jasper server , on the fire bug, on the fire bug search bar type "about" .. In the HTM code you can find the "div" tag of it.. on the right hand side, you can find the css code... set display property to "block" which will show you the "About JasperReports Server".

If you display:none then "About JapserReports Server" will be disabled. 




Hmmm :) Credit goes to Sharad, who is one of my colleagues :) :D :P

Friday, November 08, 2013

Dates in Jasper iReport - Expression for default values and counting the number of months b/w given two dates.

This post teach you how to play with default date parameters & calculations on dates in jasper iReport.

In this tutorial you learn how to make use of  "SimpleDateFormat" class and java.util.Calendar class.

I) Let us start with ReportStart(Start Date) and ReportEnd(Report End) parameters.

In some reports you need to give default parameters in your start date and end date.
Eg:
Start Date = Previous month 1st day
End Date= Previous month last day
Qn :
 How to write default expression for this ?
Ans:
NOTE: 
$P{cal}  is a parameter used to reduce the expression complexity.
$P{cal}=java.util.Calendar.getInstance()

Start Date: ( java.util.Date)
(
    $P{cal}.add(java.util.Calendar.MONTH, -1) ||
    $P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 1)
)
? null : $P{cal}.getTime()
 End Date(java.util.Date)
(
    $P{cal}.set(java.util.Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(java.util.Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()
Sample output(i.e., default values in the date place holders would be)
Star Date :  2013-10-01
End Date : 2013-10-31

II) To get the individuals of a date using SimpleDateFormat class.

From Report Start  Date($P{ReportStart})
StartYYYY
Integer.parseInt(new SimpleDateFormat("yyyy").format($P{ReportStart}))
StartMM
Integer.parseInt(new SimpleDateFormat("MM").format($P{ReportStart}))
StartDD
Integer.parseInt(new SimpleDateFormat("dd").format($P{ReportStart})) 

Similarly you can extract the parts of the date for ReportEnd parameter($P{ReportEnd} )


III) To get the current parts of the date from Calendar class in iReport

CurrentYear:
$P{cal}.get(Calendar.Year)
CurrentMonth:
$P{cal}.get(Calendar.MONTH)+1
CurrentDayOfMonth:
$P{cal}.get(Calendar.DAY_OF_MONTH)


IV) Calculating Number of Months b/w given two dates

 Calculationsif(EndYear==StartYear )
{
    (EndMonth-StartMonth+1)+(12*(YendYear-StartYear))
}
else
{
    if(EndYear>StartYear)
    {
        (12-StartMonth)+EndMonth+1+(12*(EndYear-StartYear-1))
    }
    else
    print "0"
}
NumberOfMonths parameter expresssion:
(
    $P{EndYYYY}==$P{StartYYYY}
)
?
(
    ($P{EndMM}-$P{StartMM}+1)+(12*($P{EndYYYY}-$P{StartYYYY}))
)
:
(
        (
            $P{EndYYYY}>$P{StartYYYY}
        )
        ?
        (
            (12-$P{StartMM})+$P{EndMM}+1+(12*($P{EndYYYY}-$P{StartYYYY}-1))
        )
        :
        0
)

V) Counting the number of Exceeded Months from current date when your "End Date " parameter is having the value of greater than current date

(
    $P{ReportEnd}.compareTo($P{cal}.getTime())>0
)
?
(
  (12-($P{cal}.get(Calendar.MONTH)+1))+$P{EndMM}+12*($P{EndYYYY}-$P{cal}.get(Calendar.YEAR)-1)
)
:
0



 Source Code of the report
Download the report using below link.
https://drive.google.com/file/d/0BymV_QP4TGBEVU5wV0xwZ3JfZG8/edit?usp=sharing
(Note that there will be no preview of the report after clicking the link, click on Download)

How to execute the report ?
You need to create two parameter in the "Input Controls" section of the report after uploading the report to jasper server.
Input Controls should be : ReportStart, ReportEnd

Sadakar
BI developer

Tuesday, October 22, 2013

Publishing OLAP Schema to Jasper Server - Step by Step procedure

Hi guys,
This post will teach you how to publish OLAP Schema( .xml file OR CUBE) to the Jasper Server.

Where to download Schema Workbench ?
You can develop Schema either using PSW(Pentaho Schema Workbench of Pentaho) or SW(Schema workbench of Jasper)
Both the tools are same with their version release difference.

Download the latest PSW in location mentioned below.
http://sourceforge.net/projects/mondrian/files/schema%20workbench/3.5.0-stable/psw-ce-3.5.0.zip/download

Download the same for Jasper using below link
http://sourceforge.net/projects/jasperserver/files/JasperServer/JasperServer%204.0.0/JaspersoftOLAP-Workbench-cp-4.0.0.zip/download

You need to work out at 3 places to see the output of the Schema that you created using PSW or SW tool. They are
1) Analysis Schema
2) Analysis Connection &
3) Analysis Views

I have used PSW-CE 3.5 and Jasper Server 4.5 CE for doing this.

1) Analysis Schema

Step 1:
Upload your .xml file as shown in below figure
RightClick on Analysis Views -> Add Resource -> File -> OLAP Schema

Step 2:
* Browse for the file(.xml file, nothing but your schema file) from your local computer
* Give the location where you want to save the schema file.

2) Analysis Connection
* Right click on Analysis connection -> give name and resource ID (Any name and ID)
* Give location to save the connection in your repository and click on Next button.

* As you are already uploaded your schema in Step 1 you need to to browse for the .xml file here again.
* Just you need to give the path of the schema file(.xml) file that you uploaded in step 1
* For example : /analysis/schemas/demo_schema.xml
* Click on Next, again click on Next
* Imp thing you need to Locate your data source.
* In this exmplae I'm locating already available data sources in the jasper server( I externally created and giving the path for that data source).
  For Instance : /datasources/demodatasource

That's it click on submit/OK button.


3) Analysis Views
* Here you can see the visualization of your schema.
* Right click on Analysis View- > Add resource -> OLAP View
* Give name for the view  then click on Next
* Give the connection path that you created in step 2 and click on Next
* Provide MDX query for your analysis and clickon Submit


You are done with uploading/publishing your Schema to Jasper server.

OUTPUT
You can find the output at "Analysis Views" .. there you will see the name of the schema you uploaded .. just click on that schema.. you are ready to analyze your schema.

 Sadakar
BI Developer









Thursday, October 10, 2013

Export report output to multiple sheets of excel- Page breaking in jasper iReport or Studio

Hello Community,

NOTE : Find the update section : Updated On  14th April 2015 : Example JRXML

You can break the page where ever you want and export to excel in jasper server. 
For this you need to give  the properties  shown in image to jasper iReport  report.

Right  click on report Name from Reporot Insepctor > Properties -> report properties

net.sf.jasperreports.export.xls.one.page.per.sheet true
net.sf.jasperreports.export.xls.sheet.names.1 Pivot Remit
net.sf.jasperreports.export.xls.sheet.names.3 Detail
net.sf.jasperreports.export.xls.sheet.names.2 Summary
net.sf.jasperreports.page.break.no.pagination apply

 






As shown in the above picture use the page breaks where ever you want to break the report to a page when exporting to excel.


Update 14th April 2015 : Example JRXML
Click Me to Download JRXML Example

Click Me to Download the Sample Excel Output File of the Above JRXML

Quick Screenshot :


NOTE : The above example JRXML runs on PostgreSQL foodmart database.

Server file changes : 

add below line of property in jasperreports.properties file
net.sf.jasperreports.export.xls.one.page.per.sheet=true

Location of the file :
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF\classes

Quick Steps :
1) Keep the elements in User Defined Groups (bands)
2) Take Data sets to be used by elements kept on User Defined groups (bands)
3) Place the break element either at the end of elements.
4) Set Ignore Pagination= true for the report (If you use sub reports do the same).
5) Add net.sf.jasperreports.export.xls.one.page.per.sheet=true property in jasperreports.properties file.
6) set below shown properties for the report.

7) Save the report publish it the server and export the output to Excel.
Sample output image : 

:-)
Cheers..!!!

Tuesday, October 08, 2013

start_date/from_date and end_date/to_date parameters - giving default values - iReport

Imp Reference for : daily, weekly, monthly, yearly
http://community.jaspersoft.com/wiki/dynamic-dates-reports

This post let's you know how to use date parameters in SQL query with between operator and using with multi select parameter.

Example Query:

SELECT
        column1, colum2
FROM
        XYZ_table
WHERE
      $X{[BETWEEN], date_field_name_from_table, start_date, end_date}

In this way you can use between operator for start_date and end_date parameters with multi select.
Note that multiselect generally selects the input controls at a time.


Default values for start_date and end_date:
Problem statement:
Find the problem statement in this post.
http://community.jaspersoft.com/questions/819583/default-values-date-parameters

The problem statement which I faced is :
If today is 2013-10-07 then startdate is : 2013-09-16 enddate is : 2013-09-31
if today is : 2013-10-23 then startdate is : 2013-10-01 enddate is: 2013-10-15
The same logic should applicable in january month as well(i.e, for instance if today is : 2013-01-13 then startdate: 2012-12-16 enddate:2012-12-31)
 

Apart from the solution that given in the community, also find below solution

start_date Default Value Expression:

Syntax followed : ternary expression:  condition?True:False  

(
    $P{cal}.get(java.util.Calendar.DAY_OF_MONTH)>15 ?
    $P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 1) :
    ($P{cal}.add(java.util.Calendar.MONTH, -1) ||
        $P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 16))
)

? null : $P{cal}.getTime()

end_date Default Value Expression:

(
    $P{cal}.get(java.util.Calendar.DAY_OF_MONTH) == 1 ?
    $P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 15):
    $P{cal}.set(java.util.Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(java.util.Calendar.DAY_OF_MONTH))
)

? null : $P{cal}.getTime()


In the above two start_date and end_date parameters you can find $P{cal} - which is a parameter created to get the calender dates (in simple words java calender instance)

You must create this($P{cal}) parameter and have to give default value expression before you use this parameter in start_date and end_date parameters.

Default Value Expression for $P{cal} parameters is : java.util.Calendar.getInstance()

NOTE:
for all the input controls Parameters classs is : java.util.Calendar

Sadakar 
(Learning never exhausts the mind)








Monday, September 30, 2013

localization of jasper reports OR Internationalization of jasper reports - Hello world example

Hi.. small work out but very use full..
Generally customers want to see data in their own language.

This example is developed using iReport 5.1.0 pro, Jasper server pro and foodmart db with postgresql.

Sources/References :
Example : Converting English to Spanish
1) From Jasper Community
 https://www.jaspersoft.com/jasperserver-and-ireport-internationalization#int_reports

2) From google translator
http://translate.google.co.in/#auto/pt/BRAZILIAN%20STATE%20X%20SIGN%20UPS

 
 3) Locale Codes 
 http://download1.parallels.com/Plesk/Plesk8.2/Doc/plesk-8.2-win-l10n-guide/39382.htm
4) Tutorial Point
http://www.tutorialspoint.com/jasper_reports/jasper_Internationalization.htm

Steps:
1) File->New ->Save report with your fav name to your fav directory.
2) Write a simple query and drag a field to title band( Not necessary to do but iReport requires some query and field otherwise it'll show empty report).
3)Take Text field(Not static text) and write the following.
$R{localization.text1}
NOTE: $R is the special syntax used for internationalization of text in iReport.
4) Right click on report Name -> Select properties -> Give Resource bundle Name
Ex: localizationdemo
This is resource bundle name

5) Publish your report to Jasper Server and in resources section add the bundle files.
 You need to add two files..
One file contains general English text(USA) another consists internationalized text
In this example second file having spanish(B'z you are converting English to spanish)

Right click on Resources -> Add -> Resource Bundle  id ,Name and browse for the files
You must add two files.
Example:
File 1 ID & Name : localizationdemo.properties  (You can give any of your name , extension is not mandatory). Write this text in file

localization.text1=hello world
File2 ID & Name : localizationdemo_es.properties and add the file from your local machine. Write this text in file
localization.text1=hola mundo


In jasper server Change locale to es-Spanish

See the preview of the report.
Sample output


 

Sadakar
BI developer


Thursday, September 26, 2013

Connect to jasper server from iReport - from repository navigator

Hi guys.. 
Basic but useful
1. Connecting to jasper server professional from iReport

*  Click on Add New server button in the Repository Navigator
* Give some ID ( It could be any name)
* Give JasperReports Server URL
   Eg: 1
   http://localhost:8080/jasperserver-pro/services/repository
Eg: 2
   http://sadakar.com//services/repository

NOTE: In the URL, sadakar.com is the jasper server address
* Give UserName : superuser and password as password


2.Connection to jasper server  from iReport

Do the same as shown in figure except the URL.
The URL should be different in connecting to community server
Eg:1
http://localhost:8080/jasperserver/services/repository
Eg: 2
   http://sadakar.com//services/repository
NOTE: sadakar.com is the jasper address address 

Sadakar
BI developer


Wednesday, September 25, 2013

Adding mysql jar file in AWS jasperserver instance

Some times we may not find in which location the jasperserver installed in AWS instance ..

If you need to add some jar file (let's say mysql connector jar file) to your jasper server, you need to go lib folder of  jasperserver manually from command prompt and copy the required jar file and then you need to restart the service.. This quite make your work difficult and you require more time.


To avoid all such time consuming things. .. here is a way in jasper server to add a jar file..

1) Go to your datasource folder->Edit
2) Edit Driver and add jar file to the server from your local machine(note you are uploading .jar file from your local machine to AWS jasperserver directly
3)Scroll down and Click on Test the connection.
If the connection is successful that mean you have uploaded jar file correctly. 


NOTE: after uploading .jar file you need not to restart AWS jasperserver instance



Sadakar.
BI developer

How to export project folder to AWS jasper server professional instance

Hi guys..
This is quite a basic post and will teach you importing & exporting folders
Suppose, you have developed your project in your local machine.
Your project folder structure is looks like below image

You want to upload/deploy the same project in AWS jasper server instance ..
Question:
How to export your project in AWS Jasper server instance ?
How to import your project from your local machine ?

How to upload database driver file to AWS jasper server and test the connection?

How to export your project in AWS Jasper server instance ?
In your local machine:
1) Right Click on the folder you want to export and then Click on Export option
2) After doing step 1, you can see a pop-window asking you to give the export folder name and location to save. By default it is export.zip . Just you need a click to Export

How to import your project in AWS Jasper server instance ?
In AWS jasper instance
Open the  AWS jasper server in browser...
( You need URL, username & password of the server) This will provide by your client

1) On the server menu bar go to Manage ->Server Settings
2) On the left side panel you can find import option.
3) Click on it .. Browser  the project folder(zip file) which you exported from your local machine in up question.
Find the image below to understand the scenario.


Now, where you can find your project imported in AWS Jasper server instance ..

This example is based upon JasperServer 5.2 version(earlier versions might have different folder structures).
You can find  imported project folder @

Left side panel : root->Organizations->folder that you imported(Eg: Projects in this example)


 That's it.. you have done with importing project from your local server, and exporting the same project in AWS Jasper server instance ..

NOTE:
In your project you have multiple folders, say 1,2,3,4,5,6 where 1 is the base folder and remaining are arranged in tree structured manner.

If you import and/or upload one folder then the entire folder structure will come with .zip file but you can see only the content of the folder that you imported/exported.


Sadakar
BI developer










Thursday, September 19, 2013

'All' in SQL query with Jasper iReprt and passing 'All' as parameter

Hi guys..

This post teach you the usage of   'All' in SQL query in Jasper iReport.


Example:
You have the following list of states in your SQL query and you are making it as "single select Query" parameter in your input controls.

You can send any of the states as input controls by selecting that state.. If you want to select 'All' states information at a time , then how come it is possible ????

So your actual query is something like this :
 
SELECT  DISTINCT state FROM example_table  ORDER BY state

States
X
Y
Z

Use Union to append the 'All' word in your output.. now, your query should looks like some thing like below.

SELECT  'All' AS state
UNION

SELECT  DISTINCT state FROM example_table  ORDER BY state


States
All
X
Y
Z

Now, how to pass these states and 'All' word as input parameter in your main report query... ????

Your main Query should be designed in such a way that it accepts individual states and 'All' states ..
Example report Query :

SELECT
    industry,
FROM example_table

WHERE  ($P{state}='All' OR state=$P{state})
GROUP BY
industry
ORDER BY

industry

NOTE:
1) state=$P{state} where state is the name of the column and it accepts the individual states as input.
2) P{state}='All' where 'All' represents the all states information.


So in this way you can use 'All' in your iReport queries.


Sadakar
BI developer

Wednesday, September 18, 2013

Dashboard to Dashboard drill down in jasper server with Map component example

Hi guys,
This post teach you how to drill down from one dashboard to another dashboard with parameters passing.
Dashboard to Dashboard drill down.
When you click on
"All States"  hyperlink on dashboard 1, you have to navigate to second dashboard which displays bar charts with All states.
When you click on particular state you have to navigate to second dashboard which displays that particular state bar chart ..

Dashboard 1:

 Dashboard 2

Dashboard 2:

In dashboard2 ( You can say it is in Report 2 as you are working with only single report on dashboard).
Lets say you have 1 parameter in 2nd dashboard . say parameter name as "state". Individually in 2nd dashboard when you select  a particular state, that state bars should appear on the chart. When you select "All" option from state parameter drop down, you need to list all the bars on chart.

Parameter : 
$P{state} 
Parameter Query:(
SELECT  'All' AS state
UNION
SELECT  DISTINCT state FROM  sample_table  

ORDER BY state
Report Query:
SELECT
    industry_type,
    count(company_full_name)
FROM sample_table
WHERE  ($P{state}='All' OR state=$P{state})
GROUP BY
industry_type
ORDER BY

industry_type
DESC LIMIT 5


Save your report.. create a dashboard(2nd dashboard) in jasper server..
NOTE: 
You should know how to create a parameter in repository and how to publish a report to the server.
Preview and OUTPUT
* See the image two for output.. It is selected with MN state.. displayed all the bars in MN state.
if you select "All" option the dashboard report should display all the bars in states ... also note that I have restricted to limit 5.

Dashboard1

In dashboard 1 you are displaying all the states of USA.. now you want some think like when you click on states it has to go to another dashboard(In this example Dashboard 1).. When you click on " All states" hyper link it'll navigate to the Dashboard 1 and automatically displays the bars of all states. 

Report Query:
SELECT  state,sum(revenue_of_state) AS revenue 
FROM sample_table 
GROUP BY 
state 
ORDER BY state

1) Drag and drop Map component from Palette section to " Title Band" (Summary band is preferrable)
2) Right click on Map component and select Click on " Edit Map Properties" 
3) Click on " select a map " button and then select USA(states) from Available Maps.
4) Now click on Map data then " Entites" 
5) Click on "Add" give id expression as " $F{state} value expression as "$F{revenue_of_state}".
6)  Now, the most important point here you are going to work out.
    * Click on Item Hyperlink
    * Select " Blank " as Item target
    * Select " Reference " as  Hyperlink type
    * Click on reference tab
    * Give the dashboard URL in Hyperlink Reference Expression.
Eg:
"flow.html?_flowId=dashboardRuntimeFlow&dashboardResource=%2FSadakar%2FDashboards%2FIndustry_Wise_Dashboard&hidden_state="+$F{state}

This expression leads you Dashboard2 when you click on states...


7) Now take a text filed and type "All states" give hyperlink to this text filed(Not static text field  simply Text field b'z static text field doesn't have hyperlink option)
8) right click ->hyperlink->Hyperlink target: Blank -> Hyperlink type: Reference
9) Hyperlink Reference Expression:
Eg:
"flow.html?_flowId=dashboardRuntimeFlow&dashboardResource=%2FSadakar%2FDashboards%2FIndustry_Wise_Dashboard& hidden_state=All"

This expression leads you to Dashboard2 when you click on "All states"  hyperlink on Dashboard1.

NOTE:
* Copy the URL of Dashboard and edit the URL 
* Remove http://localhost:8080/jasperserver-pro/  from URL and pass parameters from the URL.
* Passing Parameters is done with the following syntax :
    "URLofDashboardToNavigate&hidden_parameterName="+parameter
* You can add as many parameters as you want by typing 
  "URLofDashboardToNavigage&hidden_param1Name=parameter1& hidden_param2Name=parameter2

Eg:    &hidden_state=All&hidden_measure=revenue" 



I believe in " Learning never exhausts the mind" 

Sadakar 

BI developer

 



Wednesday, July 17, 2013

Taking backup from the jasper server with an example

Hi..
This post teach you how to take backup of your reports from jasper server..
It could be the entire folder which consists of list of reports
Or a single report.



Will update you with a nice working example as quick as possible as time is very short... probably in 2 or 3 days....
Mean while there is a superb post related to taking back up.
You can find the following link..
http://passionfordata.blogspot.in/2012/04/how-to-take-backup-of-jasperreport.html



Installing Jasper server community with MySQL Database



Pre requisites
1)  Install the Oracle/Sun Java JDK 1.6 or 1.7. Create and set the JAVA_HOME system
environment variable. i.e., Make sure Java path are set for your user profile or system
profile
Please follow the link to get the software.
2. Jasperreports-server-cp-5.0.0-bin-zip
a. Download Jasperreports-server-cp-5.0.0-bin-zip from the below mentioned location.
http://sourceforge.net/projects/jasperserver/files/JasperServer/JasperReports%20S
erver%205.0.0/jasperreports-server-cp-5.0.0-bin.zip/download
3. MySQL database.
a. Please follow this link to get the software.
http://dev.mysql.com/downloads/mysql/5.0.html
Note: We can use any other database as well. For demo purpose we are using mySQL.
4. Apache Tomcat Server
a. Please follow this link to get the software.
http://tomcat.apache.org/download-60.cgi
Follow below mentioned steps to install Jasper server CE 5.0.0 with mySQL database.
Step 1:- Extract all files from jasperreports-server-cp-5.0.0-bin.zip. Choose a destination, such as C:\Jaspersoft on Windows. The directory, jasperreports-server-cp-5.0.0-bin, appears in the file location you choose.
Step 2 :- Copy the <database>_master.properties file for your database from sample_conf and paste it to buildomatic:
Copy from — <js-install>/buildomatic/sample_conf/
Paste to — <js-install>/buildomatic
For example, copy mysql_master.properties to <js-install>/buildomatic.
Step 3:- Rename the file you copied to default_master.properties.
Step 4:- Edit the default_master.properties file to add the settings for your database and application server.
Below mentioned is a sample property values MySql database.
MySQL appServerType=tomcat6 [tomcat7, tomcat5, jboss, jboss7, glassfish2,
glassfish3, skipAppServerCheck*]
appServerDir=c:\\Program Files\\Apache Software Foundation\\Tomcat 6†
dbUsername=root
dbPassword=password
dbHost=localhost
Set the following in the file
webAppNameCE = jasperserver
***NOTE:
Place mysql-connector-java-5.1.10.jar driver in the following location
D:\install SW\jasperreports-server-cp-5.0.0-bin\buildomatic\conf_source\db\mysql\jdbc
Step 6:-
Run the js-install scripts.
a. Start your database server.
b. Stop your application server.
c. Open Command Prompt as Administrator on Windows.
d. Run the appropriate commands:
js-install-ce.bat : Installs JasperReports Server, sample data,
and sample databases (foodmart and sugarcrm)
js-install-ce.bat minimal : Installs JasperReports Server, but does not
install sample data and sample databases
NOTE
If you installed the optional sample databases, complete the installation by executing this command: js-ant deploy-webapp-ce from buildomatic location in command prompt.
For example in the following location:
C:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps
Step 7:-
Run the tomcat server and type the jasper server url.
For example: http://localhost:2222/jasperserver/login.html

Monday, July 15, 2013

JFree Meter chart customization in Jasper iReport-- Removing values from the meter...

Hi..
This artical will teach you how to write custom code for meter chart to remove the values over the meter.
You need to see the text(theroy) part of the post below mentioned before you start trying this

http://jasper-bi-suite.blogspot.in/2013/06/jfree-bar-chart-customization-in.html

For this artical the output would looks some thing like this.


See there are no values over the meter chart... That you achive using the code below.


package com.sadakar.meter.chart.customizer;
import org.jfree.chart.plot.MeterPlot;

public class MeterChartCustomizer implements  net.sf.jasperreports.engine.JRChartCustomizer{

  @Override
    public void customize(org.jfree.chart.JFreeChart chart, net.sf.jasperreports.engine.JRChart jasperChart) 
    {    
       MeterPlot plot = (MeterPlot) chart.getPlot();
       
        for(int i=0; i< plot.getIntervals().size(); i++)
        {
                if(i >0 && i < plot.getIntervals().size()-1)
                    plot.setTickLabelsVisible(false);
        }     
       
         
    }
}

Thanks for reading this artical..

If you have any queries.. you can mail me @ sadakar.1988@gmail.com

:) :) :) 

Other useful blog:

http://passionfordata.blogspot.in/




JFree Line Chart Cusomization in Jasper iReport. Line to Dashed-line, line width,gap in the dashed lines

Hello guys...

Here is the code for customizing Line chart....
The code credit goes to Mr. Shard Sinha.. who is an exprert in cutomizing..

You must read the points in the following artical before you starting this work to do.
http://jasper-bi-suite.blogspot.in/2013/06/jfree-bar-chart-customization-in.html

Grasp.. how to make jar file in NetBeans, How to make jar file, where to keep the jar file, where to call the class in iReport and all the theory related stuff from the above link...

What we are customizing is :
1) Line to Dashed line
2) Line width
3)Gap in the dashed lines

OUTPUT is some thing like as follows:



package com.sadakar.line.chart.customizer;

import java.awt.BasicStroke;

import java.awt.Stroke;

import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.renderer.category.LineAndShapeRenderer;

public class LineChartCustomizer implements  net.sf.jasperreports.engine.JRChartCustomizer{

    @Override
    public void customize(org.jfree.chart.JFreeChart chart, net.sf.jasperreports.engine.JRChart jasperChart) 
    {
        
       CategoryPlot plot = (CategoryPlot) chart.getPlot();
       LineAndShapeRenderer lineAndShapeRenderer =(LineAndShapeRenderer) plot.getRenderer();
       Stroke dashed =  new BasicStroke(1.0f,BasicStroke.CAP_BUTT, BasicStroke.JOIN_MITER, 10.0f, new float[] {10.0f}, 0.0f);
      lineAndShapeRenderer.setBaseStroke(dashed);
       lineAndShapeRenderer.setBaseItemLabelsVisible(false);
      lineAndShapeRenderer.setSeriesStroke(
            0, new BasicStroke(
                1.5f, BasicStroke.CAP_BUTT, BasicStroke.JOIN_MITER,
                1.0f, new float[] {4.0f, 2.0f}, 0.0f
            )
        );
       
    } 
       
    }
    
Thanks,
SADAKAR
Software Engineer in BI
(jasper, pentaho & Talend)

Jasper Server(Community OR Commercial) Starting up problem resolved by incresing the memory size

Hi Guys...!!!

Here is the tip to increase the size of the memory to start the jasper server ..

What could be the probable scenarios we can observe ..??

If you install external postgreSQL in your machine..
If you install community server with postgreSQL....
If you install another community server(with latest version) with embeded postgreSQL..
If you install commericial server(jasperserver-pro) with postgreSQL....

When you install all these type of servers in your machine ... there might be very slowness of jasper server startup... It may take 30 min or an hour to start the server..

Make sure when you install more than 1 server in your machine there should be different port numbers for every tomcat and every postgre SQL.

for example you have to give:
1) For community some 5050 for tomact,for postgreSQL 1234
2) For community with another version 6060 for tomcat, for postgreSQL 2345
3) For commercial jasper server pro 7070 for tomcat, for postgreSQL 3456
4) pro with another version 9090 for tomcat, for postgreSQL 6789

& also in services stop all the servers.. i.e, make services start up as manual but not automatic.


Increse memorey size in this file as shown below figure.
File location is :  C:\Program Files\jasperreports-server-5.1\apache-tomcat\bin


Edit here : shown red in color.

Shut down the server and then start the server... you are done.

It's always better to run the startup files and shutdown files from command prompt rather than from statr menu or cicking start service from services or clicking on .bat files in tomcat server.

Thanks,

Queries @ sadakar.1988@gmail.com

Another uselful blog :

http://passionfordata.blogspot.in/

Enjoy now starting up jasper server ... :)


Saturday, July 13, 2013

Where and How to download different professional versions of iReport ???

Hi..
The latest version of jaspersoft-pro is 5.2 . and the jasper community download page only gives us the updated software links.
Then how to download 5.1 or 5.0 or 4.7 or 4.5 and etc vesions of jaspersoft ?

Simple..

1. Go to
http://community.jaspersoft.com/download

2.Click on 3rd or any link as shown below image and copy the link address and paste the address in new window address bar of the browser and press enter


address bar:


That's it.
In this way you can download the software as you like .. inplace of 5.2 type 5.1 or 5.0 or 4.7 which ever version you want to download.


Thanks :)
SADAKAR
BI developer(Jaspersoft, Pentaho & Talend ETL)

Tuesday, July 09, 2013

XML as datasource for Reports and XPath Queries.

Hello Jasperians...!!!
Here is a nice tutorial for creating a report using XML file as data source and we use X-Path query to get the columns..

LINK :
1) Example
2)Example

Other useful links.


Thanks guys,
SADAKAR.P
Jr BI developer 
(Jaspersoft, Pentaho & Talend ETL)
Helical IT Solutions Pvt. Ltd,
Hyderabad

Monday, July 08, 2013

Stored procedure calling in iReport

Hi guys..
Here is the nice tutorial which demonstrates stored procedure in iReport .

Stored procedures in
MySQL,
SQL,
MS-SQL Server,
PgAdmin(PostgreSQL).


Find the below link and have fun

http://community.jaspersoft.com/wiki/ireport-calling-stored-procedures#PostgreSQL



Will update this artical with a nice working example either using MySQL or PostgreSQL as soon as possible.


Queries are welcome @ sadakar.1988@gmail.com

Thank you :)




Split Excel output into multiple sheets

Hi Jasperians ...!!!!

A friend posted an intersting thing Over LinkedIn that how to split Excel output into multiple sheets... !!! Just worked out and sharing it with you guys..!!!

It's a sample one.. No parameters, No headers are used... There might be getting issues when we use all the stuff..If we work out we can also overcome them.. Though I just want to share how we can split excel output into multiple sheets..!!

Greetings..!!! Here are the stpes to follow...!!!

Aim: To split the output into multiple sheets in Excel exporting
Solution Way: Printing data in one sheet & a bar chart in another sheet.

I have used the follwoing.
i) iReport Designer : 5.0.4 (Community)
ii) PostgreSQL : 9.2 (foodmart database)

Query I have used:
select  * from employee limit 15

Step 1: 
Open iReport desinger and save it to your fav location.

Step 2:
Write the query in query designer area

Step 3:
Remove all the bands from design area except
Column header, Detail & summury band.

Step 4:
Drag and drop your fav columns to detail band as shown in below figure
Drag and drop a bar chart to the summary band as shown in below figure


Step 5 :
We need to set properties at two places.

i) In the report properties
ii) In settings

i) In the report properties
Rport Inspector->ReportName(report1)->Right Click->Properties
Now add the propertey.
net.sf.jasperreports.export.xls.one.page.per.sheet
and value is set to be "true" do not use double quoutes for giving value but in xml code it automatically goes into double quotes.
Find the below image.


ii) In the report Settings
Tools->Options-> Click on export Settings -> In the list of export options click on Excel
It opens Excel Export Parameters
In common tab ---> Check One page per sheet.
Find the image below


NOTE:
Do not forget to place page break on the place from where you want to split the page.

In this example I used page break above the chart so that data comes in one sheet and chart comes in another sheet.

That's it... Save the report and see the preview.
I'm not seeing the preview in Server but previwing internally in the iReport.


OUTPUT
The output would be something like as follows. Find the images of excel sheet.

OUTPUT in first sheet:


OUTPUT in second sheet:



Thanks for posting a good idea to work out on LinkedIn.

Thanks for reading this document.

Queries are welcome @    sadakar.1988@gmail.com



Simlar posts on various sites:

1) http://stackoverflow.com/questions/3977658/how-do-you-export-a-jasperreport-to-an-excel-file-with-multiple-worksheets

2) http://community.jaspersoft.com/questions/531936/multiple-sheets-excel

3) http://jasperreports.sourceforge.net/config.reference.html

4) http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.one.page.per.sheet

5)http://community.jaspersoft.com/questions/533232/how-create-multi-worksheet-excel-jasper-repor

6) http://community.jaspersoft.com/questions/540310/unable-split-excel-output-multiple-sheets

7) http://stackoverflow.com/questions/8753401/exporting-a-report-with-multiple-sheets-from-jasperserver-to-excel


SADAKAR.P
Software Engineer in BI - Jaspersoft, Pentaho & Talend.
Hyderabad, INDIA.



Monday, June 17, 2013

JFree Bar Chart Customization in iReport.. Category axis labels overlapping is removed.

Hi folks,
Today we are going to learn how to customize BarChart Category axis.
Credit of developing code goes to Mr. Sharad Sinha who is my colleague. As part of sharing knowledge I'm gonna explain the steps.
I'm starting with the problem statement then solution accordingly.
Prerequisites :
iReport : 5.0.4
PostgreSQL : 9.2
Jasper Server : 5.0
Problem :
* We have a bar chart having many values to display on the chart .. i.e, let us say 100+
* This is not an XY Chart ... simply it is Vertical Bar Chart.
* Bars are coming up properly BUT the labels on the category axis are overlapped and coming in
    a line. or when the angle is set up to -70 the labels are coming as clumsy clumsy as shown in
   figure below.
* The values which we want to show on the category axis are : dates like 1 Jan 2013 and the next
   label we want to show 8 Jan 2013 and etc..
* Here is the snapshot of the problem statement.

Solution:
Our final out put should looks some thing like as follows.
What we have to do to get the above output ?
We have to write Chart customizer class and have to call that class from iReport.
I'm going to explain step by step to achieve this.

Read the points :
1.  Write chart customizer class java code in NetBeans.
2. Make a jar file.
3. Add that jar file in iReport as well in Jasper Server.
4. Call the class from the properties of Vertical Bar Chart in customizer class.

1.  Write chart customizer class java code in NetBeans.
package com.xyz.bar.chart.customizer;  //xyz is name of the company generally we give

import java.awt.Color;
import org.jfree.chart.axis.CategoryAxis;
import org.jfree.chart.axis.CategoryLabelPositions;


public class BarChartCustomizer
implements
  net.sf.jasperreports.engine.JRChartCustomizer
{
  private Number tickUnits;

  public BarChartCustomizer() {
    tickUnits = Integer.valueOf(0);
  }

  @Override
  public void customize(org.jfree.chart.JFreeChart chart, net.sf.jasperreports.engine.JRChart jasperChart) {
    org.jfree.chart.renderer.category.BarRenderer renderer;
    org.jfree.chart.plot.CategoryPlot plot;
    org.jfree.chart.axis.NumberAxis rangeAxis;
    org.jfree.chart.axis.CategoryAxis axis;
    renderer = (org.jfree.chart.renderer.category.BarRenderer) chart.getCategoryPlot().getRenderer();

    plot = chart.getCategoryPlot();
    rangeAxis = (org.jfree.chart.axis.NumberAxis) plot.getRangeAxis();

    axis = plot.getDomainAxis();

    CategoryAxis domainAxis = plot.getDomainAxis();
    CategoryLabelPositions pos = domainAxis.getCategoryLabelPositions();
   for(int i=0; i< plot.getCategories().size()-1; i++)
   {
      if(i%6==0){
       String cat_Name = (String) plot.getCategories().get(i);

      }
      else
      {
       String cat_Names = (String) plot.getCategories().get(i);
       domainAxis.setTickLabelPaint(cat_Names, Color.white);
      
      }
       
   }
  // plot.getDomainAxis().setLabel("TEST "+plot.getCategories().size()+"  "+pos.);
     
  }
}
2. Make a jar file.
* Right click on the project and click on clean and build
* Net beans by automatically create jar file.
* find this jar file in "dest" folder of your project.

3.Add that jar file in iReport as well in Jasper Server.
Location to place this jar file in Jasepr server.
C:\Program Files\jasperreports-server-5.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF\lib
Location to call the jar file in iReport.
* On the menu bar go to
Tools --> Option --> iReport -->Class Path--> Add jar
* Select the location of jar file.

4. Call the class from the properties of Vertical Bar Chart in customizer class.

* High light the chart(bar chart in our case)  that you want to apply the customizer class.
* Go to properties (right side appears) ---> go to customizer --> in the blank space write the
     classname including the package.
* For example in our case:
package com.xyz.bar.chart.customizer.BarChartCustomizer


That's it we have done with the customization of the chart.

NOTE: after adding jar file to the server, you must restart your server other wise we can see the effect of added jar file in our report
NOTE: Make sure to import necessory library files in Netbens while devloping the plugin.
* Your imported library files(nothing but jars) should compatable with the files that your jasper server is using other wise you will get minor/higher related error when you run your report.
* Find the image below.

Thanks for reading document :)

Queries are welcome @ sadakar.1988@gmail.com


Wednesday, May 29, 2013

Dynamically hiding the columns in table component in iReport

Hi folks.. here is the next stuff I worked out today.. i.e, on dynamically hiding the columns in the table component.

Introduction: 
  • For some reports we need to display data in the format of table.. To do this in iReport we use Table Component from the palette section. 
  • Sometimes to take better decision  we may not want all the fields/columns to display in the tabular data display. 
  • Here are the steps to design our report with hiding columns
We work out with parameters to achieve this functionality.
We need 'n' number of parameters as per our requirement.

This tutorial is taking the following prerequisites.

iReport 5.1.0, Jasper Server 5.0, PostgreSQL 9.2, foodmart database. 
  
Step 1: Creating New report
File-->New-->Name your report and save in your favorite location.
Step 2:  Creating dataset.
* From the Report Inspector , right click on report name.
* Then click on Add dataset-->Give data set name and then click on finish.
                                                --> give the connection 
                                                 --> write your query(first you can write a        
                                                                                            simple query then your original one) 
In our example :
       Data set name  : tableDataset
       Connection      : foodmart  (It is data soruce)
       Query                : SELECT distinct employee_id,full_name,first_name,position_id FROM                         
                                                                                                                                    employee
Step 3: Creating table using Table Component from Palette.
* Remove all the unwanted bands from the design area. 
* We have taken only Title band and deleted the rest. 
* Drag and drop table component from the palette section and fill the columns as usually. 

NOTE : Upto step 3 we do the report design normally

Step 4 :  Creating parameters
* We create as many parameters required.. i.e, we have to use the number of parameters equal to
    the number of hiding columns. 
* In our example I'm displaying 4 columns(Check step 3 Query).. and I want to hide 1 column or   
    2 columns or 3 columns or 4 columns...( It's our choice to hide the columns from 1st column                to last column or any random columns selection.).

* Just observe the below image
   

and see the below image to find the properties of parameters 
(I'm showing only one parameter properties in the image , for the remaining 3 parametres we have to give like this one)

There are 4 parameters in our example for four fields in the tabular component.

1st parameter details :
Name : showEmployee_Id
Parameter Class : java.lang.Boolean
Use as a prompt : check it
Default Value Expression: new Boolean(true)

2nd, 3rd and 4th parameters details falls in the same way as above parameter. 

Step 5: Using the created parameters 

* We have placed the table component in in Title band ( It is not mandatory.. we can keep in  summary band also )
* We use the created parameters for the columns available with the table component.
* find the image below. 

  
* We have to write printWhenExpression for the columns as shown in the image.
* i.e, for all the columns .. we have to give conditions with suitable parametername. 
* In our example I have given for all the four columns in Column Header Section like below
   Column 1 : Highlight this one and go the corresponding property. 
   In the properties window give the Column Print When =new Boolean($P{showEmployee_Id})
* In this way we have to write conditions for the remaining columns with suitable parameters like above. 


** By default the columns in the Detail section takes the same conditions that we have written for the columns in the ColumnHeader section. 


That's it we are done with the reporting design. 

NOTE: 
* Our report should consists of some main query.. otherwise we can not see the output. It'll just show our report is blank and the document is empty.

Preview:
Preview with in the iReport

* We need to give either true or false in the text box as we are taking in the parameters as Boolean.
* Give the same for all the parameters. 

* In our example I have given like this for this particular preview.

showEmployee_id : false
showFull_Name : true
showLast_Name : false
showProduct_Id : true

* So we are able to see only the true valued parameterised columns in the output.
* i.e, it looks like in the below image.
* see one more output where I've given fist one as true and the rest are as false.


* In this way we have made our report dynamic by hiding columns  of table component.

Preview in the server:

* Upload the report to the server in a regular way.
* And create the input controls for the parameters.. parameters in iReport=inputControls in 
repository of inputs
* parameter name in the iReport should be same as ID of the inputcontrol






* Crate all the 4 input controls as shown in the images.
* Remembers the Type for the input controls is Boolean 

Now go to the location of the the report in the server and see the output.

* The out will looks like in the below image with the input controls specified.
OUTPUT 1: images on the selection of input controls




OUTPUT 2: 
* If we select 'n' number of parameters(input controls) we can see that many columns output in the table component. 


Queries/Suggestions/Improving the concept in some other way is/are welcome @ sadakar.1988@gmail.com


Thanks for reading this article 

:) :) and :)