Aggregate Update

When I did the Aggregate example, I noticed that I could not import the DataTypeUtil from the org.eclipse.birt.data project. To work around this, I just created my own DataTypeUtil class. I hate this kind of code, so I submitted a bug to have those class(s) exposed.

It turns out that the issue is the way in which I was adding the dependency classes.

DataTypeUtil is in the org.eclipse.birt.data project, so this item needs to be added to your dependencies. In addition, DataTypeUtil throws a BirtException. So you need to add the org.eclipse.birt.core package.




But there is a catch. The org.eclipse.birt.core package has a filter that acts to block the inclusion of items from the org.eclipse.birt.data project. So it can not be the last item in the dependency list. I have asked the developers to look and see if they can resolve this issue.

Scott

Building Crosstabs in BIRT

Update 07/2010
Details on BIRT Cubes and Crosstabs see this post.

The BIRT PMC Released the draft project plan for BIRT 2.2 last week. A Dynamic Crosstab Report Item is listed as one of the projects. This is an often requested feature and it is great to see that it is slated for the next revision of BIRT. That said, static crosstab reports can certainly be built using the current version of BIRT. Additionally, a fully dynamic crosstab can be built with the help of the Design Engine (DE) API, although the DE API approach requires a little more work and entails building or modifying a report design on the fly.

As background, Wikipedia states that a crosstab displays the joint distribution of two or more variables. In the world of reporting this usually means aggregating left to right and top to bottom.

What is often not stated is that a user is sometimes looking for the number of columns aggregated to be dynamic. We differentiate static crosstabs from dynamic crosstabs based on whether the number of columns in the crosstab is known at design time or at run time.

Krishna Venkatraman, from the BIRT team, supplied an example that is now posted on the BIRT web site that presents a neat static crosstab report. .



This report sums left to right and top to bottom. Additionally it is grouped by year and quarter. The columns of this report design were known before run time. To do the aggregration Krishna relied on the Total.sum built-in BIRT function. In each column the expression for the field is set to Sum(quantity ordered multiplied by price each). The Total.sum function allows a second parameter that filters what is included in the sum. In this case, the filter is set to the appropriate column.

For example, in the Planes column the expression is as follows:

Total.sum(dataSetRow["AllOrders::OrderDetails::QUANTITYORDERED"]*dataSetRow["AllOrders::OrderDetails::PRICEEACH"],dataSetRow["Products::PRODUCTLINE"]=="Planes").

This filters the values to only include planes within the sum. Additionally the BIRT Binding Editor allows further culling of the data by supplying an Aggregate On field, which allows the data to be aggregated on a group level. If the data element is in the Quarter group, the binding editor is set to only aggregate on the quarter. If the data item is in the Total, it aggregates all group levels.



To build a dynamic crosstab in the current version of BIRT, calls to the Design Engine API (DE API) are required. The DE API can be called from a Java application or built into a servlet. An example on the DE API Examples section of the BIRT Wiki should be a helpful starting point for this endeavor.


The Dynamic Report Servlet example shows how to open an existing report or template and add a table, data source and data set. The number of columns generated is decided at run time and are selected in the dynamic.html webpage.



After selecting the columns the form submit calls the example servlet which executes and displays the modfied report.



Adding column aggregations to create a true crosstab is an relatively simple extension, once you have a good understanding of how the DE API mechanism can be put to work to create dynamic tables.

BIRT Preferences

Report Design

As a developer, I look at my innate laziness as a virtue, I always try to automate the simple tasks. The BIRT report designer allows you to automate several of the routine development tasks through the preferences tab. I am just going to run the various preference options to illustrate what is available.

The options for BIRT Reports can be found under Window => Preferences => Report Design. You can quickly see that there are several sub - dialogs under Report design.

Chart

The first sub dialog is to set up preferences for charting, logically entitled Chart. You have to options here. The first thing to note is that for the Chart, preview refers to the view of the chart within the chart editor. As you work with the chart editor, selecting data sets and fields, the chart editor by default will show data from the actual query, using up to 6 rows of data. When the report actually runs, in the Preview tab, these settings are ignored.

If you do not want to run the query to the data when you are in the chart editor, or if you would like to adjust the number of data rows used to create th e preview image in the chart editor, adjust this dialog appropriately.

Comment Template

I always like to leave a comment block on any code that I write identifying who created the code, when it was created, etc. Every now and then someone doing maintenance on code that I created will contact me with questions on something I have written. Unfortunately, in the rush to get things done, it is way too easy to forget this step.

Fortunately, I can just set up my workspace to add the comments automatically. One less repetitive task to take care of.

Data Set Editor

The BIRT Data Set Editor dialog controls the behavior of all BIRT Data Set Editors. The first dialog controls the number of rows displayed in the preview dialog of the data set editor. This property is ignored when you a view a report using the Report Preview Tab.

JDBC Data Set

The JDBC Data Set dialog queries the database server looking at the schemas, and the tables within each schema. This information is used to populate the Available Items and Schema fields of the JDBC DataSet dialog. In some cases, this query can be a very expensive and time consuming activity. The Report Design => Data Set Editor => JDBC Data Set allows you to control the amount of database meta data that the Report Designer will query.

If you lower the limits on these items, the table or schema that you would like to query may not show up in the Available Items area, or schema drop-downs. The unshown items are still available, but the developer will need to know the actual names and will have to write (or copy) the appropriate sql query.

XML Data Set

The XML Data Set will only pull in a limited number of rows from an XML data source.

Element Names

You can create custom name patterns for your BIRT elements. This is particularly useful if you plan on writing BIRT Script and you want to control the names of the controls. This topic was all ready covered in this post.

Preview

Okay this is the third time that I talk about Preview, and now I am actually going to talk about the preview that occurs when you run and view a report. The first interesting feature is for those report developers that will be deploying into multi-lingual environments. Through the locale drop-down, you can control which locale your report will use when it runs, without modifying the machine locale settings.

The next check boxes control the report behavior when running in an external browser. Simply click on the Preview tab in the Report Perspective, brings up the standard built in report browser. When you run the report in a browser (through the File menu or the View Report icon) you will either use your default browser, or the browser of your choice.

The Maximum number of rows to display option works on both the Preview tab and when you View Report as HTML (File menu or Icon). Essentially, this allows you to see a section of a slow running report much quicker. This option does not impact a report when run in the Web Viewer or when run through the Report Engine.

Preview Server

Eclipse ships with a Tomcat web application server. This app server is used to server up the Eclipse help files. In addition, BIRT uses the web server to display reports in preview mode. By setting the server and port values in this dialog, you can allow other users to use the Tomcat server running under your Eclipse instance to preview report designs you have created. I have included the URL that you would use assuming that your server name is my_server and the port you want to use is 9999.

http://my_server:9999/viewer/frameset?
__report=c:\workspace\work_birt_sdk\my_reports\quick.rptdesign
&__format=html
&__svg=false
&__locale=en_
&__designer=true
&__masterpage=true
&__rtl=false
&__maxrows=500
&__resourceFolder=C:\Program+Files\Actuate\BRDPro\resources&-49158193

Resource

There are two basic types of resource files. Image files and localization properties files. These types of files are often shared between developers. Setting the Report Design => Resource => Resource folder allows developers to locate these shared files in a common network repository.

Template

Templates allow developers to simplify repetitive report design tasks. Typical team development environments will want to be able to create and share report templates. By default, templates are stored in the following directory:

$ECLIPSE_HOME/plugins/org.eclipse.birt.report.designer.ui_version/templates

Modifying the report => templates location allows developers to share report templates from a common network location.