Home > BaaN, BIRT, Infor > Variable Columns in a Report – BIRT Example

Variable Columns in a Report – BIRT Example


There was requirement for a BIRT report with variable number of columns to be displayed for every row based on certain condition. Let me take an example to expalin the requirement more precisely.

Lets take an example of project costing, There may be “n” number of ledger accounts and for every ledger account there may be “n” number of dimensions (lets take project as a dimension to simplify the example). In a static report we will have to know the number of columns at the time of design itself but here in this case we will come to know about the columns at the runtime. So we need a report design that will display the columns dynamically based on runtime data.

Use of embedded crosstab solved the purpose.
What I had to do was just to create a Group with “Ledger” as a key and insert a crosstab in Group Header or Group Footer.

The data cube will have Grouping on Ledger and Project with summary of debit and credit. Apart from this there will be a filter which will check if the Ledger account is same to print it in respective group.

Check out the rptdesign file below, for simplification purpose I am using flatfile data source.

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.15" id="1">
    <property name="createdBy">Eclipse BIRT Designer Version 2.2.1.r221_v20070924 Build &lt;2.2.0.v20070924-1550></property>
    <property name="units">in</property>
    <property name="comments">Copyright (c) 2007 &lt;&lt;Your Company Name here>></property>
    <html-property name="description">Creates a blank report with no predefined content.</html-property>
    <list-property name="userProperties">
        <structure>
            <property name="name">Data Cube.Data Set.x</property>
            <property name="type">integer</property>
            <property name="isVisible">false</property>
        </structure>
        <structure>
            <property name="name">Data Cube.Data Set.y</property>
            <property name="type">integer</property>
            <property name="isVisible">false</property>
        </structure>
    </list-property>
    <property name="Data Cube.Data Set.x">135</property>
    <property name="Data Cube.Data Set.y">80</property>
    <text-property name="displayName">Blank Report</text-property>
    <property name="iconFile">/templates/blank_report.gif</property>
    <data-sources>
        <oda-data-source extensionID="org.eclipse.datatools.connectivity.oda.flatfile" name="Data Source" id="6">
            <property name="HOME">C:\Documents and Settings\90385833\Desktop\embeddedCrosstab</property>
            <property name="DELIMTYPE">COMMA</property>
            <property name="CHARSET">UTF-8</property>
            <property name="INCLCOLUMNNAME">YES</property>
            <property name="INCLTYPELINE">NO</property>
        </oda-data-source>
    </data-sources>
    <data-sets>
        <oda-data-set extensionID="org.eclipse.datatools.connectivity.oda.flatfile.dataSet" name="Data Set" id="7">
            <structure name="cachedMetaData">
                <list-property name="resultSet">
                    <structure>
                        <property name="position">1</property>
                        <property name="name">Ledger</property>
                        <property name="dataType">string</property>
                    </structure>
                    <structure>
                        <property name="position">2</property>
                        <property name="name">Debit</property>
                        <property name="dataType">float</property>
                    </structure>
                    <structure>
                        <property name="position">3</property>
                        <property name="name">Credit</property>
                        <property name="dataType">float</property>
                    </structure>
                    <structure>
                        <property name="position">4</property>
                        <property name="name">Project</property>
                        <property name="dataType">string</property>
                    </structure>
                    <structure>
                        <property name="position">5</property>
                        <property name="name">Balance</property>
                        <property name="dataType">float</property>
                    </structure>
                </list-property>
            </structure>
            <property name="dataSource">Data Source</property>
            <list-property name="resultSet">
                <structure>
                    <property name="position">1</property>
                    <property name="name">Ledger</property>
                    <property name="nativeName">Ledger</property>
                    <property name="dataType">string</property>
                    <property name="nativeDataType">12</property>
                </structure>
                <structure>
                    <property name="position">2</property>
                    <property name="name">Debit</property>
                    <property name="nativeName">Debit</property>
                    <property name="dataType">float</property>
                    <property name="nativeDataType">8</property>
                </structure>
                <structure>
                    <property name="position">3</property>
                    <property name="name">Credit</property>
                    <property name="nativeName">Credit</property>
                    <property name="dataType">float</property>
                    <property name="nativeDataType">8</property>
                </structure>
                <structure>
                    <property name="position">4</property>
                    <property name="name">Project</property>
                    <property name="nativeName">Project</property>
                    <property name="dataType">string</property>
                    <property name="nativeDataType">12</property>
                </structure>
                <structure>
                    <property name="position">5</property>
                    <property name="name">Balance</property>
                    <property name="nativeName">Balance</property>
                    <property name="dataType">float</property>
                    <property name="nativeDataType">8</property>
                </structure>
            </list-property>
            <property name="queryText">select "Ledger", "Debit", "Credit", "Project", "Balance"
 from embeddedCrosstab.csv : {"Ledger","Ledger",STRING;"Debit","Debit",DOUBLE;"Credit","Credit",
DOUBLE;"Project","Project",STRING;"Balance","Balance",DOUBLE}</property>
        </oda-data-set>
    </data-sets>
    <cubes>
        <tabular-cube name="Data Cube" id="24">
            <property name="defaultMeasureGroup">Summary Field</property>
            <property name="dimensions">
                <tabular-dimension name="Group" id="28">
                    <property name="defaultHierarchy">NewTabularHierarchy</property>
                    <property name="hierarchies">
                        <tabular-hierarchy name="NewTabularHierarchy" id="29">
                            <property name="levels">
                                <tabular-level name="Project" id="30">
                                    <property name="dataType">string</property>
                                    <property name="columnName">Project</property>
                                </tabular-level>
                            </property>
                        </tabular-hierarchy>
                    </property>
                </tabular-dimension>
                <tabular-dimension name="Group1" id="31">
                    <property name="defaultHierarchy">NewTabularHierarchy1</property>
                    <property name="hierarchies">
                        <tabular-hierarchy name="NewTabularHierarchy1" id="32">
                            <property name="levels">
                                <tabular-level name="Ledger" id="33">
                                    <property name="dataType">string</property>
                                    <property name="levelType">dynamic</property>
                                    <property name="columnName">Ledger</property>
                                </tabular-level>
                            </property>
                        </tabular-hierarchy>
                    </property>
                </tabular-dimension>
            </property>
            <property name="measureGroups">
                <tabular-measure-group name="Summary Field" id="25">
                    <property name="measures">
                        <tabular-measure name="Debit" id="26">
                            <expression name="measureExpression">dataSetRow["Debit"]</expression>
                            <property name="dataType">float</property>
                        </tabular-measure>
                        <tabular-measure name="Credit" id="27">
                            <expression name="measureExpression">dataSetRow["Credit"]</expression>
                            <property name="dataType">float</property>
                        </tabular-measure>
                    </property>
                </tabular-measure-group>
            </property>
            <property name="dataSet">Data Set</property>
        </tabular-cube>
    </cubes>
    <styles>
        <style name="crosstab-cell" id="4">
            <property name="borderBottomColor">#CCCCCC</property>
            <property name="borderBottomStyle">solid</property>
            <property name="borderBottomWidth">1pt</property>
            <property name="borderLeftColor">#CCCCCC</property>
            <property name="borderLeftStyle">solid</property>
            <property name="borderLeftWidth">1pt</property>
            <property name="borderRightColor">#CCCCCC</property>
            <property name="borderRightStyle">solid</property>
            <property name="borderRightWidth">1pt</property>
            <property name="borderTopColor">#CCCCCC</property>
            <property name="borderTopStyle">solid</property>
            <property name="borderTopWidth">1pt</property>
        </style>
        <style name="crosstab" id="5">
            <property name="borderBottomColor">#CCCCCC</property>
            <property name="borderBottomStyle">solid</property>
            <property name="borderBottomWidth">1pt</property>
            <property name="borderLeftColor">#CCCCCC</property>
            <property name="borderLeftStyle">solid</property>
            <property name="borderLeftWidth">1pt</property>
            <property name="borderRightColor">#CCCCCC</property>
            <property name="borderRightStyle">solid</property>
            <property name="borderRightWidth">1pt</property>
            <property name="borderTopColor">#CCCCCC</property>
            <property name="borderTopStyle">solid</property>
            <property name="borderTopWidth">1pt</property>
        </style>
    </styles>
    <page-setup>
        <simple-master-page name="Simple MasterPage" id="2">
            <page-header>
                <image id="105">
                    <property name="source">embed</property>
                    <property name="imageName">nk.bmp</property>
                </image>
            </page-header>
            <page-footer>
                <text id="3">
                    <property name="contentType">html</property>
                    <text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
                </text>
            </page-footer>
        </simple-master-page>
    </page-setup>
    <body>
        <table id="8">
            <property name="width">100%</property>
            <property name="dataSet">Data Set</property>
            <list-property name="boundDataColumns">
                <structure>
                    <property name="name">Ledger</property>
                    <expression name="expression">dataSetRow["Ledger"]</expression>
                    <property name="dataType">string</property>
                </structure>
                <structure>
                    <property name="name">Debit</property>
                    <expression name="expression">dataSetRow["Debit"]</expression>
                    <property name="dataType">float</property>
                </structure>
                <structure>
                    <property name="name">Credit</property>
                    <expression name="expression">dataSetRow["Credit"]</expression>
                    <property name="dataType">float</property>
                </structure>
                <structure>
                    <property name="name">Project</property>
                    <expression name="expression">dataSetRow["Project"]</expression>
                    <property name="dataType">string</property>
                </structure>
                <structure>
                    <property name="name">Balance</property>
                    <expression name="expression">dataSetRow["Balance"]</expression>
                    <property name="dataType">float</property>
                </structure>
            </list-property>
            <column id="15"/>
            <header>
                <row id="9">
                    <cell id="10">
                        <label id="16">
                            <property name="backgroundColor">#B5FDBA</property>
                            <property name="fontWeight">bold</property>
                            <text-property name="text">Variable Columns for Rows - BIRT Example 1 </text-property>
                        </label>
                    </cell>
                </row>
            </header>
            <group id="18">
                <property name="groupName">NewTableGroup1</property>
                <property name="interval">none</property>
                <property name="sortDirection">asc</property>
                <expression name="keyExpr">row["Ledger"]</expression>
                <structure name="toc">
                    <expression name="expressionValue">row["Ledger"]</expression>
                </structure>
                <property name="repeatHeader">true</property>
                <property name="hideDetail">false</property>
                <property name="pageBreakAfter">auto</property>
                <property name="pageBreakBefore">auto</property>
                <property name="pageBreakInside">auto</property>
                <header>
                    <row id="84">
                        <cell id="85">
                            <data id="86">
                                <property name="backgroundColor">#FFFFD7</property>
                                <property name="fontWeight">bold</property>
                                <property name="resultSetColumn">Ledger</property>
                            </data>
                        </cell>
                    </row>
                </header>
                <footer>
                    <row id="21">
                        <cell id="22">
                            <extended-item extensionName="Crosstab" id="63">
                                <property name="measureDirection">vertical</property>
                                <property name="measures">
                                    <extended-item extensionName="MeasureView" id="74">
                                        <property name="measure">Debit</property>
                                        <property name="detail">
                                            <extended-item extensionName="CrosstabCell" id="75">
                                                <property name="content">
                                                    <data name="Debit" id="77">
                                                        <property name="resultSetColumn">Debit</property>
                                                    </data>
                                                </property>
                                            </extended-item>
                                        </property>
                                        <property name="aggregations">
                                            <extended-item extensionName="AggregationCell" id="88">
                                                <property name="aggregationOnRow">Group1/Ledger</property>
                                                <property name="content">
                                                    <data id="89">
                                                        <property name="resultSetColumn">Debit_Group1/Ledger</property>
                                                    </data>
                                                </property>
                                            </extended-item>
                                        </property>
                                        <property name="header">
                                            <extended-item extensionName="CrosstabCell" id="76">
                                                <property name="content">
                                                    <label id="78">
                                                        <property name="backgroundColor">#DFFFFF</property>
                                                        <text-property name="text">Debit</text-property>
                                                    </label>
                                                </property>
                                            </extended-item>
                                        </property>
                                    </extended-item>
                                    <extended-item extensionName="MeasureView" id="79">
                                        <property name="measure">Credit</property>
                                        <property name="detail">
                                            <extended-item extensionName="CrosstabCell" id="80">
                                                <property name="content">
                                                    <data name="Credit" id="82">
                                                        <property name="resultSetColumn">Credit</property>
                                                    </data>
                                                </property>
                                            </extended-item>
                                        </property>
                                        <property name="aggregations">
                                            <extended-item extensionName="AggregationCell" id="90">
                                                <property name="aggregationOnRow">Group1/Ledger</property>
                                                <property name="content">
                                                    <data id="91">
                                                        <property name="resultSetColumn">Credit_Group1/Ledger</property>
                                                    </data>
                                                </property>
                                            </extended-item>
                                        </property>
                                        <property name="header">
                                            <extended-item extensionName="CrosstabCell" id="81">
                                                <property name="content">
                                                    <label id="83">
                                                        <property name="backgroundColor">#DFFFFF</property>
                                                        <text-property name="text">Credit</text-property>
                                                    </label>
                                                </property>
                                            </extended-item>
                                        </property>
                                    </extended-item>
                                </property>
                                <property name="rows">
                                    <extended-item extensionName="CrosstabView" id="64">
                                        <property name="views">
                                            <extended-item extensionName="DimensionView" id="65">
                                                <property name="dimension">Group1</property>
                                                <property name="levels">
                                                    <extended-item extensionName="LevelView" name="NewLevel View" id="66">
                                                        <property name="level">Group1/Ledger</property>
                                                        <property name="filter">
                                                            <filter-condition-element>
                                                                <expression name="expr">data["Ledger"]</expression>
                                                                <property name="operator">eq</property>
                                                                <simple-property-list name="value1">
                                                                    <value>row["Ledger"]</value>
                                                                </simple-property-list>
                                                            </filter-condition-element>
                                                        </property>
                                                        <property name="member">
                                                            <extended-item extensionName="CrosstabCell" id="67">
                                                                <property name="content">
                                                                    <data name="Ledger" id="68">
                                                                        <property name="backgroundColor">#DFFFFF</property>
                                                                        <property name="resultSetColumn">Ledger</property>
                                                                    </data>
                                                                </property>
                                                                <property name="width">1in</property>
                                                            </extended-item>
                                                        </property>
                                                    </extended-item>
                                                </property>
                                            </extended-item>
                                        </property>
                                    </extended-item>
                                </property>
                                <property name="columns">
                                    <extended-item extensionName="CrosstabView" id="69">
                                        <property name="grandTotal">
                                            <extended-item extensionName="CrosstabCell" id="87">
                                                <property name="content">
                                                    <label id="92">
                                                        <property name="backgroundColor">#DFFFFF</property>
                                                        <text-property name="text">Grand Total</text-property>
                                                    </label>
                                                </property>
                                            </extended-item>
                                        </property>
                                        <property name="views">
                                            <extended-item extensionName="DimensionView" id="70">
                                                <property name="dimension">Group</property>
                                                <property name="levels">
                                                    <extended-item extensionName="LevelView" name="NewLevel View1" id="71">
                                                        <property name="level">Group/Project</property>
                                                        <property name="member">
                                                            <extended-item extensionName="CrosstabCell" id="72">
                                                                <property name="content">
                                                                    <data name="Project" id="73">
                                                                        <property name="backgroundColor">#DFFFFF</property>
                                                                        <property name="resultSetColumn">Project</property>
                                                                    </data>
                                                                </property>
                                                            </extended-item>
                                                        </property>
                                                    </extended-item>
                                                </property>
                                            </extended-item>
                                        </property>
                                    </extended-item>
                                </property>
                                <property name="cube">Data Cube</property>
                                <list-property name="boundDataColumns">
                                    <structure>
                                        <property name="name">Ledger</property>
                                        <expression name="expression">dimension["Group1"]["Ledger"]</expression>
                                        <property name="dataType">string</property>
                                    </structure>
                                    <structure>
                                        <property name="name">Project</property>
                                        <expression name="expression">dimension["Group"]["Project"]</expression>
                                        <property name="dataType">string</property>
                                    </structure>
                                    <structure>
                                        <property name="name">Debit</property>
                                        <expression name="expression">measure["Debit"]</expression>
                                        <property name="dataType">float</property>
                                    </structure>
                                    <structure>
                                        <property name="name">Credit</property>
                                        <expression name="expression">measure["Credit"]</expression>
                                        <property name="dataType">float</property>
                                    </structure>
                                    <structure>
                                        <property name="name">Debit_Group1/Ledger</property>
                                        <expression name="expression">measure["Debit"]</expression>
                                        <property name="dataType">float</property>
                                        <simple-property-list name="aggregateOn">
                                            <value>Group1/Ledger</value>
                                        </simple-property-list>
                                        <property name="aggregateFunction">sum</property>
                                    </structure>
                                    <structure>
                                        <property name="name">Credit_Group1/Ledger</property>
                                        <expression name="expression">measure["Credit"]</expression>
                                        <property name="dataType">float</property>
                                        <simple-property-list name="aggregateOn">
                                            <value>Group1/Ledger</value>
                                        </simple-property-list>
                                        <property name="aggregateFunction">sum</property>
                                    </structure>
                                    <structure>
                                        <property name="name">Debit_Group/Project</property>
                                        <expression name="expression">measure["Debit"]</expression>
                                        <property name="dataType">float</property>
                                        <simple-property-list name="aggregateOn">
                                            <value>Group/Project</value>
                                        </simple-property-list>
                                        <property name="aggregateFunction">sum</property>
                                    </structure>
                                    <structure>
                                        <property name="name">Credit_Group/Project</property>
                                        <expression name="expression">measure["Credit"]</expression>
                                        <property name="dataType">float</property>
                                        <simple-property-list name="aggregateOn">
                                            <value>Group/Project</value>
                                        </simple-property-list>
                                        <property name="aggregateFunction">sum</property>
                                    </structure>
                                    <structure>
                                        <property name="name">Debit_1</property>
                                        <expression name="expression">measure["Debit"]</expression>
                                        <property name="dataType">float</property>
                                        <property name="aggregateFunction">sum</property>
                                    </structure>
                                    <structure>
                                        <property name="name">Credit_1</property>
                                        <expression name="expression">measure["Credit"]</expression>
                                        <property name="dataType">float</property>
                                        <property name="aggregateFunction">sum</property>
                                    </structure>
                                </list-property>
                            </extended-item>
                        </cell>
                    </row>
                </footer>
            </group>
            <footer>
                <row id="13">
                    <cell id="14"/>
                </row>
            </footer>
        </table>
    </body>
    <list-property name="images">
        <structure>
            <property name="name">logo.jpg</property>
            <property name="data">            </property>
        </structure>
        <structure>
            <property name="name">nk.bmp</property>
            <property name="data">
            
            </property>
        </structure>
    </list-property>
</report>


Attached is the output report

Advertisements
Categories: BaaN, BIRT, Infor Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: