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

Thursday, March 27, 2014

Inter Panel Communication in Jasper Dashboard by passing parameter from one panel to other panel OR Drill down frames with in Single Dashboard page by passing parameter(s)

Hi Guys,
Jasper by default doesn't provide drill down functionality with in the frames on dashboard.
Here is way how to achieve this functionality.

R&D links from Jasper Community 


1) http://community.jaspersoft.com/questions/537996/drill-down-reports

2) http://community.jaspersoft.com/questions/537752/materials-dashboardmashboard-webinar

3) community-static.jaspersoft.com/sites/default/files/questions/webinar_extending_dashboards_20110308.pdf

4) http://community.jaspersoft.com/wiki/creating-interactive-dashboards-using-javascript


Example :
Report 1: Displaying a pie chart with parameter (parameters : gender)
Report 2: Details of each slice of pie chart in bar chart (parameters : gender, states)


Environment :
Jasper Server 5.5 pro
iReport : 5.5 pro
Database : Postgresql foodmart

Functionality :
Let's say your input control is 'F' for gender in report 1 and when you click on state CA slice of pie chart then the Report 2 should catch the gender and state parameters from Report 1 and display the data next to this report.
i.e., Drilling down the reports in single dashboard page with parameters passing.
Technique of using contentFrame_frame_2 as Hyperlink target with Report Execution as Hyperlink Type.

Report 1 Query :
SELECT distinct
state_province,
sum(unit_sales)
FROM customer c,
sales_fact_1997 sf7
where c.customer_id=sf7.customer_id and gender=$P{gender}
group by state_province

Report 1 Pie Chart Hyperlink properties
Hyperlink target : contentFrame_frame_2
Hyperlink type : ReportExecution

_report "/Sadha/Test/IPC2"
gender $P{gender}
state $F{state_province}
viewAsDashboardFrame "true"

Images for Report-1







Report 2 Query :
SELECT distinct city,sum(total_children) from customer where gender=$P{gender} and state_province=$P{state} group by city


Report 2 Sample Image


Dashboard Final Output:


NOTE:
1) No need of adding any js code as given in the link.
2) Just need to write "Hyperlink target"=contentFrame_frame_2
3) Hide the parameters where ever required. i.e., Un check visible. 



Fixed values on Fusion Bar Chart category axis using SQL query

Hello guys,
Here is my recent work out on fixing values on category axis using SQL query for fusion bar chart.

This tip will give idea on fixing the X-axis values constant on category axis.

Note that there is no property to set max and min category values on fusion bar chart category axis. Hence a thought of implementing using SQL query.

Problem scenario :
Plot 5 A.M to 5 P.M constant times on  category axis when you have random time hours in your query result set.
Query output :
Time   Count
6 A.M    80
7 A.M    45
11 A.M  89
12 P.M  65
3 P.M    76

If you directly give it in

SELECT * FROM
(
SELECT CAST(5 as integer) as hour from DUAL
UNION
SELECT CAST(6 as integer)as hour from DUAL
UNION
SELECT CAST(7 as integer) as hour from DUAL
UNION
SELECT CAST(8 as integer) as hour from DUAL
UNION
SELECT CAST(9 as integer) as hour from DUAL
UNION
SELECT CAST(10 as integer) as hour from DUAL
UNION
SELECT CAST(11 as integer) as hour from DUAL
UNION
SELECT CAST(12 as integer) as hour from DUAL
UNION
SELECT CAST(13 as integer) as hour from DUAL
UNION
SELECT CAST(14 as integer) as hour from DUAL
UNION
SELECT CAST(15 as integer) as hour from DUAL
UNION
SELECT CAST(16 as integer) as hour from DUAL
UNION
SELECT CAST(17 as integer) as hour from DUAL
)x
LEFT JOIN
(
SELECT
hour,
count(files) as filecount
FROM files_table

) y

Again if you want to see them with A.M, P.M 
make the above query as sub query 

SELECT
  CASE
       subTable.hour
          when 12 THEN '12 P.M'
   when 13 THEN '1 P.M'
            when 14 THEN '2 P.M'
           when 15 THEN '3 P.M'
           when 16 THEN '4 P.M'
           when 17 THEN '5 P.M'
    ELSE CONCAT(to_char(x.hour),' A.M')
  END  Common_Hour,
subTable.file count
FROM
( write above query
)subTable

That's it.

Sample figure :


Thank you.


Rabbit VCS client tool installation in Cent OS 6.5

Hello guys,

Rabbit VCS installation guide :

What is Rabbit VCS?

RabbitVCS is a set of graphical tools written to provide simple and straightforward access to the version control systems you use. We provide multiple clients and extensions designed to give you a uniform experience no matter what development tools you use.

