Monday, July 26, 2010

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.

Monday, July 19, 2010

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.

Wednesday, July 14, 2010

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.

Thursday, July 08, 2010

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.