BIRT: Showing Top 10 and Still Displaying Total Count

A question on the BIRT Exchange asked how could you display the top 10, but still show the total count for the entire table.

This is fairly easy, and uses a combination of Aggregations, the Visibility Expression, Sorts, and the row._rownum property of a table.

So, lets say I have a query like:

select 
    CLASSICMODELS.CUSTOMERS.CUSTOMERNAME,
    CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER,
    sum(CLASSICMODELS.ORDERDETAILS.PRICEEACH * CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED) totalOrders
from 
    CLASSICMODELS.CUSTOMERS,
    CLASSICMODELS.ORDERS,
    CLASSICMODELS.ORDERDETAILS
where
    CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
    and CLASSICMODELS.ORDERS.CUSTOMERNUMBER = CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER
group by
    CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER,
    CLASSICMODELS.CUSTOMERS.CUSTOMERNAME

My total sales for each customer is handled in the query. What I want is to display the Top 10 Customers. So, I will create a data set, and drag that data set over to the report designer. Once there, I will add a sort in descending order based on the totalOrders column. This will list all customers in descending order based on total sales, leaving the top buying customers at the top.

If I add a Count Aggregation to the table and put it in the footer, it will display the total count of customers. The next step is to filter on the Top 10. The way I would do this is select the Detail row in the table, and set a Visibility expression like so:

row.__rownum > 9

__rownum is based on the order it is in the Tables binding, and gets set on fetch. So why use a Visibility Expression instead of a Filter? Because Filter does not have access to the __rownum property, and it would affect the Tables aggregation, so there would be an inaccurate count of customers.

The resulting Top 10 can now be used in a Dashboard by being called as a Reportlet, and the Total Count aggregation can be set as the drill down link for the full list.

