Tuesday 29 May 2018

Tip : Usage of COLUMN_NUMBER variable in Jaspersoft Studio reports

Hi folks,

Recently, I wrote about the usage of horizontal display (looks like vertical display) of sql output in jaspersoft studio at read it here.

At report level to display the row number with detail band we use "REPORT_COUNT" variable, what if you want to display the column numbers when displaying the records in horizontal view(looks like vertical) ?

Use "COLUMN_NUMBER" variable in a text expression. i.e., ${COLUMN_NUMBER}.


Watch video tutorial for the same example below(with voice)


Sample screenshot is shown in below: (An example of fixed number of customers to display)

(Click on image to get the best view)

Sample screenshot of the report design: 

(Click on image to get the best view)

Variable usage : 

(Click on image to get the best view)


JRXML: ( Supports in 6.4.2 Pro or later) - The example uses "foodmart" db of postgresql.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.2.final using JasperReports Library version 6.4.1  -->
<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="ColumnNumber" columnCount="10" printOrder="Horizontal" pageWidth="1000" pageHeight="240" columnWidth="100" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="4b5118e2-f385-4f14-a743-8c42ce6f64f8">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
<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"/>
<queryString language="SQL">
<![CDATA[SELECT * FROM customer limit 10]]>
</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"/>
<title>
<band height="30">
<textField>
<reportElement mode="Opaque" x="0" y="0" width="1000" height="30" backcolor="#C4BFBE" uuid="f5f8aa2e-b32c-4e01-9cd4-eced9709e8e5">
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="20" isBold="true"/>
</textElement>
<textFieldExpression><![CDATA["COLUMN_NUMBER variable usage"]]></textFieldExpression>
</textField>
</band>
</title>
<detail>
<band height="210" splitType="Stretch">
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<textField>
<reportElement x="0" y="30" width="100" height="30" uuid="c06c9319-9184-44f7-93ea-727751f38877"/>
<box leftPadding="5">
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{customer_id}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="60" width="100" height="30" uuid="64bcc1b5-899e-4433-a3a7-b73e8d4a5dfc"/>
<box leftPadding="5">
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{fullname}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="90" width="100" height="30" uuid="6628fe84-1bc9-42c2-9d5f-d08e0f9b891a"/>
<box leftPadding="5">
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{country}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="120" width="100" height="30" uuid="0b1a3b1f-3878-4e64-a5fc-ca33cda52c66"/>
<box leftPadding="5">
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{yearly_income}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="150" width="100" height="30" uuid="33c68e73-de62-4287-9154-9a1f76730d3a"/>
<box leftPadding="5">
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{gender}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="180" width="100" height="30" uuid="a8e7cf7d-26de-4078-ae16-c9e6322bdd0c"/>
<box leftPadding="5">
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$F{occupation}]]></textFieldExpression>
</textField>
<textField>
<reportElement mode="Opaque" x="0" y="0" width="100" height="30" backcolor="#E3E1E1" uuid="dc7a89ed-2fe5-4b5a-a7c2-ec32148e009a"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font isBold="true"/>
</textElement>
<textFieldExpression><![CDATA["Customer"+" "+$V{COLUMN_NUMBER}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>

Tuesday 8 May 2018

Tip : Horizontal(looks like vertical) display of detailed records in jasper reports - convert rows as columns(alternative of cross tab)


Hi folks, 

In this tip, you would see how to convert sql rows into report columns. 
By default when you put text fields in detail band, you could see the report prints the query output in vertical order(row by row). There are some situations when your query results definite number of rows in output and need them to be displayed as horizontal view. 

In this case, usually one might think of cross tab implementation. This is fine upto some extent but working with cross tab is not always developer friendly hence instead of using it one can split the report into N number columns in reports properties and provide desired width for columns and then pull your fields one below to other. In the report advanced properties change Print Order property from Vertical to Horizontal and this would result converting rows output into columns that would look like a cross tab implementation. 

Design:

Query format/Sample query output


Sample output: 


JRXML: (Developed using 6.4.2 professional - it should also works in community[not tested])

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.2.final using JasperReports Library version 6.4.1  -->
<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="columns" columnCount="5" printOrder="Horizontal" pageWidth="650" pageHeight="842" columnWidth="120" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" whenResourceMissingType="Error" uuid="adca597a-f8d3-4f9f-ae19-21b3272acdb9">
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<queryString>
<![CDATA[(SELECT 100 customerID, 'Sadakar' CustomerName, 'Hyd' CustomerCity FROM customer limit 1)
UNION ALL
(SELECT 101 customerID, 'Hasini' CustomerName, 'MNCL' CustomerCity FROM customer limit 1)
UNION ALL
(SELECT 102 customerID, 'Venkat' CustomerName, 'Banglore' CustomerCity FROM customer limit 1)
UNION ALL
(SELECT 103 customerID, 'Robert' CustomerName, 'Chenni' CustomerCity FROM customer limit 1)
UNION ALL
(SELECT 104 customerID, 'Soujanya' CustomerName, 'MMR' CustomerCity FROM customer limit 1)]]>
</queryString>
<field name="customerid" class="java.lang.Integer"/>
<field name="customername" class="java.lang.String"/>
<field name="customercity" class="java.lang.String"/>
<pageHeader>
<band height="46" splitType="Stretch">
<textField>
<reportElement x="0" y="0" width="610" height="46" uuid="217244d6-5976-4298-bf74-d5e93099b5c7"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="16"/>
</textElement>
<textFieldExpression><![CDATA["Horizontal display of definite number of rows output like a cross tab view"]]></textFieldExpression>
</textField>
</band>
</pageHeader>
<detail>
<band height="93" splitType="Stretch">
<textField>
<reportElement x="0" y="3" width="100" height="30" uuid="160c2caa-87e8-4757-8b6d-7ff9daad46e8"/>
<textFieldExpression><![CDATA[$F{customerid}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="33" width="100" height="30" uuid="91ef78c5-4eae-43a4-9f64-8a6848961018"/>
<textFieldExpression><![CDATA[$F{customername}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="63" width="100" height="30" uuid="f3c5ce61-e141-409e-8b21-86d4cdbfd7e5"/>
<textFieldExpression><![CDATA[$F{customercity}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>