Friday 30 October 2015

Replace special character with empty space or dot in a given string(user names) usign regular expressions in Jasper Reports.

Hi Guys,

Hope this helps !






<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.1.final using JasperReports Library version 6.0.0  -->
<!-- 2015-10-30T01:53:37 -->
<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="Blank_A4_11" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="61a0c24d-0d01-45c8-9798-7b1e375005ef">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <parameter name="Parameter1" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["sadakar.pochampalli@xyz.com__123"]]></defaultValueExpression>
    </parameter>
    <parameter name="Parameter2" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[$P{Parameter1}.toString().replaceAll("[\\W]", ".").replaceAll("_",".")]]></defaultValueExpression>
    </parameter>

    <queryString>
        <![CDATA[]]>
    </queryString>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="70" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="320" height="30" uuid="f65d6201-4a54-4f04-9540-0d7b1b061bc0"/>
                <textFieldExpression><![CDATA[$P{Parameter1}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="40" width="320" height="30" uuid="11d43e9d-990e-490c-95b3-faa86c13f52f"/>
                <textFieldExpression><![CDATA[$P{Parameter2}]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="61" splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="125" splitType="Stretch"/>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

Wednesday 14 October 2015

TIP : Get Prvious and Next Months month Names in Jasper Reports

Hi guys,


# Add below code to JRXML <import value="org.apache.commons.lang.time.*"/>
# Take a parameter lets say "Date" and its type as "java.util.Date"
# Default value expression : new Date()

# D



JRXML (Works only in Professional Jasper Studio )

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.1.0.final using JasperReports Library version 6.1.0  -->
<!-- 2015-10-14T15:27:28 -->
<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="Get Prvious and Next Months month Names" pageWidth="590" pageHeight="842" columnWidth="550" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="586bcdf6-d4c0-4e91-b47f-552a90f90619">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <property name="com.jaspersoft.studio.unit." value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>
    <import value="org.apache.commons.lang.time.*"/>
    <parameter name="Date" class="java.util.Date">
        <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="150" splitType="Stretch">
            <textField>
                <reportElement mode="Opaque" x="160" y="30" width="390" height="30" backcolor="#EDF7FF" uuid="e65ee664-6d00-4776-8a31-d2014241ebec"/>
                <box leftPadding="10">
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement verticalAlignment="Middle">
                    <font fontName="Calibri" size="12"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{Date}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement mode="Opaque" x="160" y="60" width="390" height="30" backcolor="#EDF7FF" uuid="31f2ad0a-7a43-474c-8827-6e0adedd0cc0"/>
                <box leftPadding="10">
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement verticalAlignment="Middle">
                    <font fontName="Calibri" size="12"/>
                </textElement>
                <textFieldExpression><![CDATA[new SimpleDateFormat("MMM").format(DateUtils.addMonths($P{Date},-1))]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement mode="Opaque" x="0" y="30" width="160" height="30" backcolor="#EDF7FF" uuid="5fa38ea6-0449-4ebf-b669-4034169caa26"/>
                <box leftPadding="10">
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement verticalAlignment="Middle">
                    <font fontName="Calibri" size="12" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA["Current Date"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement mode="Opaque" x="0" y="60" width="160" height="30" backcolor="#EDF7FF" uuid="4f56f7e3-f84b-4aa6-a83f-c3ed7151711f"/>
                <box leftPadding="10">
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement verticalAlignment="Middle">
                    <font fontName="Calibri" size="12" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA["Current Month - 1"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement mode="Opaque" x="0" y="90" width="160" height="30" backcolor="#EDF7FF" uuid="129247b7-38f0-4d71-8429-697723aff783"/>
                <box leftPadding="10">
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement verticalAlignment="Middle">
                    <font fontName="Calibri" size="12" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA["Current Month + 0 "]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement mode="Opaque" x="160" y="90" width="390" height="30" backcolor="#EDF7FF" uuid="5f91c64b-8d22-41b9-9448-b2285471f871"/>
                <box leftPadding="10">
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement verticalAlignment="Middle">
                    <font fontName="Calibri" size="12"/>
                </textElement>
                <textFieldExpression><![CDATA[new SimpleDateFormat("MMM").format(DateUtils.addMonths($P{Date},0))]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement mode="Opaque" x="0" y="120" width="160" height="30" backcolor="#EDF7FF" uuid="f55b5d8c-da33-4b37-8345-b8d60d4682c4"/>
                <box leftPadding="10">
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement verticalAlignment="Middle">
                    <font fontName="Calibri" size="12" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA["Current Month + 1 "]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement mode="Opaque" x="160" y="120" width="390" height="30" backcolor="#EDF7FF" uuid="1745467e-740c-4863-821b-cd46041cbf95"/>
                <box leftPadding="10">
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement verticalAlignment="Middle">
                    <font fontName="Calibri" size="12"/>
                </textElement>
                <textFieldExpression><![CDATA[new SimpleDateFormat("MMM").format(DateUtils.addMonths($P{Date},1))]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement mode="Opaque" x="0" y="0" width="550" height="30" forecolor="#180566" backcolor="#EDE8E8" uuid="4442c4ba-1214-4042-afc8-072027105db2"/>
                <box leftPadding="10">
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Calibri" size="20" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA["Get Prvious and Next Months month Names"]]></textFieldExpression>
            </textField>
        </band>
    </title>
</jasperReport>

TIP: LoggedIn User Name defaut value expression in Jasper Server 6.x

How to get the default value of logged in user in jasper server input control.

1) Create a parameter named with "LoggedInUser" (Don't give double quotes).
    Give type as : com.jaspersoft.jasperserver.api.metadata.user.domain.User
NOTE:
# LoggedInUser is a default parameter provided by the product which is not visible. We have to manually create and use.
# Do not give this parameter is for prompting ( i.e., deselect the check box).

2) Create another parameter of your choice ( I have created "User" as another parameter).
Give type as : java.lang.String
Give default value expression as : $P{LoggedInUser}.getUsername()

3) Publish the report to the server and check the text field poped up with logged In User Name

Test Image : 


 

References : 
http://community.jaspersoft.com/wiki/built-parameters-logged-user

JRXML (Copy paste the code in Professional 6.x Studio report and observe the parameters and expressions). 


<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.1.0.final using JasperReports Library version 6.1.0  -->
<!-- 2015-10-13T23:55:04 -->
<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="UserName" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="83eecdc9-c7c8-4d1a-9508-923092906537">
    <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/"/>
    <property name="ireport.jasperserver.user" value="superuser"/>
    <property name="ireport.jasperserver.report.resource" value="/Training/UserName_files/main_jrxml"/>
    <property name="ireport.jasperserver.reportUnit" value="/Training/UserName"/>
    <parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User" isForPrompting="false"/>
    <parameter name="User" class="java.lang.String">
        <defaultValueExpression><![CDATA[$P{LoggedInUser}.getUsername()]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="30" splitType="Stretch">
            <textField>
                <reportElement x="120" y="0" width="120" height="30" uuid="b4ccca7e-ad0e-4202-85f4-a4117b1a7c57"/>
                <textElement textAlignment="Left" verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{User}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="0" width="120" height="30" uuid="dcbe87b3-fb60-488b-a959-4317df54299f"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA["User Name :"]]></textFieldExpression>
            </textField>
        </band>
    </title>
</jasperReport>

Saturday 10 October 2015

Tip : Handling NULL values for integer/Number type parameters in Stored Procedure Reports in Jaspersoft Business Intelligence Reports

Problem Statement : 
# How to pass null values to Integer/Number type parameters in Stored Procedures.

# Usually, we show textual information in the parameter drop down & in the back end pass
      IDs  related to that textual information.

# When we show textual information in the drop down we get --- (triple dashes) for single
        select  query parameter that means NULL(or null).

# When we pass this null value the stored procedure parameter which is Integer/Number type parameter doesn't understand and throws exception.

# Lets say the SQL part inside the SP is handling NULL values with 0 (zero) which will returning
    all the data (B'z Integer/Number type parameters don't accept NULL values in some cases if you won't handle it using nvl function of SQL.



Solution : 
Example : SP with static values
Return all the values of stored procedure when passing 0's.
{
  call procedure_employee(
                                                0,
                                                0,
                                                $P{ORACLE_REF_CURSOR} )
}

Return nothing when pass NULL values
{
  call procedure_employee(
                                                '  ',
                                                '  ',
                                                $P{ORACLE_REF_CURSOR} )
}
Example of SP with parameters which will return all the values of SP.

{
         call procedure_employee(
                                                        nvl($P{param_empID},0) ,       
                                                        nvl($P{param_ManagerID},0),
                                                        $P{ORACLE_REF_CURSOR}
                                              )
}

i.e., nvl function can be used with parameters in SQL stored procedure. 

NOTE : 
# $P{ORACLE_REF_CURSOR} is an java.sql.Resultset type Build in parameter 
     in jasper studio/iReport.  
# Enclose stored procedure call in curly braces {} and select query language type as PL/SQL in studio query dialogue window. 
# For String type single select query parameter it is not required to use nvl function. While writing SQL code inside Stored procedure we can write some thing like 
     WHERE <Field_Name>=<Parameter_Name> OR <Parameter_Name> IS NULL

I hope this helps some one.! I must thank you to my colleague in suggesting this approach to experiment on the SP call with Integer type single select NULL value.

Thank you. 
Sadakar Pochampalli

 

Thursday 8 October 2015

Use Case : Handling multi select parameter at report level in TIBCO Jaspersoft BI (OR) "Filter Expression" example to filter(multi select) the data at report level in TIBCO Jaspersoft BI

Hi Folks,

Here is my new workout on filtering data at report level instead of query level in TIBCO Jaspersoft Reports. I hope you will find this useful.

When we can go with this approach?
1) Situations when you can't send  multi select parameter to Stored Procedures from Jasper. (I have tested with Oracle SP & hoping that the same would work in other RDBMS stored procedure methodologies ). 
2) Situations when you write complex sub queries to get the the multiple values WHERE IN clause of main query.

This example, I have explored while converting Business Objects crystal reports into TIBCO Jaspersoft reports.

Software used for the use case : 
1) OS : Windows 10
2) TIBCO Jasper Design Studio 6.1 CE
3) TIBCO Jaspersoft server 6.1 CE
4) Mozilla firefox browser
5) Database : PostgreSQL and foodmart database