The below link gives you the step by step installation of Rabbit CVS.

Other references :


Alternatively you can also install Tortoise SVN client in  Cent OS using Command line utility. 









Wednesday, March 19, 2014

Relative size of the dashboard in jasper server - No scroll down or No scroll side of dashboards in tablets/laptop browsers


Relative size of the dashboard in jasper server - No scroll down or No scroll side of dashboards in tablets/laptop browsers.

IMP NOTE: Update : Ther is a bug in 5.5 pro with Proportional size and
 with viewAsDashboardFrame=true parameter
(Tested date of mine : 20th March 2014)

As of my R&D and support from jasper guys, came

1. Generally people do not see their dashboards by scrolling down or scrolling side in the browsers when they use laps or tablets.
2. Tabs/laptops have their own window sizes for browsers.
3. You relatively adjust the size of the dashboard at the time designing itself so that you can avoid scrolling side or scrolling down in the browsers installed in tablest/laptops.
4. There are basically two sizes we can set for dashboards in the jasper server
They are :
i) Fixed Size
ii) Proportional size.

5) You can find these options while designing “a new dashboard” or a dashboard in “Open in Designer mode”
Fixed size values are : Find the below image

Click on Options -> Guide 
Blue dot indicates the selected size for your dashboard. 

6) You need to select the Proportional option b'z you are going to see the dashboard in tablets/laptops ... where you NO need to scroll to see the full view of the dashboard.
The frames will render according to the browser size.

Quick Reference :

Sunday, March 16, 2014

Part 1 : Jasper server 5.5 customization

1) Changing "Jaspersoft:Login"  to "Your Organization:Login" (Changing the page name -located on top of URL)

Below image give an idea on what you are going to modify.


1. You need to modify decorator.jsp file
2. Location of the file
C:\Jaspersoft\jasperreports-server-cp-5.5.0\apache-tomcat\webapps\jasperserver\WEB-INF\decorators
3.Search for the below code(Original code)
<title>Jaspersoft: <decorator:title /></title>
4. Modified Code
  <title>Sadakar Consultancy Services: <decorator:title /></title> (note that Sadakar is the name of your organization).
5. Reload the page. You will find the modified name.
Eg:




 NOTE:
You need not to restart the Jasper server as you are modifying only message information.

Tool tip for Fusion charts in Jasper iReport /Studio - Giving multiple values in Tool tip.

This post will talk about smart functionality of tool tip for fusion charts and reference links for HTML-5 charts tool tips.

Giving multiple values in tool tip for fusion charts.. 


Multiple field values in Tool tip in Jasper Fusion charts
1) Right click the chart ->Chart Data ->Data set items ->Data set items -> Item Hyperlink -> Tool tip

"product name:"+$F{product_name}+"\\n"+
"product count:"+$F{product_count}+"\\n"+
"product amount:"+$F{product_amount}

Problem with new line
Test 1:
\\n worked wtih Mozilla firefox 27.01 .. It may not work with older versions of fire fox
The older version fire fox uses <br> or <br/>  tags inplace of \\n

Test 2:
\\n worked with Version 31.0.1650.63 Built from source for CentOS release 6.5 (Final)


Advanced formatting with new HTML5 charts
http://community.jaspersoft.com/wiki/advanced-formatting-new-html5-charts

Customizing Tooltips in JasperReports HTML5 Chart Components 
OR

Thursday, March 13, 2014

Google Chrome OR Chromium installation in Cent OS 6.5 final:


Google Chrome OR Chromium  in Cent OS 6.5 final

Hi Guys,
We can not install Google Chrome in Cent OS 6.5 (as of March 13th 2014, in future Chrome may support linux cent OS for redhat) as Chrome has stopped its support to Redhat Cents OS..
Instead we can install Chromium browser which is similar to Chrome for Redhat linux machines(Cent OS).

Information on the same you can find using below link

Find the step by step installation of Chromium in using below link

To install Shockwave Flash plug-ins follow below link

Could Not Load Shockwave Flash error on chrome with win 7


NOTE :
You need to type chrome://plugins in the browser URL

Saturday, March 08, 2014

Tomcat is running but Jasper Server UI start Up problem - How to resolve it ?

Hi..
This post talk about a trick of running Jasper Server UI

What is/are the general problem(s) ?
Windows :
Generally, we run Jasper Server by going to
All Programs -> JasperReports Server CP 5.5-->Start or Stop Services ->Start Service
This way works better if you have not installed many servers / soft wares for which the services start with booting of the system whenever you switched on your machine/windows server.

