Data Driven Reports

Last week I posted about how to debug reports using a tester application. As part of that exercise I showed a dynamic table report where the number of columns in the report was driven by the data. This week, I'm going to examine how we access the database to determine the dynamic column values in that example.

BIRT event handlers have three distinct phases:

- BeforeFactory / OnPrepare
- OnCreate
- OnRender

If you want to modify the design of the report (add columns, drop columns, etc.) it has to be done in the beforeFactory/OnPrepare method. At this time you can get access to the DesignElementHandle objects that make up the report design and modify appropriately. The problem is that the report does not have any access to the data at this time. The DataSources and DataSets have not been run at this time.

In theory, one could get a handle to the DataSource / DataSets and manually instantiate and run them, but it's not pretty. You would need to write a lot of code. I have entered a bugzilla entry to rectify this situation, but I am not sure when or if that will be a feature we can add (volunteers? anyone? anyone?).

So how do you get access to data in the beforeFactory/OnPrepare method? Well it turns out that it is relatively easy to use the Report Engine API to access parameters through the ParameterDefinitionTask. Parameters in turn can be tied to a DataSet. So it seems like we have a route to access data from the OnPrepare method.

To demonstrate, I will create a simple report that combines both the REAPI and DEAPI to dynamicaly create a column for each type of order status and in that column will be a label for the column type. As stated before, the source code for this example can be found on my subversion server at http://longlake.minnovent.com/report/birt_example. The projects that you will need are:
- birt_api_example
- birt_runtime_lib_222
- script.lib

The first thing we are going to do is to build a simple report that gets all of the orders and separates them by the order status. If you create a data source that uses classic models and add a data set named setOrders with the following SQL.
select status from orders

Drag the data set onto the report layout and you will have a table with all of the statuses. Now it is trivial to create a report that summarizes the count for each status as a row. What we would like to do instead is have the statuses be columns for this report. This means that we want to take our one column report at design time, and make it an N columns at runtime. Since we are not concerned with the detail columns, you can delete the detail row from the table for now.

This leaves you with a report with only one column and one cell that holds a label which says Status. I like to start simple.



To make this happen we need to have a different data set that returns all of the unique statuses, name the data set setStatus and make the sql:

select distinct status from orders

As stated before, the easiest way to get to this dataset's values in the OnPrepare method is to wrap the DataSet inside a parameter. So create a new parameter named pStatus. Make it a combo and add setup the dataSet to be the setStatus that we just defined. For the value field use row[0] which is a standard field that will return the row number for each row. Make the Display field equal to row["status"] as shown in the accompanying screen shot.



Once you have the parameter setup, you are ready to start writing your event handler. If you want to test whether you have the parameter setup correctly, run the report and click on the Show Report Parameters button at the top of the preview panel. It should show you seven order statuses.

Now we need to add an event handler to the report. We could add the event handler in a number of locations. We could add it to the label, the cell which hold the label, the column, or the table. It makes sense to add the event handler to the table for a couple or reasons. The most basic reason is that we may want to support more than one dynamic column within this table.

To create a table event handler, create a new java class that extends the TableEventAdapter. We want to create this class in the script.lib project so that we can debug the code as we produce it.

package deapi_event_handler;

import org.eclipse.birt.report.engine.api.script.eventadapter.TableEventAdapter;

public class DynamicTable extends TableEventAdapter {

}

If you right click in the java class and select Source -> Override/Implement Methods and then select the OnPrepare method in the dialog you will have the method that we need to override. The first thing we want to do is to get the values that have been exposed through our parameter. I have created a helper class ParameterUtil that has methods to get either a list of values using the ParameterDefinitionTask.

public static Collection<IParameterSelectionChoice> getParameterValues(String parameterName, IReportContext reportContext) {
IGetParameterDefinitionTask task = null;
HashMap curParams = new HashMap();
IReportRunnable runnable = reportContext.getReportRunnable();
try {
task = runnable.getReportEngine().createGetParameterDefinitionTask(runnable);

// get the names of all the parameters
Collection paramRefs = task.getParameterDefns(false);

// for each parameter name, get the parameter value
// add the name and value to a hashmap
for (Iterator iterator = paramRefs.iterator(); iterator.hasNext();) {

    ParameterDefn pDefn = (ParameterDefn) iterator.next();
    String name = pDefn.getName();
    Object curP = reportContext.getParameterValue(name);

    curParams.put(name, curP);
}

// set the parameter values for this task from the hashmap.
task.setParameterValues(curParams);

// get the parameter that is tied to this table.
IParameterDefnBase scalar = task.getParameterDefn(parameterName);
if (scalar instanceof IScalarParameterDefn) {

    // bind the parameters to the query text
    task.evaluateQuery(scalar.getName());

    // get the values for this parameter from its DataSet
    Collection<IParameterSelectionChoice> paramChoices = (Collection<IParameterSelectionChoice>) task
            .getSelectionList(scalar.getName());

    return paramChoices;

}
} catch (Exception e) {
System.out.println("Failure to get parameters");
} finally {
task.close();
}
return null;
}

As you can see, this method takes a string which names the parameter we are going to use and the report context and returns the values/labels exposed by that parameter. So if we want to use that method we have to figure out which parameter holds the values that we want to show as columns.



To do this, I am going to add a NamedExpression to my table. Once I have defined a NamedExpression on my table, I can look up the name of that expression from the script and use that in my ParameterUtil method. To add a NamedExpression to a table, to to the PropertyEditor for the table and select the NamedExpression tab.

In addition to creating the named expression, you will need to specify that this table will use the Event Handler we created in a previous step.

So now I will modify my OnPrepare method to first get the named parameter value and then to get the values defined by that expression.

@Override
public void onPrepare(ITable tableScript, IReportContext reportContext) {

String paramName = tableScript.getNamedExpression("dyn_parameter");

Collection<IParameterSelectionChoice> colNames = ParameterUtil.getParameterValues(paramName, reportContext);

for (IParameterSelectionChoice paramChoice : colNames) {
   System.out.println(paramChoice.getValue() + ": " + paramChoice.getLabel());
}

}

At this point, you should be able to run the report using the tester. If all goes well, you should see the following in your console.

home: C:\BIRT\ws_ec2008\birt_api_example\Reports
1 property file(s) found
output: C:\BIRT\ws_ec2008\birt_api_example\tester_output_20080425_171337\data_driven.
Executing tester_run\data_driven.properties
Start run
0: Disputed
1: On Hold
2: In Process
3: Shipped
4: Cancelled
5: Resolved
C:\BIRT\ws_ec2008\birt_api_example\tester_output_20080425_171337\data_driven.: built.  Start render.
Run duration = 2531 ms
Render duration = 641ms
Success 1 : tester_run\data_driven.properties
Finished Fri Apr 25 17:13:45 CDT 2008

So what have we accomplished? So far, we have a simple report that is able to dynamically get a list of values from a query defined within the report at runtime, in the onPrepare method. Now that we have this working, we can start on the next step which is to use those values to dynamically create new columns in the report.

Next week, I will examine how you can use the Design Engine API to do just that for both labels and data items.