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, September 24, 2015

Tip : How to Handle null values when passing nothing in Single Select Query parameter in Jasper Design Studio 6.x (Or) Get all the data of query result set when passing nothing in Jasper Design Studio 6.x

Hi Community,

In this post we will see how to handle NULL input (-- input value in single select parameter) in single select parameter.
i.e., When passing (--) input get all the values of query result set and when passing some value from selected input control get the report output for only that parameter.

Note that this example doesn't cover step by step procedure to develop a report from scratch.
Click on images to get the best view of content placed over.

Software Used to test this example : 
TIBCOJasper Design Studio 6.1.1 CE final
TIBCO Jasper Server 6.1 CE final
PostgreSQL database - foodmart database

Core part of the example 

1) Design the report as usually.

2) Create filter for your report, in this example lets create a folder for education filed
     parameter name : param_Education
     Type : java.lang.String
      Default value : I have not taken any.

3) Query
SQL Query
SELECT DISTINCT
                        education,state_province,
                       sum(total_children) "Total Children",
                       sum(num_children_at_home) "Children at home"
FROM customer
WHERE (education = $P{param_Education} OR  $P{param_Education} IS NULL)
GROUP BY
              education,
             state_province
ORDER BY
         education,
         state_province

4)
# Publish the report to the server (point out the report to use foodmart database created in jasperserver - which is not discussed in this post).

# Create the parameter in jasper server with the same id .
parameter name : Education
parameter id : param_Education
parameter type : Single Select Query

# Give the data source connection for parameter and write the query for this parameter to get the values in input control drop down.

Input control query : SELECT DISTINCT education FROM customer

# Do not select parameter as Mandatory




5) Preview of the report in Jasperserver by selecting input control as

Test 1 : Select nothing and click on Apply button ( i.e., select -- from drop down and click on apply button). 


Test-2 : By selecting an input control value




Download Example : Click Me

References :

 http://stackoverflow.com/questions/4697187/need-for-a-jasperreports-null-parameter-value-to-show-all

I hope this helps some one :-)

Did you find it useful ? Please share for community developer like us..!!!

Improvements, suggestions are welcome.

Cheers...!!!



Tuesday, September 08, 2015

Step By Step Procedure to run the existing examples of D3 chart samples(that comes with product) in 6.x Jasper Design Studio 6.x and Jasper Server 6.x CE(Pro)

Hi Folks,

In this tutorial, you will learn/see how to run D3 chart(s) samples that comes with TIBCOJasperserver 6.x product.
Notice that you will only see how to run the existing samples given by Jaspersoft in Jaspersoft Design Studio 6.x and Jasperserver CE 6.x and in upcoming posts you will find creation/development other D3 visualizations.

This post is specific for below software setup as on this article written date (9th Sep 2015). With future releases of product the procedure may change as the sources of this article are saying that they eventually move the jars/files to Jasper Libraries.

Software Used for Testing :
1) JasperDesign Studio CE 6.1 installed as a plug to Eclipse Java EE IDE for Web Developers.                                                                                                         Version: Mars Release (4.5.0)
   (You can use stand alone studio - I just wanted to get the studio in Eclipse )
2) Jasperserver 6.1 CE
3) Operating System : Windows 10
4) Java 1.7 installed in OS.

This post is divided into 2 parts. 
1) Executing/Running the samples in Jasper Design Studio
2) Executing/Running the samples in Jasper Server


NOTE : Click the images to get the best view of content 

1) Executing/Running the samples in Jasper Design Studio 6.x (I used 6.1 and the latest release is 6.1.1)

1) Installation of PhantomJS and setting path of its executable file in Jasper Studio.
2) Setting the path for require.js
3) Setting customvisualization pattern path

1) Installation of PhantomJS and setting path of its executable file in Jasper Studio.

a) Download the PhantomJS software from here : http://phantomjs.org/ and unzip it. 


b) Set the above .exe as path JapserDesign Studio properties (Images shown below are from Eclipse). 

Go to Windows -> Click on Preferences  -> Jasper Design Studio -> Click on Properties

com.jaspersoft.jasperreports.components.customvisualization.phantomjs.executable.path =C:/Users/Sadakar/Downloads/backup/phantomjs-2.0.0-windows/phantomjs-2.0.0-windows/bin/phantomjs.exe

com.jaspersoft.jasperreports.components.customvisualization.require.js=file:/E:/1_Softwares/JAVA IDE/eclipse-jee-mars-R-win32-x86_64/eclipse/configuration/org.eclipse.osgi/936/0/.cp/resources/scripts/require-2.1.6.src.js

