Designing High Performance BIRT Reports

Over the last couple of months BIRT-Exchange has hosted many webinars explaining and demonstrating BIRT technology. Last weeks webinar featured Mica Block discussing BIRT performance. In this presentation, Mica explains how to gauge performance and provides tips for improving generation time. If you missed it or any of the others, they are available
here.

In addition the following topics will be discussed in the future:

5/30/2008
Using the BIRT Report Engine API
Virgil Dodson

6/13/2008
Using the BIRT Design Engine API
Jason Weathersby

6/27/2008
What's New with BIRT 2.3
Virgil Dodson

7/11/2008
Ad-hoc BIRT Reporting for End Users
Rob Murphy

7/25/2008
BIRT Charting Primer
Virgil Dodson

8/8/2008
Using the BIRT Chart API
Jason Weathersby

Embed HTML

So this is a really simple problem that Jason solved for me. If you have HTML in your database and you want to embed it into your report as HTML you need to do a few things. First start with a Text control.

1) Change the top drop-down to HTML

2) Change the second drop-down to Dynamic Text

3) Click on the tag and use the expression builder to select the appropriate field.





4) Manually insert the attribute format="HTML" into the VALUE-OF entity













If you follow these steps your HTML text will show up in your report document as formatted text that obeys the HTML rules.

Java Event Handler ClassPaths

One of the most common questions that comes up when developing Java Event Handlers is:

"Where do I put my classes?"

Rather than write it up here, I added an entry to the BIRT FAQ here.

BIRT: Swapping Data Set at Runtime

Often it is a requirement to design a report against a development database and then switch the connection at runtime. This can be done in many ways with BIRT. The options include:

JNDI - Look up the data source.
Property Binding - Swap the data source at runtime using property binding expression.
Script – Write a script in the beforeOpen event handler to modify the data source properties.
Connection Profiles – Store the connection information in a shared profile.
DTP driverBridge extension – Implement a driverBridge extension to intercept calls to a specific driver.
Design Engine API (DE API) - Swap the dataset property on a specific table before running the report.

Using the DE API often serves as a useful alternative, because the data sources may be different types. For example your deployed report may use a scripted data source to retrieve some EJB value, but at design time you may not have access to this data source. So you could develop a stubbed data source that uses JDBC to test your report layout and when deployed swap the data set on elements that are bound to the data set. To do this you need to do a few things. First name your elements that use the data set to be swapped. This can be set in the general properties for each report element. Next make sure your two data sets (one for design time and one for deploy time) have the same result columns. If the result columns are not the same much more work needs to be done in order to implement this solution. Finally implement a beforeFactory script that modifies the report design before it is executed. Assume we have two data sources/data sets and one table. If I name my table “mytable” and my data sets are named “viewer” and “designer” the beforeFactory event script would look as follows:

des = reportContext.getHttpServletRequest().getParameter("__designer");

if( des == null || des == "false" ){
   mytable = reportContext.getReportRunnable().designHandle.getDesignHandle().findElement("mytable");
 mytable.setProperty( "dataSet", "viewer" );
}

Note that the “designer” data set is the default data set used in the report. This is the data set that you should drag to the canvas to build your report


The first line of code determines whether we are using the designer. The designer adds the __designer parameter to URL when the report is previewed. This parameter will not exist when deployed. If this parameter does not exist or is set to false we assume that the report is deployed. This line:

reportContext.getReportRunnable().designHandle.getDesignHandle()

returns the report design handle. BIRT 2.3 has an easier way of getting the report design, but this method should work for BIRT 2.2 and 2.3. We then use the report design handle to locate the table element to be modified. Finally we set the dataSet property to the viewer dataset.


An example report illustrating this concept is located here.

BIRT Drill Through

BIRT supports drilling down from a master report to a detail report. To facilitate this feature, BIRT provides a Hyperlink Options builder.



To drill from a table data element, the developer can select the data element, choose the hyperlink property and click on the ellipsis. This will launch the Hyperlink builder. Once the builder is launched, select the Drill-through radial, select either a report design or report document to drill to and supply any needed parameters. Parameters are often based on the current row value of the master table. That is all that is needed to create a master detail report, but suppose you want the detail report to be based on report parameter or some other variable. One way of accomplishing this task is to call the Design Engine API from script and modify the design at runtime.

For example suppose you have a master report and you want the detail report to be based on report parameter. You could define a report parameter for the master report that contains the name of different report designs to be used as a detail. The follow report parameter illustrates doing this with a static list box parameter. Its values are DetailOne and DetailTwo.



Next select the data element that you want to link, select general properties and name the element. In this example it is named mydataelement.




Finally enter script similar to the following in the beforeFactory script event.
importPackage(Packages.org.eclipse.birt.report.model.api);
importPackage(Packages.org.eclipse.birt.report.model.api.elements);



