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, March 29, 2016

Tip : Excel Export Expand(+) and Collapse(-) for grouped data in Jasper Reports (or) The Outline Row Level Property for Excel Extract Reports in Jaspersoft

Hi,

In this post you can learn how to get Expand and Collapse options when exporting a grouped by data excel report.

Steps : 
1) Design report as follows 
     
      Group Header  ($F{city})
             Detail (some fields)
      Group Footer (Dummy Text field with 0 height). 

(Click on image to get the best view)


2) Apply Body property to the fields that you keep in Detail Band
net.sf.jasperreports.export.xls.row.outline.level.1=Body

(click on Image to get the best of content)
 

3) Apply End property to the dummy field that you keep in Footer band.
net.sf.jasperreports.export.xls.row.outline.level.1=END

(Click on image to get the best view)
4) Sample Excel outputs
(Collapsed Stage)
A group expansion stage : 

References : 
http://jasperreports.sourceforge.net/sample.reference/xlsfeatures/


Sample JRXML ( works on 6.2 or later Jasper Studio professional with foodmart database )
 <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-03-29T18:23:38 -->
<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="Expand and Collapse Excel" pageWidth="595" pageHeight="842" columnWidth="595" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="58e9ce0f-5fbd-48ec-8645-53f5a0b2ad01">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <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"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
    <property name="ireport.jasperserver.url" value="http://localhost:8081/jasperserver-pro/"/>
    <property name="ireport.jasperserver.user" value="superuser"/>
    <property name="ireport.jasperserver.report.resource" value="/2_Training_Naresh/Reports/21_Expand_and_Collapse_Excel_Export_files/main_jrxml"/>
    <property name="ireport.jasperserver.reportUnit" value="/2_Training_Naresh/Reports/21_Expand_and_Collapse_Excel_Export"/>
    <queryString>
        <![CDATA[SELECT * FROM customer ORDER BY city]]>
    </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"/>
    <group name="city">
        <groupExpression><![CDATA[$F{city}]]></groupExpression>
        <groupHeader>
            <band height="16">
                <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                <textField>
                    <reportElement x="0" y="0" width="100" height="16" uuid="752494bc-a3a7-447b-b24a-16dd57b4e203">
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <box leftPadding="5"/>
                    <textElement verticalAlignment="Middle">
                        <font isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{city}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="100" y="0" width="100" height="16" uuid="4edb4831-d823-471c-8cdf-9621627e0fc9">
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement verticalAlignment="Middle">
                        <font isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[""]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="200" y="0" width="100" height="16" uuid="925b91cc-0b9e-4c68-b43e-01546ae19316">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[""]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="300" y="0" width="120" height="16" uuid="607f71cb-a9c1-4417-bfa3-cc4bac1f81c2">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[""]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="420" y="0" width="175" height="16" uuid="1508d978-b950-4f89-a362-d944afcd326c">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[""]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
        <groupFooter>
            <band>
                <textField>
                    <reportElement x="0" y="0" width="100" height="0" uuid="24a95893-5789-4871-bca0-f6d7f8a9bcf4">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="END"/>
                    </reportElement>
                    <textFieldExpression><![CDATA["Text Field"]]></textFieldExpression>
                </textField>
            </band>
        </groupFooter>
    </group>
    <columnHeader>
        <band height="16" splitType="Stretch">
            <printWhenExpression><![CDATA[$V{PAGE_COUNT}==1]]></printWhenExpression>
            <staticText>
                <reportElement x="100" y="0" width="100" height="16" uuid="0d2dd0ef-ad82-40cb-b7af-6cdbedc6fa12"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Customer ID]]></text>
            </staticText>
            <staticText>
                <reportElement x="200" y="0" width="100" height="16" uuid="b1f7385d-2678-405c-bdc1-1ebf363d1f16"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Customer Name]]></text>
            </staticText>
            <staticText>
                <reportElement x="300" y="0" width="120" height="16" uuid="72efd2e8-b27c-49f5-bbf6-7f0cf7b65868"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Occupation]]></text>
            </staticText>
            <staticText>
                <reportElement x="420" y="0" width="175" height="16" uuid="71a0dffd-4d85-40f0-b04e-b966697b8a14">
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement textAlignment="Center"/>
                <text><![CDATA[Phone Number]]></text>
            </staticText>
            <textField>
                <reportElement x="0" y="0" width="100" height="16" uuid="126c9bef-1748-4333-b3c4-dd641268e523">
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement verticalAlignment="Middle">
                    <font isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[""]]></textFieldExpression>
            </textField>
        </band>
    </columnHeader>
    <detail>
        <band height="16" splitType="Stretch">
            <textField>
                <reportElement x="100" y="0" width="100" height="16" uuid="262d3c0f-c9d4-4198-a6f1-4235e12967f6">
                    <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{customer_id}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="200" y="0" width="100" height="16" uuid="23a4613e-cbc2-49fa-8b85-c8c1c2b85538">
                    <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{fullname}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="300" y="0" width="120" height="16" uuid="9bdd418b-5ed5-4731-bb47-5f662f214693">
                    <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{occupation}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="420" y="0" width="175" height="16" uuid="1f727d26-1462-4c6a-8e6b-5dbb0e05668d">
                    <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{phone1}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="0" width="100" height="16" uuid="282bb881-bc06-490f-83b2-a34764648547">
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement verticalAlignment="Middle">
                    <font isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[""]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>
 