In windows machine, you can stop the softwares in services window.(In W7 search box just type the services and open it)...
Simply type J ... the command will take you to JasperPostgres and JasperServer.. where you can right click on each of them individually and set the start up as Manual..
Now, how you can run the services ?
Once you on your machine/windows server go to command prompt and then go the bin folder of Jasper tomact and then issue the startup.bat command(for eg: C:\Jaspersoft\jasperreports-server-cp-5.5.0\apache-tomcat\bin\startup.bat).. It'll open up the Jasper server pop up starting jar saying the server start up information(it's tomcat service startup).. then you have to go the services and 1st start the Jasper Server Postgres and then the Jasper server service...
This will work fine..

If  the above two approaches fails to run Jasper server UI but tomact runs, how you can make jasper server UI to run ?
 
 If you are an expert in working with tomcat server you have already known how to start the UI applications by logging into managers roles..
The same approach is given below to run the Jasper Server UI from tomcat Manager..

You need to set the role and user name for the tomcat server manager.. i.e., you need to modify the tomact-users.xml file
Location of this file :
C:\Jaspersoft\jasperreports-server-cp-5.5.0\apache-tomcat\conf\tomact-users.xml
Add the below code in between <tomcat-users> and </tomcat-users> tags

<role rolename="manager-gui"/>
<user username="tomcat" password="s3cret" roles="manager-gui"/>


You can also give your own your username and password for this..

Now, stop the tomcat from command prompt using below command
C:\Jaspersoft\jasperreports-server-cp-5.5.0\apache-tomcat\bin\shutdown.bat

 Start the tomcat ..
C:\Jaspersoft\jasperreports-server-cp-5.5.0\apache-tomcat\bin\startup.bat

Once you run the tomcat server, you can see the server start up in pop up window opens with jar icon...
 and then quickly go to the services and start the jasper server postgresql server..

Once you see the message of  jasper server start up on the popup log , and postgrese start up in services...

Go to web-browser and type
http://localhost:8080/manager
Give tomcat/s3creat and login to the mangers roles..
There you can find the list of applications..
click on start button for jasper server UI application and wait for few seconds/min.. you can find the start stats on top
The below image gives you the better idea than the above description.


Now go to web browser type http://localhost:8080/jasperserver

:-) :-D :P














Thursday, March 06, 2014

Date input control default value in jasper iReport - Giving date value in default expression.


This simple post tell you about the issue in giving default value for date input control(s)

Default value = Direct date value 

When you give date input control default value directly for example 06/03/2004 you may get the default value in the server input control for it as 0.5677777 or some thing similar to it or some other date..

You need to give the date value using SimpleDateFormat class along with parse function applied for that date.

For example:
new SimpleDateFormat("MM/dd/yyyy").parse("01/06/2014")

The above will come as it is in the server date input control now.

:D

Wednesday, March 05, 2014

Jasper Server Calendar Date input control date fomat change - Change date format from yy-mm-dd to mm/dd/yyyy

Hi Guys,

This post talk about how you can change the date format of built in Calendar  in Japer Server.

Example Scenario :
Changing the yy-mm-dd format to mm/dd/yyyy
For instance
Changing 2014-03-06 to 03/06/2014

Steps :
1) You need to change the properties of jasperserver_config.properties filein 2 places
2)
Location of the file

/opt/jasperreports-server-5.5/apache-tomcat/webapps/jasperserver-pro/WEB-
INF/bundles
File Name:
jasperserver_config.properties

3) Modify below code in the file
1st place code
 Original format : comment it
# Used for parsing and formatting dates by server
#date.format=yyyy-MM-dd
#datetime.format=yyyy-MM-dd HH:mm:ss
#time.format=HH:mm:ss
 Modified format:
 date.format=MM/dd/yyyy
datetime.format=MM/dd/yyyy HH:mm:ss
time.format=HH:mm:ss

2nd place code
Original format :
Comment it
#calendar.date.format=yy-mm-dd
#calendar.datetime.format=yy-mm-dd HH:mm:ss
#calendar.time.format=HH:mm:ss


Modified format:
calendar.date.format=mm/dd/yy
calendar.datetime.format=mm/dd/yy HH:mm:SS
calendar.time.format=HH:mm:ss

Once you done with the modification you must have to restart the jasper server.
Sample output :



Timezone issue while connecting to Oracle database from SQL developer client Tool

Hi Guys..

You need to modify sqldeveloper.conf
Steps are given below
1)  Go to the installation directory of Oracle SQL Developer.
2)  Open the file located at: sqldeveloper/bin/sqldeveloper.conf.
3)  At the end of file, add the following line:
    AddVMOption -Duser.timezone=GMT-4

4) Now, restart the SQL developer tool from command prompt.
 Command to start :
sqldeveloper/sqldeveloper/bin$sh sqldeveloper.sh
OR
sqldeveloper/sqldeveloper/bin$./sqldeveloper.sh

