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

Tuesday, December 16, 2014

Tip : Parameter with wildcard % in WHERE clause LIKE statement - Jasper Studio / iReport


This tip is useful when you use wild card characters in LIKE statement of your WHERE clasue

Example 1 : 
General SQL Query : 
SELECT *  FROM customer WHERE education LIKE '%'||'School'||' %' 
Parametrized query in iReport/Studio 
SELECT *  FROM customer where education like '%'||'$P!{Parameter1}'||' %'




Example 2 :
General SQL Query : 
SELECT *  FROM customer WHERE education LIKE '%'||'Partial'||' %'
Parametrized query in iReport/Studio 
SELECT *  FROM customer where education like '%'||'$P!{Parameter1}'||' %' 

Why $P!{} parameter instead $P{} parameter ?
Because when you use wildcards in SQL query you need to combine the parts of the actual strings.
In above example   % param %  takes part1 of String + YourEntry + part2 of String (parts could be optional).. So when you enter your entry the parameter in Studio doesn't understand.. It treats each character of your entry as separate words hence you must use multi select parameter syntax ( i.e., $P!{}) 

https://community.jaspersoft.com/questions/525243/passing-wildcards-parameter-statement

Sadakar Pochampalli
:-)

Monday, December 15, 2014

Just looked once again & summarized -- Overview of JasperSoft : 15th Dec 2014


JasperSoft is one of the commercial open Source Business Intelligence suite in the world which 
is finding place in Gartner's  Magic Quadrant over the last few years & recently acquired by TIBCO.

Organizations want decision making in it's business timely. 
No matter what's organizations mix of platforms,applications, infrastructure that they use,
Jaspersoft provides easily deploy in any environment with less cost of licensing with its modular, 
scalable & standards-based architecture.

Using Jaspersoft BI suite, we can deliver End to End BI Solutions starting 
from ETL to Visualization of data.

Core Benefits using Jaspersoft BI suite are : 
1. Pixel perfect reporting, dash boarding and in-memory analysis using ad-hoc editor.
2. Embed into third party applications with it's flexible web-based architecture.
3. Subscription Model - i.e., Allows more users at substantially which reduces the cost. 
JasperReports Server is the heart of the Jaspersoft BI Suite and the suite contains below tool set.
JasperETL,Jasper Studio or iReport, Jasper Reports Library, Jasper Schema Workbench, Visualize.js

JasperETL : (Extract, Transform, Load)
It is used to extract the data from operational/transactional system to create a warehouse 
to perform reporting & analysis.  

Jaspersoft Studio : 
Reporting - The process of organizing data into informational summaries.
Jasper Studio - It is an eclipse-based report designer for JasperReports and JasperReports server which allows 
you to create pixel perfect reports containing
charts, images, tables, sub reports which allows you to publish the reports in the form of pdf, word, csv and etc. 

Jasper Schema Workbench & Self-service BI capability:
A desktop stand alone tool to build muti-dimensional model to perform OLAP operations in Ad-hoc editor of jasper server. 
The powerful in-memory analytic engine in jaspersever uses domains created from traditional databases or virtual data sources to perform 
enterprise self-service BI solutions. 

JasperReports Library : 
It is a Java Library that offers an interface to the JasperReports Library reporting engine.
This library is the engine at the core of the iReport Designer, Jaspersoft Studio, and JasperReports Server. 

Visualize.js : 
It is a JavaScript framework(Jasper Server programming language) used to embed data visualizations in the form of reports,dashboards
inside third party web applications & supportable in all kind of devices like mobile, iPad, laptop and desktop. 

BigData Analytics: 
Using jasper we can also connect to big data technologies such as Hadoop , MongoDB, 
Cassandra to develop reports, dashboards and perform analytic's directly.
columnar-based in-memory engine allows analysis of Big Data.

For more details visit 

Friday, December 12, 2014

Maintaining Jasper Community Server Repository in DEV environment for Different Users for their individual Reporting work.


Maintaining Jasper Community Server Repository in DEV environment for Different Users for their individual Reporting work. 

Login into the Server with Admin privileges(As this documentation belongs to community the default username & password for 5.6 CE community is jasperadmin/jasperadmin).

