Thursday, October 21, 2010

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") ){

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.


Anonymous said...

Perfect... This is just i was looking for.. albeit I had to make little change.. but was stuck on this for almost 4-5 days... thanks..

Anonymous said...

Excellent... i got the solution.....

Gustavo Leyva said...

Hi!! Thank you so much!!, I was stuck for a few days!

Jason Weathersby said...

Glad the post helped

Gustavo Leyva said...

Hi Jason,

I would like to know your advice, I am new with BIRT and still working to understand the tool;

I have to create a report with a nested table, parent is the first table and children is the second table, the parents can have none, one or more childrens, childrens only one parent, I just want to see the parent with all children have resolution date, then I need to "hide" or "filter" the parent row if one of the children have not a resolution date.

123 | exam | ID | DESC | RES_DAT
234 test1 1/1/13
234 test1 1/1/13
987 | exam2| ID | DESC | RES_DAT
23 test2 1/1/13
43 test2

I would need to hide or filter the parent row with the ID = 987 because the children row ID = 43 have not a resolution date.

Please let me know your comments.

Thanks in advance.


Jason Weathersby said...

You will need to use an oncreate row script on the detail row of the inner table and an onrender script of the outer table's detail row to do this. My email is jasonweathersby at windstream dot net. Send me an email and I will send you an example.

Hitesh Jain said...

I am getting the below exception

rg.eclipse.datatools.connectivity.oda.OdaException: Duplicate column names found in source data.

Can you help me why it is getting this error? As i am new to BIRT Report and also i dont see that suppress duplicates checkbox mentioned.

praveen kumar said...

The post was a good solution. Can i render only 5 records/categories initially in a piechart and later by using the Top/Bottom N filter provided by the piechart, i need to fetch more records i.e., greater than 5.Currently if i restrict the intial query with 5 by either delimiting or intial filter, after rendering i will not be able to get more than 5 records. Is there a place to change the query after rendering the data in a piechart?