5) Connect to data base and test the connections now, you will not get time zone error while connecting. 


I believe in "Learning never exhausts the mind"  

Install SQL developer in Cent OS 6.5

Hi Guys..

SQL developer client tool installation in Cent OS 6.5

1) You must install java 1.6 version to run SQL developer in cent OS.
    Tried with Open JDK installation and tried with it JDK path (for example open jdk path in cent OS is : [/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0]) but unable start the SQL developer

2) Later on with 1.6 installation and pointing to it JDK path(~/jdk1.6.0_25/bin/java) it has worked with out any issue.

3) Download SQL developer from  Oracle site [Click here for quick link]
   Click on the Linux RPM Download ..
   Once the download completes Extract it to a folder
Navigate to below path
sqldeveloper/sqldeveloper/bin
Click on sqldeveloper.sh file from command line
i.e., sqldeveloper/sqldeveloper/bin/sh sqldeveloper.sh
OR
sqldeveloper/sqldeveloper/bin/./sqldeveloper.sh

SQL developer will take JAVA home to run for the 1st time .. whether it is a window or linux machine

Give your Java 1.6 home location
Find it with which java command give that location and press enter.




Connecting to Oracle databse from Jasper iReport & Timezone issue

Hi Guys,
While connecting to Oracle database from Jasper iReport you may get time zone error message .. to over come this you need to set the timezone in your iReport.

How to connect to Oracle database from jasper iReport ?
1) First thing you need to know which database version you are using (either oracle 10g/11g/12)
2) You need to download the appropriate jdbc connector from oracle site( you need to have an account for oracle to download)
For example :
To connect 10g database, you need to download classes.12.jar file from below oracle site
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc101040-094982.html

Give a try with ojdbc14.jar file also.

NOTE:
Different versions of Oracle database uses different jar files

3) Placing jar files in iReport
Approach-I
Once the download is completed you need to add that jar file in iReport lib folder which you can find in iReport installation folder.
Location to place the jar file 
C:\Program Files\Jaspersoft\iReport-5.5.0\ireport\libs
Close the iReport if you opened already and Open/start it again(It will load the jar file when you restart the iReport)

Approach-II
Alternatively you can add the jar file in classpath option of Tools
i.e., Go to Tools->Options->Class Path->Add jar file
Once you add the suitable jar file you need to check that jar file.


Approach-III
i) Go to Window ->Click on services(Ctrl+F5)
ii) Expand Database ->You will find Drivers ->Right click on it -> Add jar ->It pop ups a window asking you to locate jar file.. locate the suitable jar file for you Oracle jdbc connection.

Either of the 3 approaches given above must work to add Oracle jdbc driver to iReport.

Now Connecting to Databse
1) Click on Databse icon ->Click New->Click on JDBC Connection  Click on next.
2) JDBC Driver class
Oracle (oracle.jdbc.driver.OracleDriver)
[This should be black in color which means that you have added oracle jdbc connector to iReport using above either 3 approaches]
3) URL
Syntax : jdbc:oracle:thin:@localhost:1521:DatabaseName 
Example1 : jdbc:oracle:thin:@localhost:1521:orcl (where orcl is SID of your Oracle)
Example 2: jdbc:oracle:thin:@192.168.2.9:1521:orcl (Remote database connection using IP)
4) Username & Password
For example : sadakar/sadakar
5) Click on Test Connection .. If the Connection is successful click on OK button.



Where you need to set the timezone in Jasper iReport ?

1) Go to Tools->Options
2) Click on Compilation and Execution
3) Select Report Timezone from the list of drop down
   for example :
   GMT0 (GMT+00:00)
 
Hope this information might be useful for newbies :D


Jasperserver 5.5 professional import-export utility issue while installating in Cent OS

Hi Guys,
This post talk about the below error(import-export.xml) error while installation goes in Cent OS for non root users.
Environment :
Cent OS 6.5(final)

Solution for this error :
Solution-I : --> Installation as root user
* You need to install the jasper server with sudo permission
* for example in Cent OS 6.5
$su
$password(you need type your sudo password of your system- which is a root privilege password )
* This password will be created when you install Cents OS in your machine or you need get it from your IT Team.
* installation will go under opt/ folder
* Though you are installing it under opt with root privileges if you get the same error you need to add your server host in hosts which generally located in etc/hosts for Cent OS
* Open it with vi editor(supported editor) and you need to add your servername/host at the end of each line
* add your server name/machine name at line begining with 127.1.0.0 or localhost
* Also add the same at the end of the second line. 

Solution -II ---> Installation as non root user
In this case you need to install the jasper server as war file installtion.
Refer User Guide for war file installation for non root users in Cent OS.