Open Source Business Intelligence software is finally coming into its own, with three major players coming to the fore: JasperReports, Pentaho, and BIRT (Business Intelligence and reporting tools). Business Intelligence technology comprises solutions for delivering enterprise data in the form of customizable reports, facilitating such practices as data mining and decision support systems. Of the three leading projects, both JasperReports and BIRT are commercial Open Source; they are backed by JasperSoft and BI veteran Actuate respectively. Pentaho uses the more traditional collaborative Open Source model. One of BIRT's distinguishing features is that it's an official Eclipse project, giving it a strong endorsement by one of the leading forces in the Java community. Our team chose BIRT after some due diligence on the other two because of our prior experience with Actuate and deep appreciation of all things Eclipse.

This article presents a "first experience" approach to BIRT. We developed a simple BIRT report table of "sales broken down by region and salesperson" using a hypothetical database. The final PDF report preview is reproduced in Figure 1. BIRT has a rich set of layout capabilities that are only hinted at in this article, but will allow report designers the freedom to provide richly designed formatted reports. The actual report generation is done via BIRT's Report Engine API, which can be combined with a report designed to generate BIRT PDF reports from any Java Application.

Getting Started
BIRT 2.0 has a bit of a tricky install process, which makes one wonder if it's harder than needs be to "advertise" for the commercial Actuate version whose only additional virtue, other than support, is a standard complete installer. In any case, carefully follow the instructions at www.eclipse.org/birt to get it up and running in Eclipse. If installing a new version of Eclipse with BIRT, we recommend that you install it to a new directory instead of over an old Eclipse version...this caused us problems in the past.

You'll want to create a small database table with some test data. You can download our sample set at the link at the end of the article. When you restart Eclipse, create a new project by clicking "New Project...Business Intelligence and Reporting Tools...Report Project." Then to create a new report, click Window...Open Perspective and select Other...Report Design, then select File...New...Report and Report templates: Blank Report to set up a new report.

Setting Up Database Access
The first step in setting up a report is telling BIRT how you want to do the JDBC access to your database. To do this, BIRT needs to know about your database driver and login information. Click the "Data Explorer" tab and right-click Data Sources...New Data Source and select JDBC Data Source. On the next page, click Manage Drivers...and Add the jar that contains the JDBC drivers for your database. Click the Drivers tab to verify that the drivers from the selected JAR file have been added then click OK. Select your driver from the Driver Class dropdown, enter the DB URL (e.g., "jdbc:mysql://localhost:3306/birt_sample"), username, and password. Click "Test Connection" to verify and now you have a source for data for your report.

Getting Data
Now that you have a Source, you'll need to set up a Data Set, which is the set of data that will be used for the current report. This is typically an SQL query on your Data Source. Right-click Data Set...New Data Set and give your data set an appropriate name. Select the Data Source you just set up, if it's not already selected. When you hit OK, BIRT will bring up the Data Set wizard, giving you a window to enter your SQL query.

Try to choose a query that has both hierarchical and additive information. For this article, we'll assume if you have a table called "Sales"; you might write a query to get all regional and salespersons' totals (see Figure 2).

Your Data Set will now be listed under Data Sets. Expand it and you'll see the fields that BIRT has found in the database, in our case SALESPERSON_NAME, REGION_NAME, AMOUNT. Let's go ahead and create a report table using this data.

Setting Up the Report
Our business requirements for this report are to have a "Region" header, an indented list of salespeople and their individual totals, a regional total at the end of each Region section, and a grand total at the end of the report.

Let's start with the creation of the report header. Click the Palette tab in the upper left corner. The Palette shows you all the tools that will be used for creating report objects. Start with a Text object to be the header. Click "Text" and drag the object to the Report Workspace in the middle of the screen. This will bring up the "Edit Text Item" window that gives you a full set of CSS-compliant HTML tags to format your text. Switch from "Plain Text" to "HTML/Dynamic Text" to enable the HTML tags. Since this is the report header, let's make it H1. Note that BIRT inserts the open and close tags for you when you click the H1 button. Let's also choose a sans-serif font like Verdana by clicking the FONT tag. Enter Verdana for face (leave the size and color tags blank...they'll be ignored). Input a title for your report like "Regional Sales Report" inside the FONT tag.

To get the appropriate hierarchical totaling behavior for our report, we're going to use a set of "Grouped" tables. In the palette window, select a Table to hold the regional level of data. Click and drag the table object to the report workspace in the middle of the screen. Every table is made up of three components, the Header, Footer, and Details (see also the "Reporting Terminology" box). In our case, the table will have a Region header, a details section with individual salespeople, a Region subtotal footer, and a Grand Total footer. Note that only one footer is allowed for a Table object so we'll be grouping multiple tables to achieve the desired behavior.

