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, 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.

  • 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                         
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. 

* 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 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

* 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 :)

Tuesday, May 28, 2013

log files in jasper server



List Of Questions in iReport

  1. Create a report without writing any sql query in query editor ? 

Domain based report Creation from iReport

Here are the series of steps to create a domain report from jasper iReport.
In this tutorial I'm using
iReport-professional 5.1.0
Jasper-server 5.0
postgreSQL(foodmart database).

Few points to note down about domains in jasper server and domainReport in iReport. 

  •  Community Edition of Jasper Server and iRport do not provide the feature of domains and domain based reports. 
  • Professional and Enterprise edition the Domains act as data sources. 
  • They are a meta layer between the reports and the underlying data base.
  • The idea with Domains is they allow for easy joining of the data base tables and organize the resulting columns into a nice business friendly presentation.
  • Domains also allow for calculated fields. 
  • The calculations are created using DomEL (domain expression language). 
  • This is a simple language that allows for manipulation of fields that resolves to SQL in the underlying DB.

*  We do not create any database connection externally for developing domain based report which is a traditional way of creating database connections using JDBC and JNDI.

* Before we start developing domain based report we should create a domain in the jasper server.

* And we should connect to JasperServer from iReport. 

* To know the procedure of creating domain in the jasper server plz  read the below article. http://jasper-bi-suite.blogspot.in/2013/05/creating-domain-in-jasperserver.html

* In this tutorial I have created a domain in jasperserver and named it as foodmart_domain.
* My domain consists of two tables. They are i) employee ii) store 
* Fields I used for employee table are : 
        first_name, last_name,gender,marital_status,hire_date,postion_title

* Fields I used for store table are : store_number,store_city,store_country 

Do this first
Go to window->Click on Domains --> we can observe the window appearing just below to the Report Inspector window which is known as Domain Window. 

Step 1: 
File->New->Click on Domain Report(appars at leftsidd)->Click on BlankA4->Click on Finish.
Step 2:
* We will navigate to the following window. 
* Give all the details 
  Connection : This is nothing but connection from iReport to JaseperServer
                          ( We connect from Repository panel of iReport)
  Domain       : Select a domain which you have already created in jasper server.
                          ( In this tutorial I have already created a domain and named it as   foodmart_domain, and I've not shown the procedure of creating domain in this tutorial b'z we are    learning how to create a report using domain but not domain creation )
 Filters          :  Filters are nothing but input controls in the domain based report. 
                          * We can select the field which we want to use as input control
                          * In this tutorial I have used two filters(input controls).
Find the images below for filters creation.

* Available fields :  As we successfully connected to our foodmart_domain we can observe the         
                                     the tables(of course fields also.. just double click on the folder of       employee or store.. we can observe the fildes also ) available with the domain left side of the above image. 
* Condition Editor : To use the filters concept we just have to
       --> highlight(click on the field) the field from Available fields panel 
       --> then click on create condition
       --> Field name : When you highlight the field from avaliable fileds panel it will automaticall       
                                     appear here.
      --> Comparison :  give the condition from the drop down list. 
       --> Value(s)      :   give some value ( Like we give in the parameter default expression in                   
                                                                      general reports) 
       --> Check the is prompt ( This is used for popping up the filters) 
    * In this way we can add as many filters(input controls=parameters) to the report before we 
        design the report itself.
    * We can add the filters in the middle of the designing of the report also. 
    * We can also change, delete, add/update the filters and corresponding conditions. 
In our example I have given two filters. They are : i) gender ii) store_country 

1st filter : 
Field Name : gender
Comparison : Equals 
Value(s) : M

2nd  filter : 
Field Name : store_country
Comparison : Equals 
Value(s) : USA

* Click on next
* Give the name for the report and location to save the report.  

Step 3 :

When you click on finish you are ready to design your report....

* You can observe the tables and fields available within the domain in Domains Window
* From there you can drag and drop the fields to the details band of the design area. 
* The complete design of the report you can find in the below image.

That's it we are done with report ... A domain report.. 

* When we designing domain report , the iReport engine generates domain query language on it's own( defaultly) 
* This you can observe at Report Query Area.
* Find the image below for our example

