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.

BIRT Java Object Data Type

In addition to simple data types BIRT also supports a Java Object Data type. This type can be useful when writing reports that use scripted data sources that consume POJOs that contain a member object. For example assume you have an array of objects and each of these objects contains three member variables (a String, an Integer, and an Object).




Before BIRT supported the Java Object data type you could write a scripted data source that iterated the array and assigned a row variable for the string member and another for the integer variable. If you wanted to use the Object member you would need to call some method on the object that returned a BIRT primitive type. While this is generally not a drawback with a simple table, if you wanted to nest tables and use the inner object in another dataset, scripting this became very complex. Using the Java Object data type now makes this much easier. The only caveat to using the Java Object data type is that the object must be serializable. The reason for this is that BIRT caches datasets and the object will be cached just like any other column value.

To illustrate using the Java Object type, consider the following classes. The BIRTOuterJavaObject contains string, integer, and object member variables.




package test.birt.javaobject;

import java.io.Serializable;

public class BIRTOuterJavaObject implements Serializable {


 private static final long serialVersionUID = 6530113212317618087L;
 private String outerstring;
 private int outerint;
 private BIRTInnerJavaObject innerobj;

 public String getOuterstring() {
  return outerstring;
 }

 public void setOuterstring(String outerstring) {
  this.outerstring = outerstring;
 }

 public int getOuterint() {
  return outerint;
 }

 public void setOuterint(int outerint) {
  this.outerint = outerint;
 }

 public BIRTInnerJavaObject getInnerobj() {
  return innerobj;
 }

 public void setInnerobj(BIRTInnerJavaObject innerobj) {
  this.innerobj = innerobj;
 }

 public BIRTOuterJavaObject() {

 }
}



The BIRTInnerJavaObject is just a sample object that stores information about an automobile. It contains three member variables which are all strings.

package test.birt.javaobject;

import java.io.Serializable;

public class BIRTInnerJavaObject implements Serializable {

 private static final long serialVersionUID = -4509250036928470517L;


 private String make;
 private String model;
 private String year;
 public String getMake() {
  return make;
 }
 public void setMake(String make) {
  this.make = make;
 }
 public String getModel() {
  return model;
 }
 public void setModel(String model) {
  this.model = model;
 }
 public String getYear() {
  return year;
 }
 public void setYear(String year) {
  this.year = year;
 }
 public BIRTInnerJavaObject() {
 this.year = "2009";
 this.make = "Chevrolet";
 this.model = "Corvette";
 }
 public String toString(){
  return "Make:--"+this.make+" Model:--"+this.model+" Year:--"+this.year;
 }
}

The BIRTRow class sets up the array of objects that will be used in the report.

package test.birt.javaobject;

import java.util.ArrayList;

public class BIRTRow {
 
 private ArrayList rows = null;
 
 public BIRTRow() {
  rows = new ArrayList();
  rows.clear();
 }
 public void setupRows(){
  
  BIRTOuterJavaObject row1o = new BIRTOuterJavaObject();
  BIRTOuterJavaObject row2o = new BIRTOuterJavaObject();
  BIRTOuterJavaObject row3o = new BIRTOuterJavaObject();
  
  BIRTInnerJavaObject row1i = new BIRTInnerJavaObject();
  BIRTInnerJavaObject row2i = new BIRTInnerJavaObject();
  BIRTInnerJavaObject row3i = new BIRTInnerJavaObject();
  
  
  row1i.setMake("Toyota");
  row1i.setModel("Land Cuiser");
  row1i.setYear("2008");
  row1o.setOuterint(1);
  row1o.setOuterstring("Outer Row 1 String");
  row1o.setInnerobj(row1i);
  rows.add(row1o);
  
  row2i.setMake("Jeep");
  row2i.setModel("Cherokee");
  row2i.setYear("2002");
  row2o.setOuterint(2);
  row2o.setOuterstring("Outer Row 2 String");
  row2o.setInnerobj(row2i);
  rows.add(row2o);
  
  row3i.setMake("Land Rover");
  row3i.setModel("LR3");
  row3i.setYear("2006");
  row3o.setOuterint(3);
  row3o.setOuterstring("Outer Row 3 String");
  row3o.setInnerobj(row3i);
  rows.add(row3o);
  
 }
 public ArrayList getRows(){
  return rows;
 }
}

The primary goal of the example is to nest two tables. The outer table will have three columns. One column for each of the member variables of the outer object(BIRTOuterJavaObject). The Inner BIRT table will contain three columns as well, one for each of the member variables of the inner object(BIRTInnerJavaObject). Note that in this example the inner object could have contained a list of objects but for simplicity it only contains one object. Because the inner dataset will only return one row of data the example could have been completed using just one data set. So the report will contain two datasets one for the outer object and one for the inner object. The inner object dataset will be parameterized so that when we nest the inner table it can get the object from the outer dataset. The inner dataset will only return one row of data.



The outer dataset scripted data set code is as follows:
Open Method:

importPackage(Packages.test.birt.javaobject);
myrows = new BIRTRow();
myrows.setupRows();
rowsiter = myrows.getRows().iterator();
icnt = 0;

The open method sets up the array of objects and gets an iterator for the list.
Fetch Method:

if( rowsiter.hasNext() )
{
var obj = rowsiter.next();
row["OuterString"] = obj.getOuterstring();
row["OuterInt"] = obj.getOuterint();
row["InnerObj"] = obj.getInnerobj();
icnt++;
return true;
} else {
return false;
}

The fetch method iterates over each of the outer objects and assigns the column values. Note that the InnerObj column is of type Java Object and will be an instance of BIRTInnerJavaObject.


The inner dataset has three string columns.



The inner dataset also has a parameter that we will set when the table is nested. The parameter is named JavaObjectParam and its default value is set to null.



The inner data set code is as follows:

Open method:

importPackage(Packages.test.birt.javaobject);
if( inputParams["JavaObjectParam"] == null ){
icnt=1;
}else{
icnt=0;
}

The open method just checks to see if the dataset parameter is set. If the parameter is null the dataset will not return any rows. If the parameter is set the dataset will only return one row which is an instance of BIRTInnerJavaObject.

Fetch Method:

if( icnt > 0 )return false
var obj = inputParams["JavaObjectParam"];
row["Make"] = obj.getMake();
row["Model"] = obj.getModel();
row["Year"] = obj.getYear();
icnt++;
return true;

In the fetch method we get the object from the parameter and call the getter methods for the object and assign them to the column values.

Once both datasets are created, you can drag the outer dataset to the report canvas which will create the first table. Next add a column to the first table and drag the second dataset to the new column which should create the second (nested) table. To make the inner table work properly the dataset parameter has to be bound to the outer tables InnerObj column. To do this select the inner table and click the binding tab in the properties view. Click the data set parameter binding button and set the value of the dataset parameter to row[“InnerObj”].



The Java Object Data type can also be consumed by a chart. Continuing with this example if you add a chart and bind it to the outer dataset you can specify getter methods for the series, categories, optional grouping, tooltip help, etc.



The completed report should have similar output to the following.



This example is available at BIRT-Exchange as two projects. One Java project and one BIRT Report project. To run the example import both projects to your workspace, open the BirtJavaObjectReport.rptdesign and preview the report.

If you are using Actuate BIRT 11, The Java Object data type can be used in conjunction with the new POJO ODA data source that allows the Report Designer to access and report off of POJO’s using a GUI designer. If you are interested in this approach take a look at this blog post and this video that show the driver in action. An example of using this new data source is available on BIRT-Exchange and you can try out Acutate BIRT 11 by downloading it here.