c) Add custom visualization pattern property

net.sf.jasperreports.web.resource.pattern.customvisualization =com/jaspersoft/jasperreports/customvisualization/.*

(Note that require.js by default comes with Jasper Design Studio 6.x and as I am using Eclipse I have referred the hard coded path. Also, this property by default set in the properties.. Just check whether it is there or not, if not provide the path).

Quick Understanding Images : 
1st two properties image



3rd property image



d) Import the the existing example(s) D3 charts project zip file to Studio and run the reports. 
Jaspersoft has provided few examples demonstrations that you can download from here

Direct Download Link (In feature it may move)
http://community.jaspersoft.com/sites/default/files/wiki_attachments/cvc_samples_20150127.tgz

Or

Go to this page http://community.jaspersoft.com/wiki/custom-visualization-component-v60x-how-run-samples and check the "Installing the samples" section and download the zip file.

Now, In studio
File > Import > General > Existing Projects into Work space > Select Archive file > select the downloaded zip file > Click Next then Finish.


Each of the folder is an example of D3 visualization.

e) Run the report 
Expand an example : lets say raphael_map and observe the the different .js files and .jrxml/.jasper file. (While developing we won't get all these. To get all of them, right click on folder name and build it or right click on build.js file and build the file.. Observe the newly generated .js files).

Now, open the .jrxml file and click on Preview button.


Exporting to PDF



Do the same for other samples ..
(Got null pointer exception when previewed in Interactive preview - Need to check, will update this point).

2) Executing/Running the samples in Jasper Server 

1) Give the path of PhantomJS .exe file in Computer Environment variables. 

2) Add below properties in jasperserver.properties file

File location : C:\Jaspersoft\jasperreports-server-cp-6.1.0\apache-tomcat\webapps\jasperserver\WEB-INF\classes

com.jaspersoft.jasperreports.components.customvisualization.require.js=file:/E:/1_Softwares/JAVA IDE/eclipse-jee-mars-R-win32-x86_64/eclipse/configuration/org.eclipse.osgi/936/0/.cp/resources/scripts/require-2.1.6.src.js

net.sf.jasperreports.web.resource.pattern.customvisualization.scripts=com/jaspersoft/jasperreports/customvisualization/resources/require/.*

NOTE : look at the yellow colored things in above code and do it this way.

3) Publish the Reports to the Jasper and view the output
a) Open the JRXML from the folder.
b) Click on Publish button -> the server will pick .js file and JRXMl file.
c) Provide data source ( for this example : JNDI data source I have chosen as suggested in WIKI pages).
d) Browse the repository(jasper server) for the report and click on RUN button.

4) Sample output 



Exporting to PDF


Another Example sample output : 

In futures posts we will see how we can develop our own D3 visualizations. (If the script is not limited by Jasper licences).

: -) Add links or references in comment box if you already have an example and help the community

Thank you for your time on this page. Suggestions and improvements are always welcome.

Did you find it useful ? Please share this page for community developers.

References : 
1) http://community.jaspersoft.com/wiki/custom-visualization-component-v60x
2) http://community.jaspersoft.com/wiki/custom-visualization-component-setup-jasperreports-server
3) http://community.jaspersoft.com/wiki/custom-visualization-component-v60x-how-run-samples
4) http://community.jaspersoft.com/questions/851432/custom-visualization-component-community-version-faild
5) http://community.jaspersoft.com/questions/847377/problems-custom-visualization-component-windows
6) http://community.jaspersoft.com/jaspersoft-studio/issues/4096
7) http://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v610/custom-visualization-component



Tip : Export Images to Excel Export in TIBCOJapser Server 6.1 Professional

Hi Folks,

Below explained tip is very useful for getting images(usually logos of companies/clients) when exporting reports to Excel(Excel Paginated).

Software used to test this tip : Jasper Design Studio Pro 6.1 and Jasper Server 6.01 Pro
1) Click on the Report Name in outline section. (Highlight the report name).
2)  Go the properties and from the list of drop down choose
net.sf.jasperreports.export.xls.ignore.graphics
3) By default above property value is true
4) Make it false i.e,
net.sf.jasperreports.export.xls.ignore.graphics = false

Find the image below for quick understanding.


Sample output : Excel Export



Reference :
http://community.jaspersoft.com/wiki/no-images-xls-export
http://jasperreports.sourceforge.net/sample.reference/xlsfeatures/

:-) :-)