* We have not provided any SQL query to generate the report. 
* We have not added any parameters in the Report Inspector of Parameters section.
* But the iRport engine has provided the domain query and parameters from jasperserver. 
* When we work with domain based Reports from iReport these all things done by iReport Engine.

How to see the preview of the report in Report Designer ?
We can directly preview the output in the designer by clicking on preview tab
You need to export this report to jasperserver and have to see the preview as follows.

* Right click on the Folder-> click on Run JasperServer Report
* You can see the output(preview) in new a new window(Report Viewer Window)

How to see the preview of the report in the JasperServer ?
upload the report to the server and see the preview. 
The filter which we gave at the time of report design become input controls in the server and looks like in the following image. 

Now click on Ok and see the preview. 

We are done with simple domain based report from iReport.

Welcome with any queries at sadakar@gmail.com or in this blog.

Thanks for reading this article :)

Creating Domain in JasperServer

What are Domains ?

* A Domain is a metadata layer that provides a business view of the data accessed through a data source.
* A Domain presents the data in business terms appropriate to your audience, and can limit the access to
data based on the security permissions of the person running the report.
* A Domain defined in JasperReports Server can be used to create reports, Ad Hoc views, and Domain Topics.

Tutorial to create a domain :

What is metadata ?

* The term metadata refers to "data about data".
* Metadata is data about data.
* It is descriptive information about a particular data set, object, or resource, including how it is formatted, and when and by whom it was collected.
* Although metadata most commonly refers to web resources, it can be about either physical or electronic resources.
* It may be created automatically using software or entered by hand.

More Information about metadata.

* We generally create database in SQL clients such as mySQL, SQL, postgreSQL and etc.
* For example :

Our database looks like :

sample database
and etc where A, B, C,D,.... etc are fields in the respective tables.

* We can say metadata or Domain from the above sample database as follows.
i.e, which ever tables and the fields in the tables we want to use in reporting and/or in analysis we separate them as a mini database which is nothing but domain or metadata.

* While creating metadata we may use some conditions, joins, SQL operations, security things and etc.

Tuesday, May 14, 2013

Date Parameters in iReport Designer OR Date Input Controls in JasperServer Repository

Generally we deal with date input controls in our reports...

Here is the stuff related to date input controls..

* Parameters in iReport Designer are called as input controls in JasperServer Repository.
* Generally we use start_date and end_date (OR) from_date and to_date.


Give the properties as shown in the figure:
Name: start_date
Parameter Class : java.util.Date
Use as a prompt : Check this box
Default Value Expression : new SimpleDateFormat("yyyy-MM-dd").format(new Date().getTime() - 7 * 24 * 60 * 60 * 1000)
Description : Write your description
Properties : Add properties if you want any in your report parameter.

NOTE:  In Default Value Expression we are taking the start date as one week back from the current date. i.e, from current date to 7 days back.


* As shown in figure we are giving Default Value Expression as Current date. i.e, new Date() function brings out current date(i.e, todays date).

* The names of the parameters and ID's of the input controls in jasperServer repository should match one another.
  i.e, parameter names in Report Designer = ID's in input controls  of JasperServer Repository 

Useful other posts:

Thanks ...
Have fun with date input controls :) :) :)

Monday, May 06, 2013

Using Excel or CSV files as Data Source to Create Reports

It's a one year journey of using every time datasource type as "Database JDBC Connection"... On the way I just found a hut to take some rest and to have some fun. At the time my mind was thinking that why we have to go every time in a traditional way.. The moment I thought I was very eager to find some other alternative.. Today worked out on it.

I'm using : iReport Professional 5.0.0(Trail version) and Jasper Server CE(5.0.0).
Create an excell file before starting to work out this.
I've taken a sample data like


Step 1 : Spreadsheet as datasource :
Click on datasource icon... Click on new .. select either of the options

i) Microsoft Excel(xls) data source (OR)[This is for older verisons of excell 98,2000 )
ii) Microsoft Excel 2007(xlsx) data source [This is for 2007.. didn't studied about 2010)
we are using 2007(xlsx) as our environment having 2007 excell software.
Find the picture below.

now click on Next
It'll open anthoer window.
and give the details that has shown in it.

Name :sample_ xlsx2007
Excell file :C:\Users\helical002\Desktop\xlsx datasource.xlsx
click on Get Columns name from the first row of the file
You can see the list of column Name and Column Index as shown in the figure.
* Right click on reportname in the report insepctor then click on Query Editor
* Click on Excell Datasource  tab
* Click on getfields from datasource
Click on OK.

Step 4:
Come back to the desing area of iReport.
Drag and drop the fields that you want to use.
Save the file and and preview the output.
The final output looks something like this
We are done with the report.

NOTE: The same procedure we need to apply for CSV(Comma Separated Values) files to use as a datasource.

Thanks for reading this document.
Queries : sadakar.1988@gmail.com
:) :) :)