The example reports XML is below.

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.22" id="1">
    <property name="createdBy">Eclipse BIRT Designer Version 3.7.1.v20110905 Build &lt;3.7.1.v20110905-1820></property>
    <property name="units">in</property>
    <property name="iconFile">/templates/blank_report.gif</property>
    <property name="bidiLayoutOrientation">ltr</property>
    <property name="imageDPI">96</property>
    <data-sources>
        <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source" id="7">
            <list-property name="privateDriverProperties">
                <ex-property>
                    <name>contentBidiFormatStr</name>
                    <value>ILYNN</value>
                </ex-property>
                <ex-property>
                    <name>metadataBidiFormatStr</name>
                    <value>ILYNN</value>
                </ex-property>
            </list-property>
            <property name="odaDriverClass">org.eclipse.birt.report.data.oda.sampledb.Driver</property>
            <property name="odaURL">jdbc:classicmodels:sampledb</property>
            <property name="odaUser">ClassicModels</property>
        </oda-data-source>
    </data-sources>
    <data-sets>
        <oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="totalDataSet" id="8">
            <list-property name="columnHints">
                <structure>
                    <property name="columnName">CUSTOMERNAME</property>
                    <property name="analysis">attribute</property>
                    <text-property name="displayName">CUSTOMERNAME</text-property>
                    <text-property name="heading">CUSTOMERNAME</text-property>
                </structure>
                <structure>
                    <property name="columnName">CUSTOMERNUMBER</property>
                    <property name="analysis">measure</property>
                    <text-property name="displayName">CUSTOMERNUMBER</text-property>
                    <text-property name="heading">CUSTOMERNUMBER</text-property>
                </structure>
                <structure>
                    <property name="columnName">TOTALORDERS</property>
                    <property name="analysis">measure</property>
                    <text-property name="displayName">TOTALORDERS</text-property>
                    <text-property name="heading">TOTALORDERS</text-property>
                </structure>
            </list-property>
            <list-property name="parameters"/>
            <structure name="cachedMetaData">
                <list-property name="resultSet">
                    <structure>
                        <property name="position">1</property>
                        <property name="name">CUSTOMERNAME</property>
                        <property name="dataType">string</property>
                    </structure>
                    <structure>
                        <property name="position">2</property>
                        <property name="name">CUSTOMERNUMBER</property>
                        <property name="dataType">integer</property>
                    </structure>
                    <structure>
                        <property name="position">3</property>
                        <property name="name">TOTALORDERS</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">CUSTOMERNAME</property>
                    <property name="nativeName">CUSTOMERNAME</property>
                    <property name="dataType">string</property>
                    <property name="nativeDataType">12</property>
                </structure>
                <structure>
                    <property name="position">2</property>
                    <property name="name">CUSTOMERNUMBER</property>
                    <property name="nativeName">CUSTOMERNUMBER</property>
                    <property name="dataType">integer</property>
                    <property name="nativeDataType">4</property>
                </structure>
                <structure>
                    <property name="position">3</property>
                    <property name="name">TOTALORDERS</property>
                    <property name="nativeName">TOTALORDERS</property>
                    <property name="dataType">float</property>
                    <property name="nativeDataType">8</property>
                </structure>
            </list-property>
            <xml-property name="queryText"><![CDATA[select 
    CLASSICMODELS.CUSTOMERS.CUSTOMERNAME,
    CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER,
    sum(CLASSICMODELS.ORDERDETAILS.PRICEEACH * CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED) totalOrders
from 
    CLASSICMODELS.CUSTOMERS,
    CLASSICMODELS.ORDERS,
    CLASSICMODELS.ORDERDETAILS
where
    CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
    and CLASSICMODELS.ORDERS.CUSTOMERNUMBER = CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER
group by
    CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER,
    CLASSICMODELS.CUSTOMERS.CUSTOMERNAME
    ]]></xml-property>
            <xml-property name="designerValues"><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<model:DesignValues xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design" xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">
  <Version>2.0</Version>
  <design:ResultSets derivedMetaData="true">
    <design:resultSetDefinitions>
      <design:resultSetColumns>
        <design:resultColumnDefinitions>
          <design:attributes>
            <design:identifier>
              <design:name>CUSTOMERNAME</design:name>
              <design:position>1</design:position>
            </design:identifier>
            <design:nativeDataTypeCode>12</design:nativeDataTypeCode>
            <design:precision>50</design:precision>
            <design:scale>0</design:scale>
            <design:nullability>Nullable</design:nullability>
            <design:uiHints>
              <design:displayName>CUSTOMERNAME</design:displayName>
            </design:uiHints>
          </design:attributes>
          <design:usageHints>
            <design:label>CUSTOMERNAME</design:label>
            <design:formattingHints>
              <design:displaySize>50</design:displaySize>
            </design:formattingHints>
          </design:usageHints>
        </design:resultColumnDefinitions>
        <design:resultColumnDefinitions>
          <design:attributes>
            <design:identifier>
              <design:name>CUSTOMERNUMBER</design:name>
              <design:position>2</design:position>
            </design:identifier>
            <design:nativeDataTypeCode>4</design:nativeDataTypeCode>
            <design:precision>10</design:precision>
            <design:scale>0</design:scale>
            <design:nullability>Nullable</design:nullability>
            <design:uiHints>
              <design:displayName>CUSTOMERNUMBER</design:displayName>
            </design:uiHints>
          </design:attributes>
          <design:usageHints>
            <design:label>CUSTOMERNUMBER</design:label>
            <design:formattingHints>
              <design:displaySize>11</design:displaySize>
            </design:formattingHints>
          </design:usageHints>
        </design:resultColumnDefinitions>
        <design:resultColumnDefinitions>
          <design:attributes>
            <design:identifier>
              <design:name>ORDERNUMBER</design:name>
              <design:position>3</design:position>
            </design:identifier>
            <design:nativeDataTypeCode>4</design:nativeDataTypeCode>
            <design:precision>10</design:precision>
            <design:scale>0</design:scale>
            <design:nullability>Nullable</design:nullability>
            <design:uiHints>
              <design:displayName>ORDERNUMBER</design:displayName>
            </design:uiHints>
          </design:attributes>
          <design:usageHints>
            <design:label>ORDERNUMBER</design:label>
            <design:formattingHints>
              <design:displaySize>11</design:displaySize>
            </design:formattingHints>
          </design:usageHints>
        </design:resultColumnDefinitions>
        <design:resultColumnDefinitions>
          <design:attributes>
            <design:identifier>
              <design:name>PRICEEACH</design:name>
              <design:position>4</design:position>
            </design:identifier>
            <design:nativeDataTypeCode>8</design:nativeDataTypeCode>
            <design:precision>15</design:precision>
            <design:scale>0</design:scale>
            <design:nullability>Nullable</design:nullability>
            <design:uiHints>
              <design:displayName>PRICEEACH</design:displayName>
            </design:uiHints>
          </design:attributes>
          <design:usageHints>
            <design:label>PRICEEACH</design:label>
            <design:formattingHints>
              <design:displaySize>22</design:displaySize>
            </design:formattingHints>
          </design:usageHints>
        </design:resultColumnDefinitions>
        <design:resultColumnDefinitions>
          <design:attributes>
            <design:identifier>
              <design:name>QUANTITYORDERED</design:name>
              <design:position>5</design:position>
            </design:identifier>
            <design:nativeDataTypeCode>4</design:nativeDataTypeCode>
            <design:precision>10</design:precision>
            <design:scale>0</design:scale>
            <design:nullability>Nullable</design:nullability>
            <design:uiHints>
              <design:displayName>QUANTITYORDERED</design:displayName>
            </design:uiHints>
          </design:attributes>
          <design:usageHints>
            <design:label>QUANTITYORDERED</design:label>
            <design:formattingHints>
              <design:displaySize>11</design:displaySize>
            </design:formattingHints>
          </design:usageHints>
        </design:resultColumnDefinitions>
      </design:resultSetColumns>
      <design:criteria/>
    </design:resultSetDefinitions>
  </design:ResultSets>
