Saturday 19 March 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>

1 comment:

  1. Hello. Thanks for the tips. Is there a way that I can programatically set the export to excel to begin at row 10 using a table component? The export to an excel workbook is going fine, I just want it to begin on line 11.

    thanks,

    Michael

    ReplyDelete