How to add serial number after grouping the rows in table using BIRT design report?

Question

I want to add serial number column after grouping the rows based on specific field. ex. Existing data

Serial No| Date       | Amount 
1        | 10/10/2020 | 2500
2        | 10/10/2020 | 3400
3        | 10/10/2020 | 1100
4        | 8/12/2021  | 2500

Expected Data

Serial No| Date       | Amount
1        | 10/10/2020 | 7000
2        | 8/12/2021  | 2500

Need help for Serial Number.

Based on my changes I can see

Serial No| Date       | Amount
1        | 10/10/2020 | 7000
1        | 8/12/2021  | 2500

Its adding the serial number inside grouping the data. But I need it based on grouped record.

Answer 1

The answer has two parts:

1. Group values by date
   • Create a Data Set with "Date" (Date) and "Value" (Integer)
     • Drag&Drop "Date" into "Groups (Dimensions)" and select "Regular Group"
     • Drag&Drop "Amount" into "Summary Fields"
   • Drag&Drop the new Data Cube into the layout. Now you should have the values summarized by date.
2. Add row number to crosstab
   • In the "initialize" script of the report (you can click in the layout at some empty place and select the "Script" tab to find it) you have to register a counter by adding:

     myCrosstabCount = 0;
     

   • Insert a new Data object just above the [Date] in the crosstab in the layout. Call it e.g., "MyCounter" and set its Data Type to Integer.
   • Set MyCounter's expression to this:

   myCrosstabCount++;
   myCrosstabCount;
   

   • Put [MyCounter] and [Date] into a 2x1 grid, so that they are side-by-side

