Wednesday 17 December 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 15 December 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 12 December 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 8 December 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:





Thursday 4 December 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 2 December 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

Sunday 30 November 2014

Tuesday 28 October 2014

Tip : Multi Select Query Integer Parameter in Japser iReport/Studio

Tip :  Multi Select Query Integer Parameter 
Update 1 : 16th March 2014

We can use java.util.Collection for Integers in multiselect parameter for this we need to add below code explained in thread from community

<parameter name="paramAbc_Id" class="java.util.Collection" nestedType="java.lang.Integer">             <defaultValueExpression><![CDATA[ ]]></defaultValueExpression> </parameter>  

http://community.jaspersoft.com/questions/543981/send-integer-list-values-parameter

Generally we take string values for multi select input controls in jasper iReport/studio and we take java.util.Collection for the input parameter.

Jasper iReport doesn't allow you take Integer values for multi select using java.util.Collection for parameter.

i.e, we should give java.lang.Integer for parameter though we are taking it for multi select input control.

( A multi select is a collection of values , could be integers or could be strings).

From 4.7 and greater versions of Jasper iReport multi select input control allows java.lang.Integer in place of java.lang.Collection for parameter.


Example :  Correct Way :

Main Query : 
SELECT * from customer where $X{IN,customer_id,param_customerID}

Parameter : 
Name : param_customerID
Class : java.lang.Integer

Parameter Query in the repository : 
Type : Multi Select Query
Query :
SELECT DISTINCT
        customer_id FROM customer
ORDER BY customer_id


Example : Wrong Way :
Main Query : 
SELECT * from customer where $X{IN,CAST(customer_id AS char),param_customerID}

Parameter : 
Name : param_customerID
Class : java.lang.Collection

Parameter Query in the repository : 
Type : Multi Select Query
Query :
SELECT DISTINCT
        CAST(customer_id AS char)  FROM customer
ORDER BY customer_id

:-)

Example JRXML
1) Copy below code in a report JRXML completely.
2) Create parameter in repository and test it.