1) Go to Manage section & click on Users. 

 

2) In the pop up detailed box enter the details of user as shown in below image. Let’s  assume use-1 details are 
User name = User1
Password =User1




 

3) Repeat the same for another user. Lets say the user name and password are 
User Name = User2
    Password =User2

4) Lets create 2 folders say Folder1 and Folder2

Default Folder Structure in 5.6 CE Server


 

After creating 2 folders the structure should look like below

 

5) Scenario : 
a) When the User1 logs into the Server he/she should be able to access only Folder1 but not Folder2 
b) When the User2 logs into the Server he/she should be able to access only Folder2 but not Folder1
c) When Admin logs into the server he/she can able access and have the control over all the folders & repository. 


User1 Scenario : 
1) You can do all this if you are logged into jasper server as administrator. 
2) Change the permissions for the all the folders. 
3) Lets display only Folder1 & Reports folders when User1 logs into the server. 
4) For doing the above you need to change the permissions for all the folders.

You need to do below for folders which you do not want to show for users
a) Lets take a folder “Analysis components” 
b) Right Click it -> Go to permissions by default it would display Role details. 
c) For ROLE_USER give permission as No Access and Click on Apply button tehn click on OK button(By default it shows Read Only).  

Default Permission is as below. 

After Changing the permission it should be as follows
 
e) Repeat the same for all the unwanted folders for the users. 
      i.e., ROLE_USER value should be ‘No Access” for the folders which you do  not want to show in the repository. 

5) Folder 1 
a) Role permission for ROLE_USER = No Access
b) User permission operation is User1 = Read + Write + Delete

 


 
6)  Folder 2
a) Role permission for ROLE_USER = No Access
b) User permission operation is User2 = Read + Write + Delete
(Images are not shown for this step - similar to step 5 images).

7) 
a) Login with User1 credentials (Username= User1 Password=User1)
b) Now you can able to see only the Folder1 and Reports Folder for User1


8) 
c) Login with User2 credentials (Username= User2 Password=User2)
d) Now you can able to see only the Folder2 and Reports Folder for User1.


 
I hope this helps some one who maintaining the jasper community server.

Monday, December 08, 2014

Tip : Print Column Headers only to the 1st page of the report in Jasper iReport/Studio (By default column headers will print


Steps
1) Select individual Text box of column Header and go to the properties.
2) Write $V{PAGE_NUMBER}==1 expression for PrintWhenExpression property.
3) Repeat the same for all the text boxes and save your report and preview.


Sample Images :




Sample output:
Image 1 :



Image 2:





Wednesday, December 03, 2014

Tip : Importance of ORDER BY clause of sql query for grouped bands in Jasper iReport


