Friday 29 May 2015

Classifications of Reports in Jaspersoft BI - Desktop reporting and Server side reporting

Hi folks,

This post will talk about the classifications & complexities involved in Jasper reports.

Hope readers of this post find something useful when proposing a report system to a customer.

1) Desktop Reports ( Types of reports can be developed using iReport or Jasper Studio ): 

1) Plain tabular reports
2) Cross tabular reports
3) Reports having sub reports
4) Graphical reports
5) Drill down reports
6) Majorly excel/pdf exported reports ( Tabular or Graphical with tabular).
7) Map reports
8) Widget reports
9) Domain based reports
10) Plain textual reports(text,images,html reports).

2) Server side Reports ( On-Fly reporting):
11) Ad-hoc reports(Domain/Topic based)
12) Ad-hoc reports(OLAP- Cubes based)
13) Legacy analysis reporting

Complexities of development:
1) Low complexity
2) Medium complexity
3) High complexity

Data sources & queries categorization:
RDBMS
1) Simple SQL with less joins
2) Complex SQL with more joins
3) Simple & complex stored procedures

Languages supported by Jaspersoft:
1) Groovy script
2) Java script
3) Java

----------------------------------------------------



Thursday 21 May 2015

Business Analyst best practices to follow while converting reports from other platform to Enterprise TIBCO Jasper soft platform.

Hello Analysts ...!!!!

This post will quickly gives idea for Business Analysts (and content writers) on converting enterprise Business Intelligence projects from other platforms to Enterprise TIBCOJaspersoft platform.


Questionnaires that usually comes in while talking about TIBCOJaspersoft BI platform. 

1) What is Enterprise TIBCOJaspersoft Business Intelligence ?

2) Where one can find information on TIBCOJaspersoft BI platform ?

3) What are the features of TIBCOJaspersoft Business Intelligence ?

4) How Enterprise TIBCOJaspersoft BI platform differ from other Enterprise BI platforms in the Market ?

5) What is the cost of TIBCOJaspersoft BI product(s) & official support ?

6) Is there any mechanism to migrate projects from other to jasper ?

7) What are the products & functionality of each tool product ?

8) How easy to convert reports/dashboards/analysis/analytics/ETL solutions from other plat form to TIBCOJaspersoft plat form ?

9)  Does TIBCOJaspersoft supports responsive visualizations ? (i.e., Mobile Intelligence)

10) Does TIBCOJaspersoft supports BigData technologies ?

There could be other questions that comes into mind.
(Share your question in comments section so that we can get to know more information to this article).


1) What is Enterprise TIBCOJaspersoft Business Intelligence ? 

Jaspersoft product has started as an opensource BI technology and it still in releases timely. Along with opensource edition(or community edition) it also offers it's enterprise edition with Advanced features over community edition like multi tenancy, OLAP analysis , ad-hoc reporting and etc. 


2) Where one can find information on TIBCOJaspersoft BI platform ?

One can visit the TIBCO jaspersoft official website at http://www.jaspersoft.com/ to know more about it. 
It also has community website & the url is : http://community.jaspersoft.com/


3) What are the features of TIBCOJaspersoft Business Intelligence ? 

TIBCOJaspersoft BI suite has below features 

a) Enterprise Reports 
b) Enterprise Dashboards
c) Enterprise Analysis Tools
d) Enterprise Ad-hoc reporting tools.
e) Enterprise Embedding Business Intelligence.
f) Enterprise security features. 
g) Enterprise Mobile BI. 


4) How Enterprise TIBCOJaspersoft BI platform differ from other Enterprise BI platforms in the Market ?

* Every organization/firm needs reports/dashboards for tracking its business day to day to take better decisions. 
* There are "N" number of Enterprise BI tools available in the market but why do we need to chose this means 

firstly , as it as an open source suite its enterprise edition  is cheaper than any other enterprise BI platform.

secondly, it has powerful enterprise features as explained in point-3 which you might not find in other enterprise BI suite. 

last but not the least, Jaspersoft is in very active stage with new technologies such as HTML-5, D-3 , BigData and in it's every new release one can find these type of features as how business intelligence is moving with data world. 



5) What is the cost of TIBCOJaspersoft BI product(s) & official support ?

One must contact TIBCOJaspersoft to know pricing.
Contact link : http://www.jaspersoft.com/contact-us 

Costing differ from edition to edition and number of licenses 

( Editions of Jaspersoft : Community, Reporting, AWS(Amazon Web Services), Professional & Enterprise). 


6) Is there any mechanism to migrate projects from other to jasper ? 

No, there is no a mechanism to migrate projects from other to Jasper. 

Redesign / Re writing Jaspersoft realted code to convert from other platform to Jspersoft is required. 

For example : A report developed in Pentaho or Crystal can not be loaded in Jaspersoft. We have to develop the fresh report by taking the previous platform as a reference. 


7) What are the products & functionality of each product ? 

Jaspersoft offers all the BI implementation community and enterprise products .
They are 

