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, October 28, 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, October 09, 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