I hope it helps some one. .! & thank you to the friend who explained this to me today.  :-) :-)

References: 
http://jasperreports.sourceforge.net/sample.reference/xlsfeatures/ 

Sunday, March 27, 2016

Tip : Japsersoft Design Studio \.metadata\.log error

Hi,
When you see \.metadata\.log error while launching Studio, you can follow below steps to launch it successfully,

Steps :
1) Go to C:\Users\sadakar\JaspersoftWorkspace\.metadata\.plugins\org.eclipse.core.resources
2) Delete .snap file
3) Now, launch Studio

:-)

Friday, March 18, 2016

Tip : Row banding and column banding OR alternative colors for rows and columns in Jaspersoft Studio 6.x in cross tab component

Hi Folks,

Usually we write grid reports by making use of column header and Detail band or table component in JRXML file. 

When write a grid report using column headers, we make use of Styles and conditional expression with REPORT_COUNT variable to get the alternative colors, similarly when we use Table component, we take check box utility in its style section to get the alternative colors functionally.

Today, I need to implement the row or column banding in cross tab component - cross tab also mean to display grid data but the data will be grouped by rows and columns. Neither we can't use  REPORT_COUNT variable on cross tab fields nor any utility.

Here are the steps to have row or column banding.

1) Write your cross tabular report.
2) Go to Style section from report outline or cross tab outline.
3) Create a Style -> Create two conditional Styles. One for row banding another for column banding.
    Lets say Style name is Style1
4) Write below condition in 1st condition & on its Appearance section choose row banding color.
    $V{ROW_COUNT}%2==0
5) Write below condition in 2nd condition of same Style1& on its Appearance section choose another color for column
   $V{COLUMN_COUNT}%2==0
6) Now , go the cross tabs text elements and select them and apply Style1 in Appearance section for Style property.
7) Save your report and preview.

NOTE : 
1) While choosing colors on condition do not uncheck "Transperant" property.
2) On cross tab text fields you must uncheck "Transparent" property.
3)  COLUMN_COUNT and ROW_COUNT are built in variables that you can see in grayed out color in Variables section.
4) ROW_COUNT is not available by default in variables section in 6.2 studio.

Sample output : 
1) Cross tab row banding
 
2) Cross tab row banding + Column banding


Style code in JRXML : 
    <style name="Style1">
        <conditionalStyle>
            <conditionExpression><![CDATA[$V{ROW_COUNT}%2==0]]></conditionExpression>
            <style backcolor="#C6EDF7"/>
        </conditionalStyle>
        <conditionalStyle>
            <conditionExpression><![CDATA[$V{COLUMN_COUNT}%2==0]]></conditionExpression>
            <style backcolor="#DBB2D3"/>
        </conditionalStyle>
    </style>


Applying Row banding code sample on a text field
<textField>
   <reportElement style="Style1" mode="Opaque" x="0" y="0" width="60" height="20" uuid="2dd638c2-fd6a-4bbf-8fb9-f9807dc1ee97"/>
   <textElement textAlignment="Left" verticalAlignment="Middle"/>
   <textFieldExpression><![CDATA[$V{education1}]]></textFieldExpression>
     </textField>



 References : 
http://jasperreports.sourceforge.net/sample.reference/crosstabs/index.html