1) Jaspersoft Professional Design Studio - For Reports Development 
2) Jaspersoft Enterprise Business Intelligence Server 
3) Jaspersoft ETL - For Data Integration & data warehousing services 
4) Jaspersoft Schema work bench - For OLAP functionality 
5) Jaspersoft libraries - For third party intelligence of Jasper reports. 
6) Jaspersoft Visualize.js - For Embedding BI
7) Jaspersoft Mobile BI Server. - For Mobile Intelligence 


8) How easy to convert reports/dashboards/analysis/analytics/ETL solutions from other plat form to TIBCOJaspersoft plat form ? 

* Based on project size it can be determined .
* Example : 
Assume one needs to convert reports from "X" platform to Jspersoft paltform . 

i) Classify the reports into low, medium & complex or high complex. 
    a) low level : plain reports with minimum functionality.
    b) Medium level : Graphical Reports with parameters & report level calculations. 
    c) Complex level : a) + b) with lots of report components in it and features like drill downs, drill 
                                   backs and etc. 

ii) Time Estimation : 
    a) plain reports conversion : 10 to 20 hours
    b) medium level reports conversion : 20 to 40 hours
    c) complex level reports conversion : 40 to 100 hours ( might vary). 

iii) Resource allocation : 
Again this depends on project size and dedicated Jaspesoft BI professionals. 

Example : Conversion of 10 medium level reports 

A minimum of 25 business days and a maximum of 50 business days is required to convert 10 medium level enterprise  reports with 1 Jaspersoft BI professional. 
     

9)  Does TIBCOJaspersoft supports responsive visualizations ? (i.e., Mobile Intelligence)
Yes, it supports from 5.6 version.
One can see the responsiveness of Jasper solutions in Mobile /ipad/laptop or desktop.

10) Does TIBCOJaspersoft supports BigData technologies ?
Yes, it supports bigData technologies.
Examples : One can have the reporting system with MogoDB.


Jasper soft Official Support portal : 
Once you have the product in hand, you can have official jaspersoft support at
https://support.jaspersoft.com/user/login?destination=support-my

One can request for
a) Threads related to development of BI solutions.
b) Training program
c) license information
d) Enterprise editions downloads and etc.


I believe, I have covered at least minimum points & have missed few..

Readers of this post encouraged to add your findings in comments box and let the community know ..

Thank you for reading this article.


- Sadakar Pochampalli (21st May 2015)

Monday 11 May 2015

Tip : Change port number for postgresql server installed with Jasper Server (CE or EE)


What will happen if you install two professional versions of Jasperserver in your local machine.

Let say 5.6 and 6.0.1 and first installed version is 5.6 & later installed version is 6.0.1

First server takes 5432 as port for postgres and 8080 for tomcat and the next server takes 5433 and 8080(sometimes 8081) ports respectively for postgres and tomcat.

If want to make the 6.0.1 Jaspers postgres to make use 5432 as default one you need to change the port number in postgresql.conf file.

File location : C:\Jaspersoft\jasperreports-server-6.0.1\postgresql\data

Once the changes done, restart of japsers postgresql is required.

Find the below image :



Saturday 9 May 2015

Tip : custom pattern for decimal values for a field in Jasper iReport 5.x or Jasper Design Studio 6.x - Useful when exporting the report to Excel

This post will give you the tip of handling custom pattern for decimal values for a field values in Jasper reports at report level.

Even Though, you can handle it from SQL level some time we may not get handy with SQL for quick development of reports.

Problem Statement : 
If the decimal field value is 45.8900 then get the value as 45.890 (###0.###)
If the decimal field value is 5.0000 or 0.0000 then get it as 5 or 0 (###0)
The output pattern should be same on Jasperserver page output and when export to excel. 


USE CASE EXAMPLE : 

Let us assume your result of SQL query is as below

Sales
0.0000
23.9300
0.0000
5.0000
5.0000
6.9300
8.9400
9.1950

Final output in on Jasperserver web page & when export to Excel should be as follows. 

Sales
0
23.430
0
5
5
6.930
8.940
9.195

Without providing any pattern on filed you will get output as below 
On Jasper Server page output
0.0000
23.9300
0.0000
5.0000
5.0000
6.9300
8.9400
9.1950

When export to Excel ( You will get dots if the value is 0 or 5 and this should be avoided)
.
23.93
.
5.
5.
6.93
8.94
9.195


If you apply default pattern available on filed then the output on Jasperserver page and in Excel export would be as below 
pattern applied on field value is : ###0.000

0.000
23.930
0.000
5.000
5.000
6.930
8.940
9.195

Now, How to overcome the situation to get the output as shown below in both Jasperserver page and in excel export. 

0
23.430
0
5
5
6.930
8.940
9.195

Solution : 
Select the text field and in it's expression editor write expression as below

($F{Sales}-(int)$F{Sales}!=0)?new DecimalFormat("####0.000").format($F{Sales}):new DecimalFormat("####0").format($F{Sales}) 


General Example 
---------------------------------------------------------------------
double d = 77.7;
if((d-(int)d)!=0)
    
double d = 77.7;
if((d-(int)d)!=0)
    System.out.println("decimal value is there");
else
    System.out.println("decimal value is not there");
----------------------------------------------------------------------

That's all we have  to write custom pattern for decimal values. If the value is like 5.0000 then ignore all zeros and get only 5 and if the value is like 54.8970 then get get 54.897

I hope this helps someone :-)

Did you find it helpful ? Hit the g+ share button and let us spread it for community.

References:
http://stackoverflow.com/questions/15963895/how-to-check-if-a-double-value-has-no-decimal-part

Cheers..!!!

- Sadakar Pochampalli















Friday 8 May 2015

Actian Pervasive database connection in Jasper iReport 5.6 / iReport Pro 5.6

This post will give you quick overview on installing Actain Pervasive database drivers in iReport 5.x version

1) In iReport 5.x , Go to "Tools" then "Options" then Click On Classpath
2) Locate all the 3 drivers and check them.
3) Go to your database connections window and provide the driver class, jdbc URL & username, password.

