BIRT 2.6 Webinar

Thanks to all those who attended the New BIRT 2.6 Features in Helios webinar. The recording is available on Eclipse Live. The examples used in the webinar are available at BIRT-Exchange.

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.

Adventures with Fonts

One of the biggest problems facing report developers is how to efficiently support multiple languages using multiple output formats (HTML, PDF, Excel...) BIRT provides support for multiple languages but the support is both OS and output format dependent. Having gone through this process a couple of times, I have been asked to share my experiences, hopefully it will simplify the task if you run into similar issues.

What I knew going in was that fonts are handled differently depending on the type of output. HTML fonts are rendered in the users browser, PDF fonts are rendered on the server by BIRT, and chart fonts are rendered on the server by Java AWT. On top of all that, fonts in the development environment are rendered by SWT. There are different ways to configure fonts for each of these situations.

For HTML reports, the user must have the appropriate fonts installed on their machine.

For PDF reports, BIRT renders the fonts on the server using information in the various fontsConfigXXX.xml files located in plugins/org.eclipse.birt.report.engine.fonts_XXX. The base file is fontsConfig.xml and there are additional files to handle specific operating systems. There are some useful comments in fontsConfig.xml that explain how this works. I know these files are used for PDF generation and I think they are also used for rendering fonts in the development environment.

From a previous font adventure, I knew that BIRT uses AWT to render charts, so font configuration is done in the lib folder of the JRE by manipulating various properties files. Documentation on this seems to be a bit sketchy, but this site has some information.

My customer's problem, had to do with PDF output. Thai characters were displaying as question marks in their 2.2.1.1 BIRT reports. This was happening on all the OS's they ran, including Windows, Solaris and Linux. The characters looked fine in HTML output, but the PDF rendered characters incorrectly. Their system administrator stated that they had full language packs installed on all of the machines.

I started my investigations on their Windows box, running Windows Server 2003. The first thing I looked at was the fontsConfig.xml file in plugins/org.eclipse.birt.report.engine.fonts_2.2.1.v20070823. I noticed there was a block element for Thai in the commented out section:

<block name="Thai" start="e00" end="e7f" index="27" font-family="Font-Family"/>

I realized this has the rather nice effect of dynamically switching fonts in mid-string whenever characters within this range are encountered. There's no need to use a special font in the report design and that's particularly important in multi-lingual environments.

The only problem was the font family. The sample element didn't tell me what it should be and I couldn't tell by looking looking at the font files in C:\windows\fonts. I tried downloading several font viewers but none of them told me what I needed to know.

Then I had the good luck to check on my laptop, which runs Windows 7. In Windows 7, the font listing in windows explorer shows lots more information about the fonts, including a column called "Designed For". I found there were several fonts that were "designed for" Thai and Angsana New was one that also existed on their server. I used Angsana New for the font family in the block element and restarted their web application and it fixed it!

Next I had to preform this feat on their Solaris box.

Of course I tried Angsana New right off the bat, but it wasn't going to be that easy.

I started exploring the installed packages on my on Ubuntu box by searching for "font" in the synaptic package manager. I found all kinds of programs and spent a good amount of time looking at the man pages for them and trying them out, but the only one I found that told me which fonts support which character ranges was gucharmap, the Gnome version of the character map program.

The left side has a list of "Scripts" and one of them was Thai. Clicking on that showed the Thai character set (which is relatively small). I found that holding the right mouse button down over a character would display its font name. On my Ubuntu box the font was Waree, which worked for my machine but did not solve the client's issue.

Using gucharmap on the clients machine showed that their server was using AngsanaUPC. A quick modification to the fontsConfigXXX.xml to use AngsanaUPC and the problem was resolved. The main lesson I learned from this was how to find font families that support specific blocks of unicode characters.

For windows, Windows 7 explorer works nicely. I am unsure how to handle this if you are using XP. For Linux gucharmap worked, but it was dependent on the particular distro of Linux. The only downside to this approach was the need to have XWindows access. I will continue looking for a good command line utility that can be used to research installed fonts on servers. If anyone has found a utility like this, I would love to hear about it.

More on Chart Interactivity

In an earlier post we examined chart interactivity with a focus on the Invoke Script Action, which allowed client side scripts to be called. This post is available here.

At the end of the post we mentioned predefined variables that are passed to the script that is invoked. These variables are:


evt – The Event Object.
categoryData – The Chart Category Value.
valueData – The Chart Orthogonal Value.
valueSeriesName – The specific Series Name.
legendItemText – The specific legend entry Text.
legendItemValue – The value of the specific legend entry.
axisLabel – The value of the specific axis label selected.


What these predefined variables contain depends on the chart type, how the chart is configured and what event is being fired. For example if you define a Bar chart with legend interactivity and do not show the legend value, all the variables will be null with the exception of the evt Object and the legendItemText variable. Generally these values will be populated like (evt object is always populated):

Series Interactivity Invoke Script – categoryData, valueData, valueSeriesName populated, the rest are null.
Legend Interactivity Invoke Script – legendItemText and legendItemValue populated, the rest are null.
Axis Interactivity Invoke Script – axisLabel populated, the rest are null.
All other Interactivity Invoke Script – All are null.

To see the values you can call an invoke script function to alert them. For example, select a specific series on a chart within the third tab of the chart wizard and select interactivity. For the event enter Mouse Click. For the Action choose invoke script and enter the following script.




ShowEventData(evt, categoryData, valueData, valueSeriesName, legendItemText, legendItemValue, axisLabel);






Next add a text element to the report that has the following value.

<script language="JavaScript">

function ShowEventData(evt, categoryData, valueData, valueSeriesName, legendItemText, legendItemValue, axisLabel) {

alert( "categoryData=" + categoryData + 
"\nvalueData=" + valueData + 
"\nvalueSeriesName=" + valueSeriesName + 
"\nlegendItemText=" + legendItemText + 
"\nlegendItemValue=" + legendItemValue + 
"\naxisLabel=" + axisLabel);
 
}
</script>

Make sure you set the type to HTML for the text element. The output when clicking on the series should look similar to:



An example report that contains this event on all interactivity elements is available at Birt-Exchange.

An example that uses the predefined variables with client side script to toggle the visibly of a chart series is also available at Birt-Exchange.

Multi-View Report Items

BIRT currently supports the concept of a multi-view report item. What this means is that two report items share the same data bindings and physical space, but only one is displayed. In the current version of BIRT only tables and crosstabs support this multi-view feature and the second view must be a chart. Generally the view that is displayed is chosen at design time, but this can be altered using some script and a report parameter.

To create a multi-view report item, first create a table or crosstab. Next right click on the report item and select Create Chart View.



This will launch t he Chart Builder. Construct the chart as usual. After creating the chart a second tab will appear at the bottom of the report item.



The tab selected currently determines which view will be displayed. The selected view can also be changed with script and a report parameter. First select the table tab and name the table.



In this case I have named the table “mytable”. Next add a report parameter to use in the script. The parameter should allow two choices, show as chart or show as table. In this example I have named the report parameter ShowAs and the parameter has chartview or tableview as possible values.



Finally enter the following script in the beforeFactory event.

var th = reportContext.getDesignHandle().findElement("mytable");
var mvh = th.getProperty("multiViews");

if( params["ShowAs"].value == "chartview" ){
 mvh.setProperty("index", "0" );
}else{
 mvh.setProperty("index", "-1" );
}

The first line retrieves a handle to the table and the second retrieves a handle to the multiviews property for the table. The parameter is then used to set the index for the multiview item.

The output will look similar to:



And




This example is available at Birt-Exchange.