Whats the Difference Between dataSetRow["FIELD"] and row["FIELD"]

One of the most common questions for people that are new to BIRT is about how to ask data from the DataSet in the report.  The question is when building expressions should I use dataSetRow["FIELD"] or row["FIELD"]?

So let me see if I can set the record straight.  When data is acquired, it is acquired by a DataSet, so the following query in a JDBC DataSet will create a three field resultset:

select CITY, STATE, COUNTRY 
from CUSTOMERS

Any script or expressions written on the DataSet will be written to use the format

row["FIELD_NAME"];









So if we add a computed column to the DataSet called compCityState, the expression would look like this.

Once a computed column is created, you can reference that computed column using the same row syntax.  So in the OnFetch method you could add this message to log the value of the computed column.

Packages.java.lang.System.out.println(row["compCityState"]);

The other place that you can access variable on the DataSet is through ReportItem binding.  In most cases, this means Table Binding where you have attached a table to a DataSet.  In general, when you use BIRT you associate a Table with a DataSet.  We say that the Table is bound to the DataSet.

When a DataSet is dragged to the Layout editor, BIRT automatically does the data binding and creates a bound column for each field and computed column in the DataSet.  The following shows a table and its Binding.

You will notice that all of the expressions use the term dataSetRow, which means this value is pulled from the DataSet.  Now for the tricky bit, any field that is bound to a Table can be used in another table binding.  So if we wanted to create a field that has City, State and Country, we could either:
a) go back to the DataSet
b) add a new table binding

When you add a binding to a table you are brought to the expression builder.  In this first example, I am creating a table binding that gathers data straight from the DataSet.

Because I am referencing the DataSet directly the expression uses the dataSetRow syntax.

In the next example, I will use the previously established bindings to build the expression.  In the expression builder I will select Available Column Bindings and the Table and then select the fields.  You will also notice that the Table Binding has access to any of the previously bound fields and a special RowNumber field.  

The important thing is that the syntax to access those values is to use row["FIELD_NAME"]

So the short answer is, dataSetRow syntax references the original values from the DataSet, whereas the row syntax references the bound column values.

Now for a quick test, this should be easy.

Imagine that I change the expression of the CITY expression to look like this, and all else remains the same.

If the values for the row are:
City: Kalamazoo
State: MI
Country: USA

What will a DataItem that is showing rowCityStateZip display?
What will a DataItem that is showing tablCityStateCountry display?


The answers will be after a short shameless promotion of my companies BIRT training program.  We have designed a modular training program that is focused on teaching you how BIRT works.  You can take one module or all of them.  The focus of our training is on how BIRT works, so that when you finish you really understand what is going on in the product.

Our training can be taken on-site, or through remote sessions of between two and four hours each.  We are also happy to work with your team to customize the training to match your companies needs.   If you are interested, please visit our web site and have a look.

Answers:
rowCityStateZip:        { Kalamazoo }, MI USA
tablCityStateCountry:  Kalamazoo, MI USA

Remember, dataSetRow syntax (as used in tablCityStateCountry) goes back to the original data.  Row syntax use the table binding.

Manipulating Chart Legends in Event Handlers

Recently I had the opportunity to figure out how to manipulate sizing, positioning and text wrapping of chart legends so I thought I'd share what I learned.

If you have a chart with legend items that don't fit in the available space, BIRT will do one of two things, depending on the wrapping width option. (Wrapping width is found on the legend layout dialog). If the wrapping width is set to zero, BIRT will simply truncate the legend item text and optionally append an ellipsis. (The ellipsis option is located on the legend entries dialog).

If the wrapping width is set to a positive value, BIRT will word-wrap the text. Unfortunately when it does this, it doesn't check the vertical boundaries and long items can end up overlapping.

Another downside is you have to specify a hard width within which to wrap. It won't simply wrap based on the amount of space that's available. On top of that, my client had some special requirements about how the Thai characters should be wrapped. So I dug into the chart event handlers to see what I could do.

It turned out I could do a lot, but I had to pick the right event handler. My first thought was to use beforeDrawLegendItem. It looked perfect because of the Bounds parameter. However, it turned out that was not the bounds of the text area, but of the colored rectangle, which didn't help me much. Additionally I couldn't manipulate the size and position of the legend items within that handler and have it stick. The horses had already left the barn so to speak.

The beforeRendering handler was much better. In it I could manipulate the size, position and text of each legend item. Following are the the technical details.