Quick Images : 
1) Drivers Installation 

2) Database connection 



Visit below links to download the pervasive database drivers
http://www.pervasive.com/database/Home/Products/PSQLv11.aspx
http://www.dbschema.com/pervasive-jdbc-driver.html


Other way to install Pervasive drivers explained
Follow the steps explained in below link :
http://community.jaspersoft.com/wiki/connecting-ireport-pervasive-datasource

Do you find it useful ? Click g+ and share to community.

Thank you. 

Friday 1 May 2015

Limit number of rows on Table component at report level in Jasper Studio 6.x or iReport - usage of REPORT_MAX_COUNT parameter for table

This post will guide you on below things
1) How to limit the number of rows on table component at report level.
2) How to work with REPORT_MAX_COUNT
3) How to export Excel output to two tabs(sheets)

USE CASE : 
Data set(SQL) returns N number of rows. Display top 10 rows in one sheet & All rows(including top 10 rows) in another sheet.

BENEFITS
1) Used 1 SQL query on two table components.
2) Limiting the rows to display on table components at report level ( not in SQL level).
3) Increase the performance of report (SQL query hits db only once)
4) Save time & provide value added solutions to client.

Solution Steps: 

1)Take two groups (or summary band) to keep two table components & create a dataset.
  (Use page break & ignore pagination property checked).
2) Make these two table components to use the created dataset.
3) Jump into the report XML code and write the code for first table component.
i.e., (Search for 1st table component code in XML editor)

<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">

<datasetRun subDataset="E2E Inventory" uuid="f7d1fb44-409b-4dee-a200-e78f49ed1bbf">

<datasetParameter name="REPORT_MAX_COUNT">
     <datasetParameterExpression><![CDATA[new Integer(10)]]>          </datasetParameterExpression>
</datasetParameter>

<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]>  </connectionExpression>
</datasetRun>

4) Save the report & preview it on server then export to Excel. (use excel related properties to export into multiple sheets with it's names).


Excel Export properties (Add below properties to report - right click report & find properties).
net.sf.jasperreports.export.xls.sheet.names.1 Top 10 Rows
net.sf.jasperreports.export.xls.sheet.names.2 All Items
net.sf.jasperreports.page.break.no.pagination apply
net.sf.jasperreports.export.xls.ignore.cell.border       false

IMP NOTE : 
The procedure is useful when you do not have any calculated fields on table component.
Click here to find another post on limiting number of rows on table component with calculated fileds.

limit the number of rows after adding a calculated field on table component & remove all empty cells

USE CASE : 
Find a percentage field on table component and limit the rows to top 10
i.e., There is a field "A" of Double type then find the percentage field of A and then limit to top 10.
% filed = A/Total_Of_A

In this case we can't apply REPORT_MAX_COUNT parameter to table & to it's dataset.(b'z we have a calculated field of percentage .. The summation variable "Total_Of_A" adding all the values of A but not only 10 values.

Here are the solution steps. 
1) Take a variable(Total_Of_Field_A)and find the summation of column A.
2) Add a new cell(header, detail and footer cells) to the table component and take a text field in Detail cell and in it's expression write A/$V{Total_Of_Field_A}
3) The above calculation returns the calculated filed on table component.
4) Now select all the fields and go to print when expression commonly and write $V{REPORT_COUNT}<11 which will returns only top 10 rows.
5) Issue : Though you get top 10 rows you will be finding all empty cells of whole table data.
6) Remove border for all the cells. ( i.e., to the Column cells in Detail band of table ) and provide border only to text fields.

In this way one can easily write report limiting to 10 rows using table component at report level.

References :
1) http://community.jaspersoft.com/sites/all/libraries/pdf.js/web/viewer.html?file=http://community.jaspersoft.com/system/files/documentation/jasperreports-ultimate-guide-3v2.pdf#page=75

2) http://community.jaspersoft.com/wiki/jasperreports-built-parameter-reportmaxcount-and-interactive-jive-sort-feature


If you like the article click on g+ button  :-)

Cheers..!!!