Friday, February 18, 2011

BIRT Formatting numbers and dates

BIRT offers many options when formatting data is required. This post discusses some of the common ways this can be done using the GUI, script and expressions.

Using the GUI to format data

When formatting the standard Data report item, customizing the format is very simple. Located within the properties view for the data item are three tabs that allow formatting a number, date, or string. Which format the data item uses will depend on the data type for the data item as specified in the binding.

Each of the format tabs provides a set of predefined patterns that can be applied to the data. In addition, the pattern can be set to custom allowing the developer to define very specific formatting codes. The formatting operation can be set to a specific locale or auto which will use the locale of the user running the report. For example, entering ¤###,##0.00 as the pattern for a data item that is going to contain a currency may produce €131,44 for the French locale, and $131.44 for a US locale. For an example of using positive:negative patterns you may want to have a look at this post.

Report parameters selections can also be formatted in similar fashion to a Data report item. Each parameter has a “Format As” selection within the Parameter Editor. By selecting the change button a standard or custom format can be applied to parameter.

Formatting labels within the chart builder is also possible. Most items in a chart that display data have an associated format that can be customized by using the format button. For example to format a date axis use the GUI as presented below.

Formatting Data using the Expression builder or Script

The above formatting solutions work very well, but there are times when you want to mix formatted data within a string. In this case you can write script, use and expression, or use a Text report item to format the data. For example assume a report has two dates that are passed as parameters and you want to generate text within the report that shows “Start Date – End Date”. This is not achievable using the GUI methods described above. To do this you can use a text item and the value of syntax:

Start Date: <VALUE-OF format="MMM-dd-yy">params["date1"].value;</VALUE-OF>
End Date: <VALUE-OF format="MMM-dd-yy">params["date2"].value;</VALUE-OF>

In this example the VALUE-OF syntax is evaluated at runtime of the report and the format attribute is applied to the output. This approach also works with numbers. For example, <VALUE-OF format="$#,##0.00">row[“MyData”];</VALUE-OF>.

This same functionality could have been achieved by using a data item with the following expression.

var mydt1 =params["date1"].value
var mydt2 =params["date2"].value

importPackage( );
var sdf = new SimpleDateFormat("MMM d, yyyy", reportContext.getLocale());
var mydtf1 = sdf.format( mydt1 );
var mydtf2 = sdf.format( mydt2 );
"Start Date: " + mydtf1 + " End Date:" + mydtf2

In this example we are just using the imported Java SimpleDateFormat class to do the formatting.

If you are using BIRT 2.6.2 or higher, you can use the Formatter function within the expression builder to format localized numbers, dates and string as well. The Formatter function is available within the BIRT Functions Category of the expression builder. To build the example above, use the following expression in data item.

var mydtf1 = Formatter.format( params["date1"].value, "MMM d, yyyy");
var mydtf2 = Formatter.format( params["date2"].value, "MMM d, yyyy");
"Start Date: "+mydtf1 + " End Date " + mydtf2 + BirtDateTime.diffDay(params["date2"], params["date1"]) +" Days";

In this example we have combined the start and end dates with the BirtDateTime.diffDay function to build a string that not only shows the start and end date, but shows the duration of days.

The Formatter function takes the current locale into account when formatting the element. If you want to test different locales, either set the preview locale in the preferences or use the __locale parameter in your report URL.

As a side note, if you are interested in adding your own functions to the expression builder, see this post.

You can also import Java classes to manipulate data points within the BIRT or Chart event handlers. For example to set your own format on an axis label the following script could be used on a chart.

function beforeDrawAxisLabel( axis, label, context )
if( context.getExternalContext().getScriptable().getParameterValue("MyParameter") == "date"){
if (axis.getType() == AxisType.TEXT_LITERAL)
value = label.getCaption().getValue();
var dtf = new SimpleDateFormat("MM/dd/yy");
var dt = new Date(value);
var fn = dtf.format(dt);

As another example, the code below could be placed in a report parameter’s getDefaultValueList event handler to return the current year.

importPackage( );
cal = Calendar.getInstance();
tyear = cal.get(Calendar.YEAR);