BIRT 2.6 Released

BIRT 2.6 is now available and with this release many improvements and new features are available. Charting with BIRT has been improved adding support for a Radar/Polar chart type, palette hash patterns, better JPG quality, and improved SVG to PDF support. Changes have been made to the designer to support better per project classpath management, multiple resource files for localization, and support for locale and collation strength sorting properties.



To read more about the new BIRT 2.6 features see the BIRT New and Notable Features.

Conditional Table Filters

Just ran across this and it is a nice technique for those situations where you are limited to table based filtering of data. Typically, I focus on data filtering as far up stream as possible. It is better to filter data at the source (in the where clause for JDBC). Next, I use DataSet based filtering.

But sometimes you can't filter at the Source or the DataSet, which is where table based filtering comes in. The issue with table based filters is that there is no good way in the UI to implement conditional filtering. For instance, imagine you have a data driven parameter multi-select parameter and you want to limit the choices to the values from that parameter.

This is relatively simple to do, you just create a multi-select parameter and add a filter to a table.

So what happens if your user wants to select all of the values in the list? They need to select each of the values from the pick list that appears. Unfortunately, with large pick lists this is a little tricky for the end user. In addition, table based filtering on large pick list may create performance issues.

What you would like to do is make the parameter optional, and if the value for the parameter is not set, then just don't filter on that parameter value.

The UI does not support this type of behavior, but it is easy to do through a small amount of script on the table in the onPrepare method.

importPackage( Packages.org.eclipse.birt.report.engine.api.script.element ); 
importPackage( Packages.org.eclipse.birt.report.model.api.simpleapi ); 

if(params["Region"].value!= null){
    var filterCondition = StructureScriptAPIFactory.createFilterCondition(); 
    filterCondition.setExpr("row['COUNTRY']"); 
    filterCondition.setOperator("eq");
    filterCondition.setValue1("params[\"Region\"]");
    
    var filterKey = filterCondition.getStructure();
    var filterItem = SimpleElementFactory.getInstance().createFilterCondition( filterKey )
    this.addFilterCondition( filterItem );
}

Now how would you handle the situation if you had multiple filter conditions that you wanted to add?  A trivial re-factor and you can create a general purpose method add filter method.

// generic function to add a filter to a table
function addFilterItem (table, rowExpr, paramExpr){
  
  var filterCondition = StructureScriptAPIFactory.createFilterCondition(); 
  filterCondition.setOperator("in");
  filterCondition.setExpr(rowExpr); 
  filterCondition.setValue1(paramExpr);

  // could do in one step, shows DEAPI creation steps
  var filterKey = filterCondition.getStructure();
  var filterItem = SimpleElementFactory.getInstance().createFilterCondition( filterKey );

  table.addFilterCondition(filterItem);
}

This method can then be placed into a JavaScript file, a Script extension point, or a global location.  Calling the file from the table is equally easy.

// add filters dynamically
if (params["Country"].value != null )
  addFilterItem (this, 'row["COUNTRY"]' , 'params["Country"].value' );

if (params["City"].value != null)
  addFilterItem (this, 'row["CITY"]' , 'params["City"].value' );

It is important to recognize that the second and third parameters that are being passed are expressions and not values.  So what is happening is we are creating a filter, and that filter will use the express row["COUNTRY"] to look up the appropriate country values.  That is why you need to pass the exact expression through in the function.

If you are having trouble figuring out the appropriate expression, just build the expression that you want in the UI, and then search through the XML for the word filter. You will see the expressions in the XML. Just copy these expressions and pass them to your function. Just delete the static filter and you are all set.  Yes, you can use different operators then the IN clause, but it seems the most appropriate for this type of filtering.

BIRT Excel Output

BIRT 2.5.2 provides a variety of tools to construct reports to analyze data. These include charts, aggregation elements, drill to detail capabilities, nested tables, data cubes and crosstabs. These features are presented very well in the AJAX based viewer when deployed to the web, supporting pagination, table of contents, and exporting of data and contents to other formats. Out of the box, BIRT supports exporting to HTML, paginated HTML, WORD, PDF, PostScript, PPT, and Excel. BIRT also provides an extension point to implement your own emitters. For an example of implementing an XML emitter see Developing an Eclipse BIRT XML Report Rendering Extension or tBIRT: Writing an Emitter.

As stated earlier BIRT supports exporting to Excel. The BIRT Excel emitter creates a Microsoft Office XML XLS document that can be opened in Microsoft Office 2003 or greater. To use this feature either add the __format=xls parameter to the BIRT viewer URL or use the AJAX export button.



if you are using the Report Engine API, simply set up a render option for XLS.

                //RunAndRender Task
  IReportRunnable design = null;
  design = engine.openReportDesign("Reports/myreport.rptdesign"); 
  IRunAndRenderTask task = engine.createRunAndRenderTask(design);   
  EXCELRenderOption options = new EXCELRenderOption(); 
  options.setOutputFormat("xls");
  options.setOutputFileName("output/resample/myxls.xls");
  task.setRenderOption(options);
  task.run();
  task.close();

                //or Render Task
  IReportDocument document = null;
  document = engine.openReportDocument("output/resample/myreport.rptdocument"); 
  EXCELRenderOption options = new EXCELRenderOption();
  options.setOutputFormat("xls");
  options.setOutputFileName("output/resample/xlsoutput.xls");
  IRenderTask task = engine.createRenderTask(document);   
  task.setRenderOption(options);
  task.render();

While the XLS output is quite good, some features are not supported. For example new worksheets on page breaks are not created and images and charts are not exported to the XLS. While the team continues to improve the XLS emitter there are some other options for emitting XLS. One of these options is to use the Tribix emitter located on source forge. The Tribix project offers emitters for RTF, PPT and XLS.



If you wish to use just the XLS emitter, download the org.uguess.birt.report.engine.emitter.xls_version and org.uguess.birt.report.engine.common_version plugins and copy them to the plugins directory in your Eclipse install location. You will also need to copy them to the runtime location as well. For example, if you are using the WebViewer this will be the WebViewer/WEB-INF/Platform/plugins directory. You will also need to remove org.eclipse.birt.report.engine.emitter.excel.config_version and org.eclipse.birt.report.engine.emitter.prototype.excel_version plugins from both locations to replace the out of the box XLS emitters. Restart Eclipse with the –clean option and the Tribix emitter should work. No API changes should be required if you are using the RE API. New worksheets per page and image support should now work.



If you desire more XLS output options take a look at the Actuate XLS emitter that will be available in Actuate BIRT 11 which will be released this fall. It allows exporting charts as either images or as native XLS charts.



It also provides the capability to export formulas using a new scripting language called EasyScript, within the BIRT Expression Builder.





The Actuate BIRT XLS emitter also offers the capability to export BIRT Crosstabs to native XLS Pivot Tables.



If you are interested in trying out the Actuate BIRT XLS emitter take a look at the Acutate 11 Milestone download page located on BIRT-Exchange.