NOTE:
# Assuming that the reader is fa-miler in basics of report design and  I will be covering the core part of the use case in this post
# Click the images to get the best view of the content.

USE CASE Description : 
Select few fields from customer table to have a tabular report output.
Filter the data at query level on gender field and filter the data at report level using country field.

i.e., parameter associated with gender filed is single select and parameter associated with country field is multi select which would applied on report but not the query.

1) Create new report and create data source connections.
2) Create 2 parameters. One will be used at query(param_Gender) and another will be used at "Filter Expression" of the report.

##) param_Country (java.util.List is the type) - It will be used in "Filter Expression"
 ##) param_Gender(java.lang.String is the type) - It will be used in Query
3) Write below query to the report.
SELECT * FROM customer WHERE (gender= $P{param_Gender} or $P{param_Gender} IS NULL)

4)Scroll down "Data set and Query Dialog" editor and click on "Filter Expression" tab from the bottom Menu.

5) Write below expression in the "Filter Expression" area (Open the expression editor)
(
        $P{param_Country}.isEmpty()
    )
    ?
    (
        $P{param_Country} != null
    )
    :
    (
        ((ArrayList) $P{REPORT_PARAMETERS_MAP}.get( "param_Country" )).contains($F{country}.toString())
    )


6) Save the report and Preview.
Output test 1 : 
 ( select no elements for param_Country parameter and "F" for param_Gender).
