Thursday, June 09, 2011

IReport: Dynamically hide / show columns for Jasper Reports

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:
  1. 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.
  2. 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:
  1. 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);
    
  2. Create the db:
    sqlite3 balance.db < balance.sql
    
  3. 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.
  4. 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>
    
  5. 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?

18 comments:

danda said...

Good stuff. I wish your post had existed a year ago. As it was, I ended up writing my own reporting engine in PHP... simpler than jasper, but at least I could hide columns easily at runtime. :-)

donda said...

Useful stuff, indeed. But as far as i can see, the columns are hidden and other columns are shifted to their place, thus shrinking the whole table. Is there a way to keep the original table width and make columns stretch horisontally to occupy the full width? Or did i get something wrong?

Nestor Urquiza said...

@donda I just rerun the example and it did hide the columns and the rest of the columns were shifted. I tested in iReport Designer 4.0.2 in a MAC.

donda said...

Yes, that's the point: they're shifted, but their width does not change and the whole table becomes narrower. And the goal is to keep the table size the same, just like in HTML where you can assign columns width in % and have variable number of columns: they'll just rearrange the space accordingly.

Nestor Urquiza said...

Well that was not my goal. I am sorry if I did not make it clear but the requirement here is that you can hide or add columns with *fixed* width.

About dynamic width I really do not know how to make it happen.

Dayana Baby said...

Hi Guys,

For removing the space(column space) when there is no data, we can use the property of Remove Line When Blank.

Regards
Dayana Baby

Nestor Urquiza said...

@Dayana Baby
That would work if the entire column is blank. If it contains a header it will still show up even if the whole column content is empty.

That is good for rows but to "hide" a "non empty" column" you better use the technique described here .

kimber said...

this post save me alot of time tnx =)

Unknown said...

hi...good work.but i m getting one error when using this sample code like
cvc-complex-type.2.4.a: Invalid content was found starting with element 'c:table'. One of '{"http://jasperreports.sourceforge.net/jasperreports":component}' is expected.

can anyone please tell me how to solve it?

Thanks & Regards,
Kapilan.A

Nestor Urquiza said...

@kapilan I just tested this with ireport version 4.7.1 and it does work. Check the XSD for your distribution and validate the jrxml, you might be running an incompatible version (older or newer than the ones I have used so far).

Sadakar Pochampalli said...

Nice article. I am newbie to iReport..
My Questions are :
I can use input controls with in the report designer well and fine.
i.e., I'm giving true,and false as you specified.

Now, I want to upload the same report to the server and want see the preview.
What are the input controls I need to add over inputs folder for this report ?

In the report you have used boolean .. So What type of input control should I use when uploading to the server like Boolean , Single select, multiselect(list, queries).. and How do I have to pass the parameters in the table query ???

Sadakar Pochampalli said...

Solved it.. I just checked with Boolean input control.. parametername in iReport=ID in the input controls of server.

Krishna Ananthi said...

nice one. but when i tried to execute this in jasper ireport 5.5.1 i am getting error like invalid report.. can u tell me how to run ur sample code??

Nestor Urquiza said...

@Krishna I am afraid you will need to test this in the version we tested it was working. If in newer versins then it stopped working you have a perfect test case to either share with the community in Talend forums or create a ticket in jira.
Cheers,
- Nestor

Unknown said...

I am facing issues with whitespace in jasper ireport 5.5.1. I have a static text and a textfield with height 60px (getting the textfield value dynamically). If I get null value from backend, then i was unable to reduce the height of textfield. Tried using RemoveLineWhenBlank and isBlankWhenNull but didn't get solution. Can anyone please help me out.
Thanks in advance.

Nestor Urquiza said...

@Ramya I suggest you post your question in stack overflow or the current jasper reports forum. BTW these days I am producing the front end of any report from where it belongs, directly from app UI. Then if needed exported to excel, pdf etc. The technology is already there thanks to HTML/CSS/JS and if needed webkit. IMO reporting tools tried to create a language for visualization but they will continue losing adoption. The web is a mature platform and it has won. From a data visualization standpoint we should not look at reports as any special piece of the whole app but rather just as another view that in some cases presents some real time data (OLTP, predictive analysis) while in others it presents higher latency data (data marts, OLAP) .

Anjana said...

Hi Nestor,

It is very good and well understood post. I would like to understand is show hide possible with crosstab component of jasper i am using jasper 6.6.

Nestor Urquiza said...

@Anjana I am not currently using Jasper so I suggest you ask your question in stackoverflow or wherever Jasper community is gathering nowadays. Good luck!

Followers