</model:DesignValues>]]></xml-property>
        </oda-data-set>
    </data-sets>
    <styles>
        <style name="report" id="4">
            <property name="fontFamily">sans-serif</property>
            <property name="fontSize">10pt</property>
        </style>
        <style name="crosstab-cell" 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>
        <style name="crosstab" id="6">
            <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-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="77">
            <property name="dataSet">totalDataSet</property>
            <list-property name="boundDataColumns">
                <structure>
                    <property name="name">CUSTOMERNAME</property>
                    <text-property name="displayName">CUSTOMERNAME</text-property>
                    <expression name="expression" type="javascript">dataSetRow["CUSTOMERNAME"]</expression>
                    <property name="dataType">string</property>
                </structure>
                <structure>
                    <property name="name">CUSTOMERNUMBER</property>
                    <text-property name="displayName">CUSTOMERNUMBER</text-property>
                    <expression name="expression" type="javascript">dataSetRow["CUSTOMERNUMBER"]</expression>
                    <property name="dataType">integer</property>
                </structure>
                <structure>
                    <property name="name">TOTALORDERS</property>
                    <text-property name="displayName">TOTALORDERS</text-property>
                    <expression name="expression" type="javascript">dataSetRow["TOTALORDERS"]</expression>
                    <property name="dataType">float</property>
                </structure>
                <structure>
                    <property name="name">TotalCustomerCount</property>
                    <property name="dataType">integer</property>
                    <property name="aggregateFunction">COUNT</property>
                    <list-property name="arguments">
                        <structure>
                            <property name="name">Expression</property>
                        </structure>
                    </list-property>
                    <property name="allowExport">true</property>
                </structure>
            </list-property>
            <list-property name="sort">
                <structure>
                    <expression name="key" type="javascript">row["TOTALORDERS"]</expression>
                    <property name="direction">desc</property>
                    <property name="strength">-1</property>
                </structure>
            </list-property>
            <column id="103"/>
            <column id="96"/>
            <column id="97"/>
            <column id="98"/>
            <header>
                <row id="78">
                    <property name="backgroundColor">#808080</property>
                    <property name="fontWeight">bold</property>
                    <property name="textAlign">left</property>
                    <cell id="100">
                        <label id="105">
                            <text-property name="text">LINE NUMBER</text-property>
                        </label>
                    </cell>
                    <cell id="79">
                        <label id="82">
                            <text-property name="text">CUSTOMER NUMBER</text-property>
                        </label>
                    </cell>
                    <cell id="81">
                        <label id="99">
                            <text-property name="text">CUSTOMER NAME</text-property>
                        </label>
                    </cell>
                    <cell id="83">
                        <label id="84">
                            <text-property name="text">TOTAL ORDERS</text-property>
                        </label>
                    </cell>
                </row>
            </header>
            <detail>
                <row id="85">
                    <list-property name="highlightRules">
                        <structure>
                            <property name="operator">eq</property>
                            <property name="backgroundColor">#C0C0C0</property>
                            <property name="fontSize">10pt</property>
                            <expression name="testExpr" type="javascript">row.__rownum % 2</expression>
                            <simple-property-list name="value1">
                                <value type="javascript">1</value>
                            </simple-property-list>
                        </structure>
                    </list-property>
                    <list-property name="visibility">
                        <structure>
                            <property name="format">all</property>
                            <expression name="valueExpr" type="javascript">row.__rownum > 9</expression>
                        </structure>
                    </list-property>
                    <cell id="101">
                        <text-data id="104">
                            <expression name="valueExpr">row.__rownum + 1</expression>
                            <property name="contentType">html</property>
                        </text-data>
                    </cell>
                    <cell id="86">
                        <data id="89">
                            <property name="resultSetColumn">CUSTOMERNUMBER</property>
                        </data>
                    </cell>
                    <cell id="88">
                        <data id="87">
                            <property name="resultSetColumn">CUSTOMERNAME</property>
                        </data>
                    </cell>
                    <cell id="90">
                        <data id="91">
                            <structure name="numberFormat">
                                <property name="category">Currency</property>
                                <property name="pattern">$#,##0.00{RoundingMode=HALF_UP}</property>
                            </structure>
                            <property name="resultSetColumn">TOTALORDERS</property>
                        </data>
                    </cell>
                </row>
            </detail>
            <footer>
                <row id="92">
                    <cell id="102">
                        <property name="backgroundColor">yellow</property>
                        <property name="fontWeight">bold</property>
                        <label id="106">
                            <text-property name="text">Total Customers:</text-property>
                        </label>
                    </cell>
                    <cell id="93">
                        <data id="107">
                            <property name="resultSetColumn">TotalCustomerCount</property>
                        </data>
                    </cell>
                    <cell id="94"/>
                    <cell id="95"/>
                </row>
            </footer>
        </table>
    </body>
</report>