You will get all the elements of gender F type. )
 i.e., When you don't pass anything in param_Country get all the result set of F type.

Output test 2 : 
Pass two values (Mexico and Canada) for param_Country and M type gender for param_Gender
You will get the output of M type gender who are from Mexico and Canada country.


Server side output:
1) Publish the report to the server.
2) Create these param_Country and param_Gender parameters in repository and link them to the report from server UI.
NOTE : param_Country is a multi select Query and param_Gender is single select query inputs.
Queries are :
#) param_Country takes SELECT DISTINCT country FROM customer
#) value and visible columns will be country filed.

#) param_Gender takes SELECT DISTINCT gender FROM customer 
#) value and visible columns will be gender field.



That's all. you are done writing parameters at query level as well report level.

Do you want to test the same example in your environment ?

Click this link to download the JRXML file & Project Export zip file.

# You can test it using stand alone JRXML in studio or by Importing the zip file into your server.
# Make sure you are running postgresql foodmart data base to get the output for this example.


Best Practices : 
1) Analyze the code in "Filter Expression". There could be other ways to achieve this.
2) Note that java.util.List type parameter take multiple values from parameters prompt.
3) There is no concept explained related to java.lang.Collection and $X{IN, fieldName, paramName} for this example. i.e., Report developer can assume that there would be only $X{} syntax way to pass multiple values which is not correct in few situvations.

I hope this will be helpful. If you find it useful please do share by clicking g+ on the page so that developers like us get benefit if they have the similar kind of requirement.
Also, suggestions & comments or alternative solutions are always welcome in comment box.


References :  

http://stackoverflow.com/questions/11512034/does-java-util-list-isempty-check-if-the-list-itself-is-null

 http://community.jaspersoft.com/questions/542895/dataset-expression-filter-filtering-using-contents-report-parameter-type


Thank you for finding this page & some time on this.

Cheers..!!!
Sadakar Pochampalli