Your smile makes you more stronger...

Giving Hyperlink to a text field, Row banding in iReport

Sometimes simple things might take lots of time....

Here is the tutorial .....
I'm using the following versions : iReport-Pro .5.0, Japserserver CE, Database : foodmart database in postgre SQL.
I'm using two reports
i) hyperlink
I'm gonna show how we can give hyperlink in one report and how we will navigate to another report.
And How to give row banding in iReport?

In Report 1:
Step 1: Connect to database (Click on database icon and give all the details and test it )
Step 2: delete all the not needed bands(If we don't delete it leads to page gaps in the output)
Step 3: Write a simple query in Report Query area ( Just right click on report name in the
              Report Inspector then Select the query)
             I've given like this : select 1 as One
Step 4: The bands I'm interested to take are : Column header and Detail bands respectively.
Step 5: Drag and drop the field to detail area And drag and drop a text field from palette to
            column header.
The below image gives somehow idea.

Step 6: Giving Hyperlink to text field
i) Write text in double quotes like : "Hyper link".( Text field should take double quotes whereas static text field doesn't take double quotes and doesn't applicable to create hyperlink)
ii) Right Click on the text field ->Click on Hyperlink-> It opens a window
    see the sample in the following image

iii) Give Hyperlink target as : Blank
      Hyperlink type as : ReportExecution
iv) Click on Link Parameters.. Give  Link parameter name as : _report
                                                    Parameter Class Name as : java.lang.String
                                                  Value Expression : location/Path of the second report in the server
       for example the path is : "/reports/Demos_Sadakar/HyperLinkDemo/Hyperlink_Report2"
We can observe the location in the below image

NOTE : Remember that Hyperlink_Report2 in the URL is the ID of the jasperReport when we give at the time uploading report to server.. i.e, it is not the name of the Report.

Step 7: Now save the report and upload to the server(Hope we know how to upload a report to server. Please find how to upload a report to server in the list of posts of my blog)

In Report 2:( A normal report with row banding functionality)
Step 1: Create a report and write query in query area
              (Give same database details.. foodmart details)
             I have taken this query : select * from employee
Step 2: Just drag and drop fields from Fields node of Report Insepector to Detail band.
              ( dragging only few fields)
Step 3: Now we are working on row banding.
i) Go to Report Inspector panel
ii) Right click on  Style option and Add some style.. I've given default name as style1
iii)Right click on style1 and select Add Conditional Style
iv) Now double click on it and i'll open Expression Editor.. there we need to give the follwoing condition
     new Boolean($V{REPORT_COUNT}.intValue() % 2 == 0)
 In the above expression $V{REPORT_COUNT} is the default variable provided by jasperians in iReport. Check variables in the Report Insepctor.

v) Go to the properties of Conditional Style expression it opens it's poperties( not click on Style1.. click on the conditional thing). In the properties window select give your favourite BackColor.
I'm showing the scenario in an image.
NOTE: do not check Opaque check box. just leave it.

Step 4:  Now select the fields you want to apply for the fields. I've selected all the fields that I've dragged to the Detail band. You can observe the multiple Objects properties window on the right side.
From the properties go to style and give the style1( just click on it it'll show all the created styles.. In our example we have created only one style i.e, style1)

That's it. Now save the report and see the output of the report in the server . It'd look like the following

Execution of the Reports using hyperlink- Final Output.
NOTE : When we create an hyperlink and if it works fine then the color of the text changed to light blue and it'll have underscore on mouseover.

First Report Output:

 Now click on "Hyper Link" that we are seeing on..
It'll redirect to another page which we call it as hyperlink functionality in iReport.

:) :)
Thanks for your time to read this tutorial :) :) :)
for any queries :  sadakar.1988@gmail.com