Copy paste below XML code in your iReport/Studio and observe how 2 groups are added and and how the variables created for summing up a field(StoreSales) value. 

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report2" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="35b2d5f2-2cd9-4c77-8617-4c7e643bb063">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <queryString>
        <![CDATA[SELECT     DISTINCT c.country, c.state_province,c.city,
SUM(sf7.store_sales) AS StoreSales
FROM customer c INNER JOIN sales_fact_1997 sf7 ON sf7.customer_id=c.customer_id
GROUP BY c.country,c.state_province, c.city
ORDER BY c.state_province]]>
    </queryString>
    <field name="country" class="java.lang.String"/>
    <field name="state_province" class="java.lang.String"/>
    <field name="city" class="java.lang.String"/>
    <field name="storesales" class="java.math.BigDecimal"/>
    <variable name="storesales_1" class="java.math.BigDecimal" resetType="Group" resetGroup="Country" calculation="Sum">
        <variableExpression><![CDATA[$F{storesales}]]></variableExpression>
    </variable>
    <variable name="storesales_2" class="java.math.BigDecimal" resetType="Group" resetGroup="State" calculation="Sum">
        <variableExpression><![CDATA[$F{storesales}]]></variableExpression>
    </variable>
    <group name="Country">
        <groupExpression><![CDATA[$F{country}]]></groupExpression>
        <groupHeader>
            <band height="21">
                <textField>
                    <reportElement x="0" y="1" width="106" height="20" uuid="2db194d8-11f2-45b5-aca0-59b2eb8a33ac"/>
                    <textElement>
                        <font size="14" isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{country}]]></textFieldExpression>
                </textField>
                <textField evaluationTime="Group" evaluationGroup="Country">
                    <reportElement x="104" y="1" width="100" height="20" uuid="8f2c109e-3f20-4cd6-bd6d-47612999d0dc"/>
                    <textFieldExpression><![CDATA[$V{storesales_1}]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
    </group>
    <group name="State">
        <groupExpression><![CDATA[$F{state_province}]]></groupExpression>
        <groupHeader>
            <band height="21">
                <textField>
                    <reportElement x="0" y="1" width="48" height="20" uuid="4b6cc68b-843a-465f-9690-017e1a9a975b"/>
                    <textElement>
                        <font size="12" isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{state_province}]]></textFieldExpression>
                </textField>
                <textField evaluationTime="Group" evaluationGroup="State">
                    <reportElement x="48" y="1" width="100" height="20" uuid="e5f8bc83-c7fa-498c-ab32-9be5671746ab"/>
                    <textFieldExpression><![CDATA[$V{storesales_2}]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
    </group>
    <title>
        <band height="34" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement x="4" y="0" width="100" height="20" uuid="6b6244a4-876b-4590-bb17-62e22c601c24"/>
                <text><![CDATA[country]]></text>
            </staticText>
            <staticText>
                <reportElement x="104" y="0" width="100" height="20" uuid="075e9518-1677-4852-a366-65525755d240"/>
                <text><![CDATA[state_province]]></text>
            </staticText>
            <staticText>
                <reportElement x="204" y="0" width="100" height="20" uuid="d5542f7e-cf62-4278-883c-f2dbd8b5de4f"/>
                <text><![CDATA[city]]></text>
            </staticText>
            <staticText>
                <reportElement x="304" y="0" width="100" height="20" uuid="de08318f-91ad-415e-b91c-b2c98ef59ccf"/>
                <text><![CDATA[storesales]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="4" y="0" width="100" height="20" uuid="ca601a6c-c26a-4618-9068-1807aad3e660"/>
                <textFieldExpression><![CDATA[$F{country}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="104" y="0" width="100" height="20" uuid="ac18ba4e-205f-4ed8-ac74-0cc169a3c07d"/>
                <textFieldExpression><![CDATA[$F{state_province}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="204" y="0" width="100" height="20" uuid="d5216c78-2e4c-432a-b9b6-b9178b7d8548"/>
                <textFieldExpression><![CDATA[$F{city}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="304" y="0" width="100" height="20" uuid="5cb1bb39-1728-4fb8-b041-4f8a167146b3"/>
                <textFieldExpression><![CDATA[$F{storesales}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

NOTES : 
1) Make sure of adding order by clause with the filed you want to give in group by expression if you are getting only one value or repeating values.
2) One of making non reputation of values is to use sub queries.

Reference : 

http://stackoverflow.com/questions/14450634/grouping-records-in-jasperreports
:-) 

Tuesday, December 02, 2014

Tip : Border to a report in Jasper iReport or Japser Studio - 5.6

Today, I came across a simple requirement to draw border to a report in Jasper iReport/Studio - (5.6) and this small tip may help you to save your time. :-)

1) Add Background band to the report ( By default the size of the band is zero(0)) set the height adjustable ( for example : 812 px).

2) Drag and drop fields to Detail band and give borders to the field cells (0.25 px)

3) Take "Frame" element in Background band and give borders to it (again 0.25px) and give same height as band as in step 1.

4) Save the report & preview the output. ( Tested in Internal Preview, not pdf or word , if you see the output in any of them you may not find the borders as you might need to set over lap properties - find it and set the property for it. ).

Sample JRXML file : Click me to Download.

Sample Design Image :

Sample Output Image : 

References : 
1) http://stackoverflow.com/questions/7193235/how-to-draw-a-border-around-both-the-column-header-footer-and-detail-sections

2) http://stackoverflow.com/questions/9311959/detail-band-with-borders-in-ireport