BIRT Duplicate Rows

Currently BIRT supports suppressing duplicate columns values. To do this you can select the table column and in the general properties click the Suppress duplicates checkbox.



This will have an effect similar to the following image.


You will notice that the number of rows generated in the table has not changed, but no order number is repeated. If your table does contain duplicate rows, meaning each column in the table has the same data as the row before and you suppress duplicates on each column, the number of rows displayed by BIRT will be reduced. For example assume your table is tied to a dataset that has two columns and every row contains the same data.



If this dataset is bound to a table and both columns are set to suppress duplicates the following will be displayed.



The actual number of rows is not reduced but the table only shows the value for one row. In the generated HTML a TR element is created for each empty row with empty values. If you use this approach make sure to set the padding on the table row, cell, and data items to 0 or you will get unwanted space for empty rows. It is also important to realize that these empty rows are counted if you are use the page break interval property to handle page breaks. As a side note, when dealing with duplicate rows it is often better to allow the database to handle culling repeated rows, but in some cases like flat file data sources this may not be an option.

Another option that can be used to hide duplicate rows is scripting and a visibility expression.

Assume we have a table that shows order numbers. If we only want a particular order number to be shown once, we obviously could use a distinct on the query. If that is not an option you can use a script and a visibility expression to implement this requirement. On the table row’s onCreate method you could enter a script like:




var rowLast = this.getRowData().getColumnValue("ORDERNUMBER");
reportContext.setGlobalVariable("rl", rowLast);



And a visibility expression like:

if( row["ORDERNUMBER"] == reportContext.getGlobalVariable("rl") ){
true;
}else{
false;
}

This will cause the BIRT engine to only show the row if its current row order number is different from the previous row.



As with the previous examples and all visibility operations the underlying data is not altered. It is just not displayed. For example if you hide a table the dataset still executes. If you want to alter the underlying data you will need to filter the data either on the table or the actual dataset. This can be difficult when trying to get a rows previous value so I created a simple aggregate extension Plugin that will do this for you. It is based on Scott’s post on Optimistic sums. The extension point has been revised slightly since that post so take a look at the attached examples to see the source code. The extension point adds a previous value aggregate function to BIRT. To use it you can create a computed column on the dataset.



The function stores the last row value as entered in the Column expression. Once the computed column is added to the dataset you can then select the filters tab and enter a filter like:


This will filter the dataset as shown below.



The source for this post is available at Birt-Exchange. The download contains two reports (one that uses the aggregate function and one that was demonstrated earlier in the post), the exported new aggregate plugin, and a source project for the plugin.