<?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="report1" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="6a7ac7e7-2d64-447b-9aba-54346ff9b8b4">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <parameter name="param_customerID" class="java.lang.Integer"/>
    <queryString>
        <![CDATA[SELECT * from customer where $X{IN,customer_id,param_customerID}]]>
    </queryString>
    <field name="customer_id" class="java.lang.Integer"/>
    <field name="account_num" class="java.lang.Long"/>
    <field name="lname" class="java.lang.String"/>
    <field name="fname" class="java.lang.String"/>
    <field name="mi" class="java.lang.String"/>
    <field name="address1" class="java.lang.String"/>
    <field name="address2" class="java.lang.String"/>
    <field name="address3" class="java.lang.String"/>
    <field name="address4" class="java.lang.String"/>
    <field name="city" class="java.lang.String"/>
    <field name="state_province" class="java.lang.String"/>
    <field name="postal_code" class="java.lang.String"/>
    <field name="country" class="java.lang.String"/>
    <field name="customer_region_id" class="java.lang.Integer"/>
    <field name="phone1" class="java.lang.String"/>
    <field name="phone2" class="java.lang.String"/>
    <field name="birthdate" class="java.sql.Date"/>
    <field name="marital_status" class="java.lang.String"/>
    <field name="yearly_income" class="java.lang.String"/>
    <field name="gender" class="java.lang.String"/>
    <field name="total_children" class="java.lang.Integer"/>
    <field name="num_children_at_home" class="java.lang.Integer"/>
    <field name="education" class="java.lang.String"/>
    <field name="date_accnt_opened" class="java.sql.Date"/>
    <field name="member_card" class="java.lang.String"/>
    <field name="occupation" class="java.lang.String"/>
    <field name="houseowner" class="java.lang.String"/>
    <field name="num_cars_owned" class="java.lang.Integer"/>
    <field name="fullname" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="138" height="20" uuid="10e1bad7-7151-4d49-8001-141486634050"/>
                <text><![CDATA[customer_id]]></text>
            </staticText>
            <staticText>
                <reportElement x="138" y="0" width="138" height="20" uuid="8173c779-65a8-402d-86a6-6ae03478765d"/>
                <text><![CDATA[account_num]]></text>
            </staticText>
            <staticText>
                <reportElement x="276" y="0" width="138" height="20" uuid="e4d4715e-30c1-430a-a9c3-3968ffc7a9de"/>
                <text><![CDATA[lname]]></text>
            </staticText>
            <staticText>
                <reportElement x="414" y="0" width="138" height="20" uuid="e7e42982-dc47-4888-b4ca-7a9418281f1f"/>
                <text><![CDATA[fname]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="21" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="138" height="20" uuid="764a2921-621e-4940-8fda-e01805793bd4"/>
                <textFieldExpression><![CDATA[$F{customer_id}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="138" y="0" width="138" height="20" uuid="a7928dcb-a9b4-4291-b038-8c4bb422a446"/>
                <textFieldExpression><![CDATA[$F{account_num}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="276" y="0" width="138" height="20" uuid="308acd96-bbe5-4052-94d0-286b3864ee96"/>
                <textFieldExpression><![CDATA[$F{lname}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="414" y="0" width="138" height="20" uuid="84f8c767-812a-4ef1-8e72-65cfed6f2cac"/>
                <textFieldExpression><![CDATA[$F{fname}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>



Sample output image : 


References : 

https://community.jaspersoft.com/questions/800205/multi-select-47-seems-broken

https://community.jaspersoft.com/questions/823425/passing-values-multi-select-query-input-control-jasperserver


Thursday 9 October 2014

Tip : Disable secure storage password property in Jasper Studio..

Tip : Disable secure storage password property in Jasper Studio..

Detailed information is here :

http://community.jaspersoft.com/wiki/eclipse-secure-storage-jaspersoft-studio


Image-1 : It will popup once you give Data Adapter Details 



Image-2:  Where to disable the above window in Tool.

Go to  Window -> Preferences -> Jaspersoft Studio -> Global Settings ->

In the window uncheck Security Preferences i.e., Use the secure storage to save credentials


:-)


DBVisualizer installation issue : Could not read settings file /home/sadakar001/.dbvis/config70/dbvis.xml

Tip:  
   DBVisualizer start up problem

OS :
  Cent OS 6.5 final..

When you get the error shown in below figure , you  need to delete dbvis.xml file located at /home/sadakar001/.dbvis/config70

By default you may not have .dbvis folder under home directory.. Open the terminal and go the location manually.

You will find two files
1) dbvis.xml and
2) dbvis.xml.bak

Remove dbvis.xml from here and restart the DB Visualizer again.. What it will do at the time of loading it will create a new dbvis.xml (from where you deleted) ..

Now test the client tool, you will not get driver loading errors.





Resources :
http://www.dbvis.com/forum/thread.jspa?messageID=13273

Monday 29 September 2014

No markup processor factory specifyed for '' markup error in Jasper Studio pro 5.6.1


Solution is here :

https://107.23.147.174/questions/841827/jaspersoft-studio-no-markup-processor-factory-specified


Quick Solve : 

Go to the XML view and search for 'markup'. You will find something like this
<textElement textAlignment="Center" verticalAlignment="Middle" markup="">
 
Change it to this:
<textElement textAlignment="Center" verticalAlignment="Middle" markup="none">
 
 
:-) 

Wednesday 27 August 2014

mongoDB: Lesson2 : Creating Collection & Dropping Collection.

Hey Guys,
The fun continued here with mongodb next topic, creating collections(tables in RDBMS) and inserting documents(rows in table in RDBMS).



#
 Task
Syntax with Example




1




Collection








2




Creating Collection



Method -1 :


By using createCollection method
Using db.createCollection(name,options) function


Syntax: db.createCollection(<name>,<options>)

In the above syntax <name> is the name of the collection & options are various other parameters like capped, size, max and etc.(Refer official documentation for this).

Example 1: Just creating a collection Name

> db;
test

> db.createCollection("myFirstCollection");
{ "ok" : 1 }

> show collections;
myFirstCollection
nettuts
system.indexes
>

Example 2: Creating collection with along with document.

> db.mySecondCollection.save({"firstName":"sadakar","lastName":"pochampall",ID:200});
WriteResult({ "nInserted" : 1 })

> db.mySecondCollection.save({"firstName":"dolly","lastName":"boray",ID:201});
WriteResult({ "nInserted" : 1 })

> db.mySecondCollection.find();
{ "_id" : ObjectId("53fdbdbe268aa77e481c4a6c"), "firstName" : "sadakar", "lastName" : "pochampall", "ID" : 200 }
{ "_id" : ObjectId("53fdbde4268aa77e481c4a6d"), "firstName" : "dolly", "lastName" : "boray", "ID" : 201 }
>









3






Method -2:


By using insert method:
  • Insert method can be used in two ways.
    • Create documents separately and insert them into collection.


Create documents separately and insert them into collection.
Example:
> i={"firstName":"sadakar","lastName":"pochampalli",id:100};
{ "firstName" : "sadakar", "lastName" : "pochampalli", "id" : 100 }

> j={"firstName":"dolly","lastName":"boray",id:101};
{ "firstName" : "dolly", "lastName" : "boray", "id" : 101 }

> db.myFirstCollection.insert(i);
WriteResult({ "nInserted" : 1 })

> db.myFirstCollection.insert(j);
WriteResult({ "nInserted" : 1 })

> show collections;
myFirstCollection
nettuts
system.indexes

> db.myFirstCollection.find();
{ "_id" : ObjectId("53fdbaad268aa77e481c4a6a"), "firstName" : "sadakar", "lastName" : "pochampalli", "id" : 100 }
{ "_id" : ObjectId("53fdbab2268aa77e481c4a6b"), "firstName" : "dolly", "lastName" : "boray", "id" : 101 }
>







References :


http://docs.mongodb.org/manual/tutorial/getting-started/
http://docs.mongodb.org/manual/reference/method/db.createCollection/
http://docs.mongodb.org/manual/reference/method/db.collection.insert/





4

Drop Collection
Syntax: db.<CollectionName>.drop();


Example:
> db.myFirstCollection.drop();
true
>


:-)