• Home
• Features
• Forums
• Announcements
• Newsletter
• Podcasts
• About
• Write for us

Business Intelligence and Reporting Tools Project

The Open Source community is fortunate enough to have a large collection of excellent free tools at its disposal. From such great software packages as Apache, Eclipse, and OpenOffice.org, stakeholders are finding that an increasing number of common business tasks are easily done using open source alternatives to proprietary software. However, reporting has been one of the areas severely lacking in the Open Source world. Thanks to the Eclipse Foundation and Actuate, this is being addressed with a tool developed in the Eclipse framework—BIRT.

Introduction

BIRT stands for Business Intelligence and Reporting Tools. It is an open source report development environment built on top of the Eclipse framework. BIRT has two major components: a graphical report designer that targets J2EE platforms for report development, and a Java application that resides on either a report delivery server or embedded into Java applications. An optional Chart Engine provides basic bar, pie, and line charts into BIRT reports and Java applications.

The BIRT report designer is a very robust development environment. There are several key components within BIRT. All the familiar elements of web graphical editors are present, including a layout designer, a component palette containing report items, and a property editor. Anyone who has worked with visual web page editors such as Macromedia Dreamweaver, Nvu, or even Microsoft FrontPage should be familiar with the workings of the WYSIWG layout editor provided by BIRT. Dreamweaver users should feel right at home with working with the visual data table components. In the graphical designer, there are three main tabs: Layout, Code, and Preview. The Layout tab provides a graphical layout editor and works with the property editor and component palette. Code allows custom scripting for report events, and Preview presents real-time representation of the current report.

Data connectivity is built using the Data Explorer and Data Set objects. The Data Explorer allows you to build connections to databases via Data Source components, while Data Sets provide functionality similar to record sets that other various programming languages offer. BIRT supports a variety of database platforms through its JDBC connection mechanism, and access can be extended via the Open Data Access framework. BIRT provides an Expression Builder for building calculated fields off of data and report elements. BIRT is extensible through scripting via Java and JavaScript libraries for working with BIRT objects and a rich set of API’s for programmatically creating reports.

For report deployment, BIRT separates the report designer from the delivery layer. To implement the deployment layer, BIRT includes a runtime component that plugs into an application server. Report designs are then published to this server for delivery. This adds a lot of flexibility in the delivery method by allowing developers to customize the look and feel of the requesting forms and the ability to leverage other plug-ins on the base platform. This is covered in more detail in my articles Birt Report Server Pt 1 and BIRT Report Server Pt. 2.

Demonstration

The remainder of this article discusses the basics of installing BIRT into Eclipse, and demonstrates building a simple report off of an Oracle database to familiarize the reader with the BIRT environment. The following installations are required before installing BIRT: a) Java 1.4 or 1.5; b) Eclipse 3.1 with the Graphics Editor Framework (GEF) runtime 3.1; and c) Eclipse Modeling Framework (EMF) runtime 2.1 plug-in. More information about the pre-requisites for BIRT can be located on the Eclipse BIRT prerequisite site. The database platforms JDBC drivers also must be downloaded. For this example, Oracles Oracle 9i Release 2 JDBC drivers are used. Oracle provides these free of charge from their website.

BIRT is available for download here. The version of BIRT used in this article is  1_0_1 release. Download the birt-report-framework-1_0_1.zip. Extract to the folder where Eclipse is installed, as illustrated in Figure 1.

Figure 1 - Extract the BIRT archive into Eclipse

Start Eclipse from the File menu, selecting “New,” then “Project.” A new category called “Business Intelligent and Reporting Tools” with a sub-category “Report Project” appears. This is illustrated in Figure 2.

Figure 2 - Select new BIRT Report Project

Select “Report Project” and click the Next button to start the Report Project Wizard. For this example, call the project “Simple Employee Report”. If this is your first time running the wizard or if you are not in the Report Designer perspective, a prompt to open a new Eclipse Perspective displays. Select Yes to continue. Once complete, a new perspective and blank report design are ready for editing. Figure 3 depicts the report design perspective.

Figure 3 - The BIRT Report Design Perspective