Full Sample JRXML : Jaspersoft Design Studio 6.2 Professional code and runs on foodmart db of postgres

 
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-03-19T01:30:11 -->
<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="cross tab" pageWidth="900" pageHeight="842" columnWidth="860" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="919521d7-927b-4cd9-b646-0b6cc361b467">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <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"/>
    <style name="Crosstab_CH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Crosstab_CG" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Crosstab_CT" mode="Opaque" backcolor="#005FB3">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Crosstab_CD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Style1">
        <conditionalStyle>
            <conditionExpression><![CDATA[$V{ROW_COUNT}%2==0]]></conditionExpression>
            <style backcolor="#C6EDF7"/>
        </conditionalStyle>
        <conditionalStyle>
            <conditionExpression><![CDATA[$V{COLUMN_COUNT}%2==0]]></conditionExpression>
            <style backcolor="#DBB2D3"/>
        </conditionalStyle>
    </style>
    <queryString>
        <![CDATA[SELECT * FROM customer]]>
    </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="50"/>
    </title>
    <summary>
        <band height="65" splitType="Stretch">
            <crosstab>
                <reportElement x="0" y="0" width="860" height="65" uuid="a84e27b9-2297-4bb0-9e07-61e9dd9ba7db">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                </reportElement>
                <rowGroup name="education1" width="60" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{education}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement style="Style1" mode="Opaque" x="0" y="0" width="60" height="20" uuid="2dd638c2-fd6a-4bbf-8fb9-f9807dc1ee97"/>
                                <textElement textAlignment="Left" verticalAlignment="Middle"/>
                                <textFieldExpression><![CDATA[$V{education1}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="30a79663-0b76-4abc-9bdf-ea5e07e948b2"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <text><![CDATA[Total education1]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="occupation1" height="20" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{occupation}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="60" height="20" uuid="9253a1ec-7290-4556-8c8e-342f8f25855f"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <textFieldExpression><![CDATA[$V{occupation1}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="d93ba240-0361-4d4a-b9b7-c044815db4b5"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <text><![CDATA[Total occupation1]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <columnGroup name="gender1" height="20">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{gender}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="60" height="20" uuid="490b42a0-ef38-4349-9800-d14fc159a8f6"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <textFieldExpression><![CDATA[$V{gender1}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents>
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" uuid="84014d77-4b07-4b51-85ac-21eb7ba521f5"/>
                                <text><![CDATA[Total Column Group1]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="total_children_MEASURE1" class="java.lang.Integer" calculation="Sum">
                    <measureExpression><![CDATA[$F{total_children}]]></measureExpression>
                </measure>
                <crosstabCell width="60" height="20">
                    <cellContents mode="Opaque" style="Crosstab_CD">
                        <textField>
                            <reportElement style="Style1" mode="Opaque" x="0" y="0" width="60" height="20" uuid="96136dd9-4455-4486-86a4-4837a7361a6f"/>
                            <textElement textAlignment="Center" verticalAlignment="Middle"/>
                            <textFieldExpression><![CDATA[$V{total_children_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" columnTotalGroup="occupation1">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="8d952fc1-2878-4ba9-8224-9b1637995a15"/>
                            <textElement textAlignment="Center" verticalAlignment="Middle"/>
                            <textFieldExpression><![CDATA[$V{total_children_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="education1">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="fa8c9b2f-c393-4ab9-b8d0-d20e564d03e7"/>
                            <textElement textAlignment="Center" verticalAlignment="Middle"/>
                            <textFieldExpression><![CDATA[$V{total_children_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="education1" columnTotalGroup="occupation1">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="9f0a729a-a6b0-49c5-938c-604267f0d758"/>
                            <textElement textAlignment="Center" verticalAlignment="Middle"/>
                            <textFieldExpression><![CDATA[$V{total_children_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" columnTotalGroup="gender1">
                    <cellContents/>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="education1" columnTotalGroup="gender1">
                    <cellContents mode="Opaque" style="Crosstab_CT"/>
                </crosstabCell>
            </crosstab>
        </band>
    </summary>
</jasperReport>

Friday, March 11, 2016

Learn new features in Jasper Server 6.2 : Exporting Jaspersoft dashboard to PDF, WORD , PNG and OTD - How to Guide

Hi,

Jasper server 6.2 Pro/EE releases have lots of improvements and new features. Today, I have gone through one of the features on server side in dashboard designer ,  that is Exporting dashboard to 4 variety of formats namely PDF, MS-Word , PNG image and OTD.

In this tip, you will learn/see how to export dashboard in above formats

1) Dashboard designer properties section has a check box(In the designer mode of dashboard right click anywhere on the canvas)  to enable dashboard export button when previewing.

2) After checking it in dashboard designer properties if you try exporting the dashboard you may get error message.

3) To make sure the export button working correctly, you need to install the PhantomJS software in the machine/server where your jasperserver is hosted/installed.

4) Download and keep the PhantomJS in C drive or somewhere in the directory.

PhantomJS download Link : 
http://phantomjs.org/download.html

5) Extract the zip file and keep the phantomjs-2.1.1-windows folder in C drive.  
Example : C:\phantomjs-2.1.1-windows
Inside into it, in the bin folder you can see  phantomjs.exe file.

6) You need to give the above .exe path  in js.config.properties file
File Path :
C:\Jaspersoft\jasperreports-server-6.2.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF

i.e., Add below line in above file

phantomjs.binary=C:\\phantomjs-2.1.1-windows\\bin\\phantomjs.exe

7) Restart the the Jasper server if it is already running and see the preview of dashboard and make use of export button.


Example :  1) Exporting Supermart dashboard to PDF

1) Open the dashboard in Edit mode  or go to the dashboard designer

2) Click the settings button and check the "Show export button" option.
3) Save your dashboard and click on run button

4) Click on "Export" Button to export to PDF or PNG or DOCX or OTD

5) Sample Export to PDF
Image-1 : 

Image-2 :


I hope it helps someone in configuring and in how to do .

Until Next  Post..
Sadakar Pochampalli 





Monday, March 07, 2016

Tip : Detail Overflow Vs Stretch With Overflow in Jasper Reports - When to use & How to avoid repetaiton(Images or text ) when Detail Overflow for a Text Field

Hi All,

This tip is useful when you work with grid reports.

Detail Overflow :
The element will be printed when the band overflows to a new page or a new column.

Stretch with Overflow : 
Tell the report engine to allow the text field to stretch downwards in order to display all its text when it doesn't fit in the defined text field height.
This property will work irrespective of cell height that you provide.

(NOTE : You can hover the mouse on the property to view the purpose of it, this way you can learn each and every property available in Studio)

Both are the properties of a text field.


Example Details Overflow : 
On the text field(all text fields) select
Position Type = Float
Stretch Type = Relative to Tallest Object
Detail Overflow = check this
Stretch with Overflow = check this

Page-1 output :
 
Page-2 output:

From the above images, what you can understand is when Detail overflow is checked the text Static Text and Image filed data is repeating in next page.
If you uncheck it it will not repeat in page-2 :

for example
On the text field(all text fields) select
Position Type = Float
Stretch Type = Relative to Tallest Object
Detail Overflow = Uncheck this 
Stretch with Overflow = check this

Page-1 output portion in the bottom :
Page-2 output

Hope it helps someone.! 
Cheers.!!

 Sample JRXML : Japsersoft stduio Professional 6.2


<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-03-08T12:50:05 -->
<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="Test" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="f86bcfb7-d774-451f-9f46-c58f57e0efbb">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
    <queryString>
        <![CDATA[select * from dummy]]>
    </queryString>
    <field name="sno" class="java.lang.Integer"/>
    <field name="filed1" class="java.lang.Integer"/>
    <field name="filed2" class="java.lang.String"/>
    <columnHeader>
        <band height="30">
            <staticText>
                <reportElement x="0" y="0" width="120" height="30" uuid="0cd17b54-755c-42fe-9363-7266a24337e3"/>
                <box>
                    <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>
                <text><![CDATA[Static Text]]></text>
            </staticText>
            <staticText>
                <reportElement x="170" y="0" width="130" height="30" uuid="f216b782-3080-4ba8-8cc3-2609c684f4be"/>
                <box>
                    <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>
                <text><![CDATA[filed1]]></text>
            </staticText>
            <staticText>
                <reportElement x="300" y="0" width="90" height="30" uuid="71985024-8b9a-49ae-81e0-6d6e9335cec7"/>
                <box>
                    <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>
                <text><![CDATA[filed2]]></text>
            </staticText>
            <staticText>
                <reportElement x="120" y="0" width="50" height="30" uuid="d45c0977-d842-424b-ab44-7edd5672b72d"/>
                <box>
                    <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>
                <text><![CDATA[Image]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30">
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="0" y="0" width="120" height="30" isPrintWhenDetailOverflows="true" uuid="fb93f1ac-7f7a-48fe-afa7-4aab5370cbec"/>
                <box>
                    <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>
                <textFieldExpression><![CDATA[""]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="170" y="0" width="130" height="30" uuid="b5a72533-c1b6-4af9-a0b7-06d099981190"/>
                <box>
                    <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>
                <textFieldExpression><![CDATA[$F{filed1}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="300" y="0" width="90" height="30" uuid="bab5a3e9-22db-46b0-9302-f7614a6f6add"/>
                <box>
                    <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>
                <textFieldExpression><![CDATA[$F{filed2}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="0" y="0" width="120" height="30" uuid="d4338fad-1803-491e-90ef-8e01cb3de80d"/>
                <box>
                    <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>
                <textFieldExpression><![CDATA["Hello"]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="170" y="0" width="130" height="30" uuid="8cdac468-0a3f-4ed3-844b-473e1371f92e"/>
                <box>
                    <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>
                <textFieldExpression><![CDATA[""]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="120" y="0" width="50" height="30" uuid="6ab2f4f8-04cd-4a6f-b945-3a7af4b8c3fa"/>
                <box>
                    <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>
                <textFieldExpression><![CDATA["Hello"]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

HTML-5 Charts advanced properties best practices in Japser Reports (Uncaught Java Script Error & TypeError: Cannot find function add in object 0 com/jaspersoft/jasperreports/highcharts/charts/render/scripts/highcharts-4.0.3.bbox.js#1969)

Hi All,

In Jasper Reports, when working with Advanced Chart Options with HTML-5 charts, you may experience Uncaught Java Script Error message and while exporting the report you may get below error.

Error Message

net.sf.jasperreports.engine.JRRuntimeException: org.mozilla.javascript.EcmaError: TypeError: Cannot find function add in object 0. (com/jaspersoft/jasperreports/highcharts/charts/render/scripts/highcharts-4.0.3.bbox.js#1969)
Error Message

org.mozilla.javascript.EcmaError: TypeError: Cannot find function add in object 0. (com/jaspersoft/jasperreports/highcharts/charts/render/scripts/highcharts-4.0.3.bbox.js#1969)



How to overcome & What are the best practices while working with Advanced chart properties ? 

1) Point out your query fields to the graph (any graph from HTML-5).
2) For every change in your JRXML (report or chart level) publish to the server and check the export. (Check the output in 4 different browsers IE or Edge , Mozilla, Chrome and Safari).
3) The above problem comes when you add advanced Properties.

 For example: When you add linearGradient property in Advanced Section
chart.backgroundColor.linearGradient = java.util.Arrays.asList(0,0,500,500)
You should check the expression checkbox for the property because the value of the property is written in Java. 

4) Do not use direct values like 3 , 4 or some thing like that instead use parser functions like new Integer(3) or new Integer(4).

5) Also when adding a variable or field with integer follow this method :  
Preferable : $V{someVariable}+new Integer(4) or $F{someField}+new Integer(60)
Not Preferable : $V{someVariable}+4 or $F{someField}+60 

6)  yAxis tick interval
Wrong :



<hc:chartProperty name="yAxis.tickInterval" value="10"/>
 
Correct : 
<hc:chartProperty name="yAxis.tickInterval"><hc:propertyExpression>
<![CDATA[new Integer(10)]]>
</hc:propertyExpression></hc:chartProperty>
 
 
7) Similarly with all other properties that you take from HTML-5 API should go in the way that the Studio engine can understand the script and exportable into PDF.

I hope this helps someone. !

Thank you.
Sadakar Pochampalli 






Friday, March 04, 2016

Tip : Cascading parameters - Multi select & check box(boolean true or false) in Jasper Reports

Hi Folks,

This tip is useful if you are loading thousands of values into an input control(single/multi).
How  much time the input control pane is taking to load when you run a large report in which one of the parameter is having thousands of values in it.

The end user(lets say a banking employee) might get nervous to wait for the input pane window opened to run this report(imagine if he will be waiting for 3 to 5 min to load the input pane itself, how much time he/she would has to wait to load the report  - Of course, it is based on your SQL logic but lets say your report is executing in 5 min b'z of heavy transnational data)

Add a check box(boolean) parameter to your input controls pane window and pass this parameter to all your other parameters in the report - i.e., cascade all the other parameters with this boolean parameter.

Once you cascade all the parameters, now when you run the report, the input controls pane for that report will load in matter of seconds hence the user will get experienced of the report is loading.

Lets' see an example for cascading use case with multi select parameter and boolean value

Explaining only on server side :  Cascade "paramCity" multi select parameter with boolean check box parameter. i.e., when you check the tick box then the values in "City" parameter should get loaded.

SELECT distinct state_province,city FROM customer WHERE 'true'=$P{param_CheckMe}

NOTE : In jasper boolean parameters returns true or false. While cascading make sure true should be given in single quotes as shown in above SQL code. 

and the other parameter is "paramCheckMe" is of type "boolean" created.

Screenshots : 



No values populated in "City" parameter when unchecking the boolean parameter (uncheck)

Values populated in "City" parameter when checking the boolean parameter (uncheck)

I hope this helps some one.!