dataHandle = reportContext.getReportRunnable().designHandle.getDesignHandle().findElement("mydataelement");

ac = StructureFactory.createAction();
ah = dataHandle.setAction( ac );
ah.setLinkType("drill-through");
ah.setReportName( params["detailrpt"].value +".rptdesign");
ah.setTargetFileType("report-design");
ah.setTargetWindow("_blank");
pb = StructureFactory.createParamBinding();
pb.setParamName("order");
pb.setExpression("row[\"ORDERNUMBER\"]");
ah.addParamBinding(pb);





This code creates a drill-through hyperlink on the fly and sets the detail report based on the report parameter with this line of code:

ah.setReportName( params["detailrpt"].value +".rptdesign");






This example is available at
BIRT Exchange.

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.

BIRT Resizing Charts

Note: I have added the reports that are used in this post to the subversion repository http://longlake.minnovent.com/repos/birt_example
the project is birt_api_example
and the reports can be found in /Reports/charts
Alternatively you can just go to:
http://longlake.minnovent.com/repos/birt_example/birt_api_example/Reports/charts/
navigate into the charts and cut and paste into your report designs

When creating charts in BIRT the size is set automatically. The chart can be resized with the mouse after the chart is in the report as well. This is fine if you know before hand the amount of data your chart is going to display. Currently there are several bugzilla entries tracking chart resize features, but I thought I would offer a work around until those entries are completed. Presented below are two options for resizing the chart. Using the simple chart API, and using the afterDataSetFilled chart event to modify the size of the chart based on the data retrieved.

One way of resizing the chart is to use the chart simple API within the beforeFactory event. The simple api allows simple properties of the chart to be changed before the report is executed.

Some examples include setting titles, chart dimensions, and output formats:



var chart1 = this.getReportElement( "Chart1" )
var chart2 = this.getReportElement( "Chart2" )
var chart3 = this.getReportElement( "Chart3" )
var chart4 = this.getReportElement( "Chart4" )

var color1 = chart1.getTitle().getCaption().getColor()

chart1.setColorByCategory( true );
chart1.getTitle().getCaption().setValue( "Color by Category" );
chart1.setColorByCategory( true );
color1.setRed( 255 );
color1.setGreen( 0 );
color1.setBlue( 0 );
chart1.getTitle().getCaption().setColor( color1 );

chart2.setDimension( "ThreeDimensional" );
chart3.getCategory().setSorting( "Descending" );
chart4.setOutputType("PNG");

In this example the charts must be named. Select the general properties for the chart and in the name field enter a value.

The size of the chart can be set using the following code in the before factory.



var mychart = this.getReportElement( "mychart1" );
if( reportContext.getOutputFormat() == "pdf" ){
mychart.setWidth("3in");
mychart.setHeight("3in");

}

In this example we resize the chart based on output format.

If you want the chart to resize based on the data populated in the chart data sets it requires a little bit of a work around. First, name the chart as with the simple chart API and enter the following code in your beforeFactory event handler.




var mychart = this.getReportElement( "mychart" );
mychart.setWidth("");
mychart.setHeight("");

After you have done that, enter code similar to the following in your afterDataSetFilled chart event handler.



function afterDataSetFilled(series, dataSet, icsc)
{
if( series.getSeriesIdentifier() == "seriesone" ){
if( dataSet.getValues().length > 4 ){
 icsc.getChartInstance().getBlock().getBounds().setWidth(800);
 icsc.getChartInstance().getBlock().getBounds().setHeight(600);
}else{
 icsc.getChartInstance().getBlock().getBounds().setWidth(400);
 icsc.getChartInstance().getBlock().getBounds().setHeight(300);

}
}
}

In this example we have named our series “seriesone” on the third tab of the chart wizard.

If the category values contain more than four entries we double the size of the chart. The sizing could be more dynamic, for example we could use the list size to increment a delta per category as well.

This example works with BIRT 2.2.2, but will not work with the chart output type set to SVG, when rendering to HTML.

For Birt 2.3, everything can be done in the beforeGeneration script and it does work with SVG. No other scripts are needed.



function beforeGeneration( chart, icsc )
{
xAxis = chart.getBaseAxes()[0];
yAxis = chart.getOrthogonalAxes( xAxis, true )[0];
seriesDef = yAxis.getSeriesDefinitions().get(0);
runSeries = seriesDef.getRunTimeSeries()[0];
//Retrieve list of data values
list = runSeries.getDataSet().getValues();
if( list.length > 3){
  chart.getBlock().getBounds().setHeight(250);
  chart.getBlock().getBounds().setWidth(600);
}else{
  chart.getBlock().getBounds().setHeight(100);
  chart.getBlock().getBounds().setWidth(600);
}
}