The first argument to the event handler function is a GeneratedChartState object (which I'll call gcs). Using gcs.getRunTimeContext(), you can get the RunTimeContext object (rtc). Then using rtc.getLegendLayoutHints() you get the LegendLayoutHints object (llh) which tells you about the legend as a whole. Particularly llh.getLegendSize() tells you how big BIRT wants to make the legend. Finally llh.getLegendItemHints gets you an array of LegendItemHints (liha), which gives you access to each legend item. The number of legend items is liha.length. The text for each item is lih.getItemText().

So how much height is available for the whole legend? You can get that with gcs.getPlotComputations().getPlotBounds().getHeight(). The width of the legend is llh.getLegendSize().getWidth(). I didn't need to change the width so I didn't try to find out how to do that. I'm not sure how BIRT allocates horizontal space to the legend vs the chart itself. That may present itself as a future exercise.

For my project I had to wrap this text according to the space that was available, so I needed to be able to measure the pixel width of a string of characters. To do this you need to instantiate BIRTChartComputation. The constructor takes no arguments so it's simple. I'll call my instance bcc. Next you need the font height, which you can get from bcc.computeFontHeight. This method needs an IDisplayServer and a Label. You can get the IDisplayServer from gcs.getDisplayServer() and you can create a temporary Label with GObjectFactory.instance().createLabel(). The method with compute the font height based on the text in the label. Finally, you get the overall size of a string of text with bcc.computeLabelSize, which you pass an IDisplayServer, a Label and a font height. This is a fairly expensive operation so it's best to use a binary search to look for a wrap point.

Once a new version of the text has been computed, you can simply insert it back into the LegendItemHint using lih.setItemText(). Also the item height can be set with lih.itemHeight(). Finally, if you want an ellipsis to be appended you need to set the valid item length with lih.validItemLen() to the character position where you want the ellipsis to go. If you set it to zero or a value greater than the length of the label, no ellipsis will be appended.

Since I also needed to change the overall size of the legend, I wanted a setLegendSize method in LegendItemHints, but no such method exists. In fact, the class is mostly immutable. Fortunately it's possible to instantiate a new LegendItemHints with a new size and pass it to the RunTimeContext with rtc. setLegendLayoutHints().

An that's it. Fun, huh? Ok you can stop yawning. Here's what my new legend looks like:

I know you can't do everything in event handlers, but this is more than I thought I'd be able to do when I started. I was pleasantly surprised.

BIRT and OLAP

BIRT Introduced OLAP style data cubes and crosstabs in version 2.2 and while they have been around for some time we still get a lot of questions on how to use and manipulate them. Below are just some of the resources that have been posted to BIRT Exchange that should help you with cubes and crosstabs.

Introduction Resources
To get an idea of what a BIRT cube is and how to tie it to a crosstab report item, take a look at this article which provides a detailed write-up of the technology and supplies some examples.

To see a recorded demonstration of a crosstab style report being build see this tutorial video.

Event Based Scripting Resources
BIRT provides event based server side scripting for almost all report items, including crosstabs. A detailed blog post is available here with an example that demonstrates implementing a crosstab with event handler script.



Client Side Crosstab Scripting
BIRT supplies a Text report item that allows formatted HTML to be inserted into BIRT reports. Text elements can also be used to insert client side script that will execute while viewing the report in HTML. The Text element can be used in conjunction with a crosstab to add client side script to the crosstab. As an example of this take a look at these on BIRT Exchange. One of the examples animates the crosstab by iteratively changing the background color of a cell based on its value while the other inserts mouse over events to highlight specific cells.



Using the DE API in conjunction with a Crosstab
The BIRT Project provides the Design Engine (DE API) to construct report, template and library designs. This API is used by the designer when building BIRT content. Not only can this API be used in a Java project but can be called from a Java/Java Script event handler to modify a currently running report. There are many examples of this on BIRT Exchange. A couple of examples that are relevant to cubes and crosstabs are listed below.

This example uses report parameters and a beforeFactory JavaScript event to change the sort order for a crosstab.



Another example of using this approach is located here. This example contains a report with no graphical content, but does contain a datasource, dataset, and data cube. It uses report parameters and a beforeFactory JavaScript event to construct a crosstab based on the parameters.


Building a crosstab with the API
If you are using the DE API to create reports from your Java application and wish to add crosstabs to your reports this example illustrates building a data cube and adding a crosstab to a report using the API.

Extended Capabilities
If you are interested in further capabilities, Actuate 11 which will be released this fall, provides a new feature called the Actuate BIRT Data Analyzer. This product is a zero footprint browser based BIRT cube analyzer. It can be used with existing BIRT reports that contain cubes to add, modify, re-order, or remove dimensions and measures without having to re-execute the report.



In addition to the above, the Data Analyzer can be used to pivot, filter, sort, and add computed measures and aggregates to the crosstab, all from within the browser without re-executing the report.



If you are interested in trying out this feature download the latest milestone build of the Actuate BIRT Report Designer from the milestone build page.

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.