To begin, create a new report design. Right mouse click in the Navigator pane, select New, then  Other;  under the “Business Intelligence and Reporting Tools” category, select “Report.”  This starts another Wizard prompting for the name of the individual report item. For this example, name the report design “employee_list.rptdesign” and choose “Simple Listing” as the report type. A blank report appears in the layout pane.

Next, create the database connection in three steps: first, add the Oracle JDBC drivers, then create a data source, and finally input the query into the report.  Begin by extracting the JDBC drivers to a temporary location. On the Data Explorer tab, right mouse click on the Data Source list box item and select “New Data Item.” Then select the JDBC Data Source. Once the Create New Data Source Dialog appears, click the “Manage Drivers” button. Under the JAR Files tab, click the “Add” button. Point the File Explorer Dialog to the location where you saved the JDBC drivers. If your JDBC drivers came in a .zip file instead of a .jar file (as is the case with Oracle 8i JDBC drivers) you can alternatively change the file type filter to show .zip files. Click “OK”, then change the Driver Class to “oracle.jdbc.OracleDriver”, and input the Database URL. Figure 4 shows the completed dialog with the Oracle JDBC URL syntax.

Figure 4 - New Data Source dialog

Click Test Connection to test whether the drivers are working correctly, and then click Finish to complete the Data Source setup.

To create a Data Set, right mouse click on the Data Sets list item under the Data Explorer tab, and choose “New Data Set”. Title the dataset “GetEmployeesQuery”. (Due to the size of my database, it takes quite a bit of time for the next dialog box to pop up; hopefully this is something that will be fixed in future releases of BIRT.) Figure 5 shows the query used to retrieve the employees in this example.

Figure 5 - Edit Data Set dialog, with an example entry

The question mark represents the parameter to be filled in by the user. This should be a familiar concept to anyone who has worked with parameterized queries in ADO or a similar mechanism. After filling in the parameter, click on the Parameters list box item on the left, and fill in the appropriate information. Click the “OK” button to complete the Data Set. For this example, fill in the parameter name as employee_id, the parameter type as string, set the direction to input parameter, and the default value, which is a mandatory option. Since the Employee ID is a string, the default value needs to be enclosed in single quotes in order for the preview to show up.

The completed Data Set displays the fields from the query. In the report designer, three sections are available for data tables: header (report column labels), detail (data set rows returned), and footer (aggregate calculation labels such as count, sub, and average). Drag the fields from the Data Set to the details row. Figure 6 shows the Report Detail section after the query fields are dragged into the data table.

Figure 6 - The completed Data Set display

Design parameters and Data Set parameters are separate entities, and need to be created apart from each other. To create the design parameter, right mouse click on the “Report Parameters” list box item in the Data Explorer and create a new Parameter called “Employee_ID_Parameter” and set the data type to String as pictured in Figure 7.

Figure 7 - The Parameter dialog box

To bind the design parameter to the data set parameter, start by selecting the table in the Layout tab. When the mouse cursor is moved over the table, a small tab labeled “Table” appears; click the tab to select the table. The Property Editor changes at this point, adding multiple tabs along the bottom. Select the Binding tab. The Data Set parameters are now displayed; click on the cell in the value column for the parameter to bind and open the Expression Builder. Click on “This Report’s Parameters”, and then select the visible Parameter’s name. This automatically inserts the parameter expression. Click “OK” to proceed.

Preview the report by clicking the Preview tab in the design pane. Expect to be prompted to enter in the value for the report parameter. Once the value is entered, the report result is received. Change the parameter by clicking on the “Show Reports” button. The completed report is shown in Figure 8.

Figure 8 - Finished Report Preview

Conclusion

This report illustrates some of the basics of working in the BIRT environment. I have provided additional examples in my series of articles about Sguil Incident Reporting using BIRT, and BIRT report deployment with Apache Tomcat (part 1, part 2).  The BIRT developers also provide several examples at the BIRT examples page. BIRT already offers features that contend with its commercial counterpart (Actuate’s ERD Pro) such as dynamically generated parameter lists. With a growing user community and strong commercial backing, BIRT has enormous potential to really demonstrate the capabilities of the open source development paradigm.