I could not understand why Google was not my friend this time. And as far as I can tell there is no a single post with a tutorial on how to hide columns in a Jasper Report from iReport.
It is difficult for me to accept there is no solution to respect separation of concerns in software architecture and development.
It is my eternal fight. Developers are tempted to embed look and feel code (View) inside business logic or routing code (Controller). In some cases even in the database (Model).
When I designed the
Real Time Report Architecture based on Jasper reports I always thought about someone working with iReport building the appearance of the report while someone would just make sure the database would be available in a way that simple queries could be made to create report datasets. Finally from datasets it should be a piece of cake to render a final product using graphical components.
Suddenly a road block from the team: iReport does not allow to hide columns on demand. Let us use Dynamic Jasper. In fact why are we going with this whole design in iReport, we could just do everything in Jasper, yeah from Java!
My answer: Nope, that violates separation of concerns. I will try my best to get a clean solution and if I ever use Dynamic Jasper it would be to provide a temporary hack while a bug or feature request that I fill in Tracker is resolved. And in any case I would always read the JRXML as template and modify that from code. I will never give up the concept, that is as important as your environment. The architect knows it very well.
So I first tried with "printWhenExpression" node that can be used like:
<textField>
<reportElement x="0" y="0" width="100" height="10" isPrintWhenDetailOverflows="true">
<printWhenExpression><![CDATA[new Boolean($P{showAccount})]]></printWhenExpression>
</reportElement>
<textElement>
<font size="6"/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$F{account}]]></textFieldExpression>
</textField>
There are two problems with this solution:
- It is verbose because you need to declare it not only for the value but also for any other element in the same vertical position for example lines and column names.
- An empty vertical space will remain in the column position in other words the rest of the columns will never move from their original position.
A simple concept called absolute position is the one making our lives miserable here.
The answer to this problem is then to find a different component that allows for relative positioning. For sure the author of this still to be found component allows to remove the column and make the rest move to the empty position if of course the replaced column is on the left. And that component was unfortunately missing in JasperReports for sometime up until a
tracker ticket was resolved. So the good news is that for a whole year this component has been available.
The component we need is a simple table or "Table Component" which is the way JasperReports call it. Now, anybody would think that something that has been part of JasperReports for a year should already have a tutorial especially when it solves such an important problem: Separation of concerns. The web is plenty of examples to use native library, Dynamic Jasper, JasperReports server, velocity templates. It came to my attention that actually nobody thought about using XSLT which would be in my opinion the correct way if you want to apply a transformation to an XML document like jrxml. Hey, BTW there is also XGAWK which is faster than XSLT: Oops no! Forget it, is not integrated with Java. The bottom line is there is not such tutorial and that is why I created this one.
I spent so much time on this one to help my team that I said: I will take an extra hour and document what I did with a simple example not only for my team but to give back to the community. After all Jose Marti said: Every man should have the right to be educated and in return with his effort contribute to the education of others.
Here is how to hide columns with an example:
- The first thing you will need is a database. I use sqlite3 and here is the schema for the balance.db:
'balance.sql
DROP TABLE IF EXISTS `balance`;
CREATE TABLE `balance` (
`category` varchar,
`account` varchar,
`opening_balance` float,
`ending_balance` float,
`debit` float,
`credit` float
);
INSERT INTO `balance` values ('Food', 'City Bank', 1000.00, 800.00, 200.00, 0.00);
INSERT INTO `balance` values ('Food', 'J.P Morgan', 500.00, 400.00, 100.00, 0.00);
INSERT INTO `balance` values ('Gas', 'J.P Morgan', 400.00, 200.00, 200.00, 0.00);
INSERT INTO `balance` values ('Clothes', 'City Bank', 800.00, 700.00, 100.00, 0.00);
INSERT INTO `balance` values ('Payrol', 'City Bank', 700.00, 1700.00, 0.00, 1000.00);
INSERT INTO `balance` values ('Business', 'J.P Morgan', 700.00, 1700.00, 0.00, 1000.00);
- Create the db:
sqlite3 balance.db < balance.sql
- From iReport create a new Datasource pointing it to your database. Here is the config for my local sqlite datase:
Name: balance
JDBC Driver: org.sqlite.JDBC
JDBC URL: jdbc:sqlite:/Users/nestor/Downloads/balance.db
Note that you must have the sqlite3 JDBC driver. As I explained in a previous post just go to "preferences | classpath" and point to sqlite3 driver file: sqlitejdbc-v056.jar. Of course you need to download that file if you do not have it. Just Google it.
- Create a local file named balance.jrxml with the below content. Open it from iReport:
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="balance" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="30" bottomMargin="30" isFloatColumnFooter="true">
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<style name="Sans_Normal" isDefault="true" fontName="DejaVu Sans" fontSize="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
<style name="Table">
<box>
<pen lineWidth="1.0" lineColor="#FF0000"/>
</box>
</style>
<style name="TableHeader" mode="Opaque" backcolor="#808080"/>
<style name="TableFooter" mode="Opaque" backcolor="#C0C0C0"/>
<subDataset name="TableData">
<queryString language="SQL">
<![CDATA[SELECT
category,
account,
opening_balance,
ending_balance,
debit,
credit
FROM balance]]>
</queryString>
<field name="category" class="java.lang.Object"/>
<field name="account" class="java.lang.Object"/>
<field name="opening_balance" class="java.lang.Object"/>
<field name="ending_balance" class="java.lang.Object"/>
<field name="debit" class="java.lang.Object"/>
<field name="credit" class="java.lang.Object"/>
<variable name="creditSum" class="java.lang.Double" calculation="Sum">
<variableExpression><![CDATA[$F{credit}]]></variableExpression>
</variable>
<variable name="debitSum" class="java.lang.Double" calculation="Sum">
<variableExpression><![CDATA[$F{debit}]]></variableExpression>
</variable>
</subDataset>
<parameter name="showOpeningBalance" class="java.lang.Boolean">
<defaultValueExpression><![CDATA[new Boolean(true)]]></defaultValueExpression>
</parameter>
<parameter name="showEndingBalance" class="java.lang.Boolean">
<defaultValueExpression><![CDATA[new Boolean(true)]]></defaultValueExpression>
</parameter>
<parameter name="showCategory" class="java.lang.Boolean">
<defaultValueExpression><![CDATA[new Boolean(true)]]></defaultValueExpression>
</parameter>
<parameter name="showAccount" class="java.lang.Boolean">
<defaultValueExpression><![CDATA[new Boolean(true)]]></defaultValueExpression>
</parameter>
<parameter name="showDebit" class="java.lang.Boolean">
<defaultValueExpression><![CDATA[new Boolean(true)]]></defaultValueExpression>
</parameter>
<parameter name="showCredit" class="java.lang.Boolean">
<defaultValueExpression><![CDATA[new Boolean(true)]]></defaultValueExpression>
</parameter>
<title>
<band height="150">
<componentElement>
<reportElement style="Table" x="0" y="50" width="555" height="100"/>
<c:table xmlns:c="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
<datasetRun subDataset="TableData">
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
</datasetRun>
<c:column width="80">
<printWhenExpression><![CDATA[new Boolean($P{showCategory})]]></printWhenExpression>
<c:columnHeader style="TableHeader" height="30" rowSpan="2">
<box leftPadding="10">
<pen lineColor="#000000"/>
<bottomPen lineWidth="0.5"/>
</box>
<staticText>
<reportElement x="0" y="0" width="70" height="30"/>
<textElement verticalAlignment="Middle">
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[Category]]></text>
</staticText>
</c:columnHeader>
<c:columnFooter style="TableFooter" height="15">
<box leftPadding="10">
<pen lineColor="#000000"/>
</box>
<staticText>
<reportElement x="0" y="0" width="70" height="15"/>
<textElement verticalAlignment="Middle">
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[Totals]]></text>
</staticText>
</c:columnFooter>
<c:detailCell height="15">
<box leftPadding="10">
<bottomPen lineWidth="0.5"/>
</box>
<textField isStretchWithOverflow="true">
<reportElement x="0" y="0" width="70" height="15"/>
<textElement/>
<textFieldExpression class="java.lang.String"><![CDATA[$F{category}]]></textFieldExpression>
</textField>
</c:detailCell>
</c:column>
<c:column width="80">
<printWhenExpression><![CDATA[new Boolean($P{showAccount})]]></printWhenExpression>
<c:columnHeader style="TableHeader" height="30" rowSpan="2">
<box leftPadding="10">
<pen lineColor="#000000"/>
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
</box>
<staticText>
<reportElement x="0" y="0" width="70" height="30"/>
<textElement verticalAlignment="Middle">
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[Account]]></text>
</staticText>
</c:columnHeader>
<c:columnFooter style="TableFooter" height="15">
<box leftPadding="10">
<pen lineColor="#000000"/>
<leftPen lineWidth="0.5"/>
</box>
<staticText>
<reportElement x="0" y="0" width="70" height="15"/>
<textElement verticalAlignment="Middle">
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[-]]></text>
</staticText>
</c:columnFooter>
<c:detailCell height="15">
<box leftPadding="10">
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
</box>
<textField>
<reportElement x="0" y="0" width="70" height="15"/>
<textElement/>
<textFieldExpression class="java.lang.String"><![CDATA[$F{account}]]></textFieldExpression>
</textField>
</c:detailCell>
</c:column>
<c:columnGroup width="200">
<c:columnHeader style="TableHeader" height="15">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
</box>
<staticText>
<reportElement x="0" y="0" width="190" height="15"/>
<textElement textAlignment="Center">
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[Balance]]></text>
</staticText>
</c:columnHeader>
<c:column width="100">
<printWhenExpression><![CDATA[new Boolean($P{showOpeningBalance})]]></printWhenExpression>
<c:columnHeader style="TableHeader" height="15">
<box leftPadding="10">
<pen lineColor="#000000"/>
<topPen lineWidth="0.5"/>
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
</box>
<staticText>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement verticalAlignment="Middle">
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[Opening]]></text>
</staticText>
</c:columnHeader>
<c:columnFooter style="TableFooter" height="15">
<box leftPadding="10">
<pen lineColor="#000000"/>
<leftPen lineWidth="0.5"/>
</box>
<staticText>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement verticalAlignment="Middle">
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[-]]></text>
</staticText>
</c:columnFooter>
<c:detailCell height="15">
<box leftPadding="10">
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
</box>
<textField>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement/>
<textFieldExpression class="java.lang.Double"><![CDATA[$F{opening_balance}]]></textFieldExpression>
</textField>
</c:detailCell>
</c:column>
<c:column width="100">
<printWhenExpression><![CDATA[new Boolean($P{showEndingBalance})]]></printWhenExpression>
<c:columnHeader style="TableHeader" height="15">
<box leftPadding="10">
<pen lineColor="#000000"/>
<topPen lineWidth="0.5"/>
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
</box>
<staticText>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement>
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[Ending]]></text>
</staticText>
</c:columnHeader>
<c:columnFooter style="TableFooter" height="15">
<box leftPadding="10">
<pen lineColor="#000000"/>
<leftPen lineWidth="0.5"/>
</box>
<staticText>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement>
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[-]]></text>
</staticText>
</c:columnFooter>
<c:detailCell height="15">
<box leftPadding="10">
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
</box>
<textField>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement/>
<textFieldExpression class="java.lang.Double"><![CDATA[$F{ending_balance}]]></textFieldExpression>
</textField>
</c:detailCell>
</c:column>
</c:columnGroup>
<c:columnGroup width="200">
<c:columnHeader style="TableHeader" height="15">
<box>
<pen lineWidth="0.5" lineColor="#000000"/>
</box>
<staticText>
<reportElement x="0" y="0" width="190" height="15"/>
<textElement textAlignment="Center">
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[Transaction]]></text>
</staticText>
</c:columnHeader>
<c:column width="100">
<printWhenExpression><![CDATA[new Boolean($P{showDebit})]]></printWhenExpression>
<c:columnHeader style="TableHeader" height="15">
<box leftPadding="10">
<pen lineColor="#000000"/>
<topPen lineWidth="0.5"/>
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
</box>
<staticText>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement verticalAlignment="Middle">
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[Debit]]></text>
</staticText>
</c:columnHeader>
<c:columnFooter style="TableFooter" height="15">
<box leftPadding="10">
<pen lineColor="#000000"/>
<leftPen lineWidth="0.5"/>
</box>
<textField>
<reportElement x="0" y="0" width="50" height="15"/>
<textElement>
<font size="12" isBold="true"/>
</textElement>
<textFieldExpression class="java.lang.Double"><![CDATA[$V{debitSum}]]></textFieldExpression>
</textField>
</c:columnFooter>
<c:detailCell height="15">
<box leftPadding="10">
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
</box>
<textField>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement/>
<textFieldExpression class="java.lang.Double"><![CDATA[$F{debit}]]></textFieldExpression>
</textField>
</c:detailCell>
</c:column>
<c:column width="100">
<printWhenExpression><![CDATA[new Boolean($P{showCredit})]]></printWhenExpression>
<c:columnHeader style="TableHeader" height="15">
<box leftPadding="10">
<pen lineColor="#000000"/>
<topPen lineWidth="0.5"/>
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
</box>
<staticText>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement>
<font size="12" isBold="true"/>
</textElement>
<text><![CDATA[Credit]]></text>
</staticText>
</c:columnHeader>
<c:columnFooter style="TableFooter" height="15">
<box leftPadding="10">
<pen lineColor="#000000"/>
<leftPen lineWidth="0.5"/>
</box>
<textField>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement>
<font size="12" isBold="true"/>
</textElement>
<textFieldExpression class="java.lang.Double"><![CDATA[$V{creditSum}]]></textFieldExpression>
</textField>
</c:columnFooter>
<c:detailCell height="15">
<box leftPadding="10">
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
</box>
<textField>
<reportElement x="0" y="0" width="90" height="15"/>
<textElement/>
<textFieldExpression class="java.lang.Double"><![CDATA[$F{credit}]]></textFieldExpression>
</textField>
</c:detailCell>
</c:column>
</c:columnGroup>
</c:table>
</componentElement>
</band>
</title>
</jasperReport>
- Click on Preview and it will ask you for true or false to hide or show columns in your report. This uses our old friend printWhenExpression but now it behaves correctly hiding the whole column.
Here are the results with a couple of screenshots. The first one shows all columns while the second shows only the last columns (Balance and Transaction). Note that the original with for the shifting columns (Balance and Transaction) is preserved when the columns are shifted.
This report was built starting from the TableReport in demo/samples/table directory from the JasperReports 4.0.2 distribution, however I faced several issues that I only got resolved when I hacked into the schema files especially in one of them:
$ less /Users/nestor/Downloads/jasperreports-4.0.2//build/classes/net/sf/jasperreports/components/components.xsd
BTW the
xsd URL that is used in the demo file is simply unavailable which makes it difficult to work with an IDE because you lose the autocomplete functionality.
Take a look at the totals row. It took me a while to get there just because I was trying to use "staticText" instead of "textField" which has the node "textFieldExpression" in which you can refer to variables created inside a "subDataset" (Where you define also the query and the fields mapping)
Take a look at "datasetRun". I prefer to use connection instead of datasource.
Finally see how easy is to group columns. Just the way it is supposed to be. It makes me think about (I am not going to mention the year here) when I first saw HTML. I was so excited to work with tables (years later I was and I am still fighting them when used for layout but that is a different story).
The rest of the components should be pretty obvious and the whole report by this time self explanatory.
I hope to see more people pushing for separation of concerns in the JasperReports community, after all you wouldn't send HTML code from your Servelt, would you?
