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

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