The intuitive way to design a multi-level table is to start from the most detailed level and work your way out. In our case, that's the individual salesperson. Drag a table object into the workspace and select two columns (which will be Name and Total) and one Detail. The table will be created with Header, Detail, and Footer sections. Go to Data Explorer and drag the "Salesperson's name" into the left Detail cell. BIRT helpfully adds the database column name into the Header row, which can be edited by double-clicking. We want this level of detail to be indented from the Regional totals. To do this, click the left detail cell. The Properties tab at the bottom of the screen will change to show only properties that all selected elements share, which includes Padding, the one we need for our indentation. Set the Left Padding to 15 points (see Figure 3).

Grouping and Aggregating Data
Now we want to tell BIRT to divide up our salespeople by region. This is done by "Grouping" them, much in the same way you would using a "GROUP BY" clause in SQL. Mouse over the table object in the worksheet area and a tab marked "Table" will appear in the lower left corner of the object. Right-click on this tab to get a menu of Table-level commands. Since we're moving from more granular information (salespeople) to less granular (regions), select Insert Group. This opens the Grouping dialog box. I named the group "Region" and chose to group on the REGION_NAME field from my Data Set. I left group header and group footer checked, since the header will have the Region name and the footer will have the Region subtotal. BIRT automatically puts the grouping data element in the Group Header. I'd like to label it as a "Region" so I highlight it and click "Expression" to change this text from a simple data item to a calculated expression. BIRT uses standard JavaScript as its expression language, but in this case, it's a simple String concatenation that's the same in Java and JavaScript: "Region: + row["REGION_NAME"].

In the group footer, we want to have the Regional Total. To insert the calculated information, go to the Palette and drag a Data item into the Group Footer Table Cell. A Data item is the same as a text item, but it brings up the Expression Builder window by default. We want the sum of the TOTAL column so select "BIRT JavaScript Objects...Total (Aggregate) Functions...sum." This puts the "Total.sum()" method into the builder. We want to pass the AMOUNT field as an argument so go select it from "Available Data Sets." We want to label our total so the final expression looks like: "Regional Total: " + Total.sum(row["AMOUNT"]). Copy this onto the clipboard and put a new Data item into the lowest Footer row: "Grand Total: " + Total.sum(row["AMOUNT"]) (see Figure 4).

Formatting Text
As a final touch before we preview our report, let's set the fonts settings for our table. Click in the empty lower right-hand cell and drag a selection box into all the table cells. Choose the same font that was used in the header, Verdana, this time using the Font properties tag in the Property Editor at the bottom of the screen. Highlight the Group Footer and Header rows and make them bold and give them some additional top and bottom spacing to separate the report sections. Highlight the top Header row and make it Bold, and give it a double lower border in the Borders properties tab (Style: [double line], click bottom border button).

Highlights
A very common request in Business Reports is to alternate the background colors for each line in the report. To accomplish "dynamic" behavior like this we use Highlights, or value dependent properties. Select the detail row in the table, click the Property Editor then click Highlights in the bottom tabs. Click "Add" to add a highlight condition. Under "If the following condition is true:" put "row[0] % 2" (row[0] is the row count variable in BIRT), leave "Equal" in the middle box, and put "0" for the value. Change the "Background Color" from Auto to a suitable alternate color. This will cause all even rows to be highlighted with the indicated color value (see Figure 5).

Previewing Your Work
There are two ways to preview your work. BIRT provides a simple "Working Preview" in the "Preview" tab at the base of the report. Click it now to see your report. If you want to see a report with full pagination, select File...Preview as PDF from the top Eclipse menu. This will bring up an Eclipse PDF viewer window showing the fully rendered report with all headers/footers and pagination.

Achieving Open Source Business Intelligence
At this point, you should have a good initial understanding of the BIRT suite and its use. There are more advanced tutorials (creating sub-reports and scripting) available on the BIRT site at www.eclipse.org/birt and some excellent documentation in the "BIRT Developer Guide" in the Eclipse help at http://help.eclipse.org. Look there for next steps with your current report, like "Deploying a BIRT report to an application server."

BIRT isn't quite at the same level as a commercial solution like Business Objects, Microsoft Reporting Services, or Actuate, but it's encouraging how much one can do with this initial version. With sufficient grassroots support from loyal users, BIRT should become one of the most important and visible elements of the Eclipse suite, and rightly so (Table 1).

Resources

• BIRT: www.eclipse.org/birt
• Pentaho: www.pentaho.org
• JasperSoft: www.jaspersoft.com