Here a minimal standalone example with a scripted data source. Store it as .rptdesign file and run it for instance in BIRT Report Designer 4.13.0

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.24" id="1">
    <property name="createdBy">Eclipse BIRT Designer Version 4.13.0.v202303021806</property>
    <list-property name="userProperties">
        <structure>
            <property name="name">Data Cube2.getSampleData.x</property>
            <property name="type">integer</property>
            <property name="isVisible">false</property>
        </structure>
        <structure>
            <property name="name">Data Cube2.getSampleData.y</property>
            <property name="type">integer</property>
            <property name="isVisible">false</property>
        </structure>
    </list-property>
    <property name="Data Cube2.getSampleData.x">135</property>
    <property name="Data Cube2.getSampleData.y">80</property>
    <property name="units">in</property>
    <method name="initialize"><![CDATA[myCrosstabCount = 0;
]]></method>
    <property name="iconFile">/templates/blank_report.gif</property>
    <property name="bidiLayoutOrientation">ltr</property>
    <property name="imageDPI">96</property>
    <data-sources>
        <script-data-source name="scriptedDataSource" id="4525"/>
    </data-sources>
    <data-sets>
        <script-data-set name="getSampleData" id="4543">
            <list-property name="resultSetHints">
                <structure>
                    <property name="position">1</property>
                    <property name="name">Date</property>
                    <property name="dataType">date</property>
                </structure>
                <structure>
                    <property name="position">2</property>
                    <property name="name">Amount</property>
                    <property name="dataType">integer</property>
                </structure>
            </list-property>
            <list-property name="columnHints">
                <structure>
                    <property name="columnName">Date</property>
                    <text-property name="displayName">Date</text-property>
                </structure>
                <structure>
                    <property name="columnName">Amount</property>
                    <text-property name="displayName">Amount</text-property>
                </structure>
            </list-property>
            <structure name="cachedMetaData">
                <list-property name="resultSet">
                    <structure>
                        <property name="position">1</property>
                        <property name="name">Date</property>
                        <property name="dataType">date</property>
                    </structure>
                    <structure>
                        <property name="position">2</property>
                        <property name="name">Amount</property>
                        <property name="dataType">integer</property>
                    </structure>
                </list-property>
            </structure>
            <property name="dataSource">scriptedDataSource</property>
            <method name="open"><![CDATA[count = 0;
colA = ['2020-10-10','2020-10-10','2020-10-10','2021-12-08'];
colB = [2500,3400,1100,2500];]]></method>
            <method name="fetch"><![CDATA[if(count < 4){    
    row["Date"] = colA[count];
    row["Amount"] = colB[count];        
    count++;
    return true;
}
return false;


]]></method>
        </script-data-set>
    </data-sets>
    <cubes>
        <tabular-cube name="Data Cube" id="4679">
            <property name="dimensions">
                <tabular-dimension name="Group" id="4680">
                    <property name="isTimeType">false</property>
                    <property name="defaultHierarchy">NewTabularHierarchy1</property>
                    <property name="hierarchies">
                        <tabular-hierarchy name="NewTabularHierarchy1" id="4681">
                            <property name="levels">
                                <tabular-level name="Date" id="4682">
                                    <property name="dataType">date</property>
                                    <property name="columnName">Date</property>
                                </tabular-level>
                            </property>
                        </tabular-hierarchy>
                    </property>
                </tabular-dimension>
            </property>
            <property name="measureGroups">
                <tabular-measure-group name="Summary Field" id="4683">
                    <property name="measures">
                        <tabular-measure name="Amount" id="4684">
                            <expression name="measureExpression" type="javascript">dataSetRow["Amount"]</expression>
                            <property name="dataType">integer</property>
                        </tabular-measure>
                    </property>
                </tabular-measure-group>
            </property>
            <property name="dataSet">getSampleData</property>
        </tabular-cube>
    </cubes>
    <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>
        <extended-item extensionName="Crosstab" extensionVersion="3.7.0" id="4685">
            <property name="cube">Data Cube</property>
            <property name="measures">
                <extended-item extensionName="MeasureView" id="4692">
                    <property name="measure">Amount</property>
                    <property name="detail">
                        <extended-item extensionName="AggregationCell" id="4693">
                            <property name="aggregationOnRow">Group/Date</property>
                            <property name="content">
                                <data id="4694">
                                    <property name="resultSetColumn">Amount_Group/Date</property>
                                </data>
                            </property>
                        </extended-item>
                    </property>
                    <property name="header">
                        <extended-item extensionName="CrosstabCell" id="4695">
                            <property name="content">
                                <label id="4696">
                                    <property name="textAlign">left</property>
                                    <text-property name="text">Amount</text-property>
                                </label>
                            </property>
                        </extended-item>
                    </property>
                </extended-item>
            </property>
            <property name="rows">
                <extended-item extensionName="CrosstabView" id="4687">
                    <property name="views">
                        <extended-item extensionName="DimensionView" id="4688">
                            <property name="dimension">Group</property>
                            <property name="levels">
                                <extended-item extensionName="LevelView" name="NewLevel View" id="4689">
                                    <property name="level">Group/Date</property>
                                    <property name="member">
                                        <extended-item extensionName="CrosstabCell" id="4690">
                                            <property name="content">
                                                <grid id="4699">
                                                    <column id="4700"/>
                                                    <column id="4701"/>
                                                    <row id="4702">
                                                        <cell id="4703">
                                                            <data id="4698">
                                                                <property name="textAlign">left</property>
                                                                <property name="resultSetColumn">Column Binding</property>
                                                            </data>
                                                        </cell>
                                                        <cell id="4704">
                                                            <data name="Date" id="4691">
                                                                <property name="textAlign">left</property>
                                                                <property name="resultSetColumn">Date</property>
                                                            </data>
                                                        </cell>
                                                    </row>
                                                </grid>
                                            </property>
                                        </extended-item>
                                    </property>
                                </extended-item>
                            </property>
                        </extended-item>
                    </property>
                </extended-item>
            </property>
            <property name="header">
                <extended-item extensionName="CrosstabCell" id="4686">
                    <property name="content">
                        <label id="4697">
                            <property name="textAlign">left</property>
                            <text-property name="text">Date</text-property>
                        </label>
                    </property>
                </extended-item>
            </property>
            <list-property name="boundDataColumns">
                <structure>
                    <property name="name">Date</property>
                    <expression name="expression">dimension["Group"]["Date"]</expression>
                    <property name="dataType">date</property>
                </structure>
                <structure>
                    <property name="name">Amount_Group/Date</property>
                    <expression name="expression">measure["Amount"]</expression>
                    <property name="dataType">integer</property>
                    <simple-property-list name="aggregateOn">
                        <value>Group/Date</value>
                    </simple-property-list>
                    <property name="aggregateFunction">SUM</property>
                </structure>
                <structure>
                    <property name="name">Column Binding</property>
                    <text-property name="displayName">MyCounter</text-property>
                    <expression name="expression" type="javascript">myCrosstabCount++;&#13;
myCrosstabCount;</expression>
                    <property name="dataType">integer</property>
                </structure>
            </list-property>
        </extended-item>
    </body>
</report>