Creating a Reporting Site using BIRT

Exclusive offer: get 50% off this eBook here
Joomla! Social Networking with JomSocial

Joomla! Social Networking with JomSocial — Save 50%

Learn how to develop a high quality social network using JomSocial

$20.99    $10.50
by John Ward | July 2010 | Java Open Source

In this article series by John Ward, author of BIRT 2.6 Data Analysis and Reporting, we will be looking at a practical example of building a reporting site. In this  part of the article, we will be looking at a scenario where we have a Bugzilla instance setup with a series of bugs related to BIRT. We will look at a series of requirements and build the reports necessary to fulfil those requirements.

The environment

In this setup, we are utilizing a Bugzilla 2.22.1 instance set up on Ubuntu 7.10 Gutsy Gibbon. Bugzilla has been set up to run under Apache and is connected to a MySQL 5 database. While newer versions of Bugzilla and Ubuntu are available, the steps should remain the same.

There is a single product, called BIRT Book. Under this product, there are several components as illustrated in the following screenshot:

Requirements

The most important thing to have prior to building reports is the requirements. Imagine being a carpenter trying to build a house without any blueprints. We need to have some idea what we are trying to build before we undertake the task of building reports. The following list shows a set of requirements that we have for this project. These are actually fairly sparse in terms of requirements. In my experience, requirements range from an extremely detailed set of Use Case documents, to mock-ups done in spreadsheets or some graphic format.

These are the reports the users are looking for:

  • Detailed report about bug. Show who it is assigned to, take in bug ID as a parameter, will be target for all drill down hyperlinks in other reports.
  • Report to show overall status of issues. This report will drill down to a detailed list of issues.
  • A report showing list of bugs assigned to a developer when provided with login details.
  • Performance report for users, showing percentage of issues in the form of finished state versus open state.

Creating libraries

In the case of these reports, we have a pretty good idea what kinds of things we want reusable. First, we know that all of these reports will contain the same data source, a MySQL connection to the Bugzilla database. We will also want to create a consistent header and layout for the reports. So, we will create a library containing the data source and the header, and create a template containing both. We will also create the stylesheets we want to use to make things consistent throughout the reports. Let's get on with this:

  1. Create a new reporting project called Bugzilla Reports.

  2. Create a new library called bugzillaReportsLibrary.rptLibrary in the newly created project.

  3. Switch to the Outline view in bugzillaReportsLibrary.rptLibrary. Create a new JDBC data source called bugzillaDataSource.

  4. As we are using MySQL, we need to use the Manage Drivers dialog under the Data Source setup to install the MySQL JDBC driver. The following is the dialog screen where we edit the MySQL Connector-J driver to have a description and template JDBC URL:

  5. Input the correct JDBC URL and driver for Bugzilla.
  6. Select the Themes option under the Outline tab.
  7. Change the name of the theme to bugZillaTheme.

  8. Create a new custom style called masterPageHeader.

    Creating a Reporting Site using BIRT

  9. Input the following parameters for the Style: Under the Font tab:
    • Background color: White
    • Weight: Bold
    • Font: Sans-Serif

    Under the Background tab:

    • Background color: RGB(64,0,128)

  10. Select the Master Pages element under the Outline tab. Change the name of the Master Page from Simple Master Page to BugzillaMasterPage.

  11. Select the Master Page tab in the Report Designer.
  12. Insert a grid into the header with 1 column and 1 row.

  13. Insert a Label component into the grid cell.
  14. Enter the text as Bugzilla Reports.
  15. In the Report Designer, right-click on the Grid, and select Style. Under Style, select Apply Style | bugzillaTheme.masterPageHeader. The following is the master page with the grid and style applied:
    Creating a Reporting Site using BIRT
  16. Create a new dataset called getAllBugs. Use the following query:

    SELECT
    bugs.bug_id,
    bugs.bug_severity,
    bugs.bug_status,
    bugs.short_desc,
    profiles.userid,
    profiles.login_name,
    profiles.realname,
    components.id,
    components.name,
    components.description
    FROM
    bugs,
    profiles,
    components
    WHERE
    bugs.component_id = components.id
    AND bugs.assigned_to = profiles.userid

  17. In the project, create a new template called BugzillaReportsTemplate.rptTemplate.

  18. In the newly created template, open the Resource Explorer tab and the Outline tab. Drag-and-drop the bugzillaDataSource, getAllBugs, and BugzillaMasterPage components from the library to the template.

  19. From the Outline, delete the Simple Master Page from the template.

  20. Save the template.
Joomla! Social Networking with JomSocial Learn how to develop a high quality social network using JomSocial
Published: July 2010
eBook Price: $20.99
Book Price: $34.99
See more
Select your format and quantity:

First report—bug detail report

Now that we have the groundwork laid for our project, we can start building the actual reports. In a typical situation, one wouldn't know beforehand every possible element to add to a report library. So, in this next example, we are going to build the report, then edit it to add some formatting elements such as styles that will be used in the remainder of the reports.

The following report is fairly straightforward. We already have the query to retrieve information about bugs from the template we will use, so we will need to modify it in two ways. First, we need to parameterize it so that we return only the bug we are looking for. We will also need to pull in the bug history. These are both simple modifications, so let's take a look.

  1. Open BugzillaReportsTemplate.rpttemplate.
  2. Save this report as BugzillaDetailReport.rptDesign.
  3. Edit getAllBugs dataset.
  4. Edit the query to read like the following example:

    SELECT
    bugs.bug_id,
    bugs.bug_severity,
    bugs.bug_status,
    bugs.short_desc,
    profiles.userid,
    profiles.login_name,
    profiles.realname,
    components.id,
    components.name,
    components.description
    FROM
    bugs,
    profiles,
    components
    WHERE
    bugs.component_id = components.id
    AND bugs.assigned_to = profiles.userid
    and bugs.bug_id = ?

  5. Create a new dataset parameter and call it bugID. Link it to a report parameter.
  6. Save the changes to the dataset. From the Outline tab, right-click and choose rename. Rename the dataset to getBugByID.
  7. Now, we want to create a Table element in the report that displays the bug information vertically instead of horizontally. To start, drag getBugsByID to the Report Designer.
    Creating a Reporting Site using BIRT
  8. Delete the header and footer rows.

  9. Insert four rows into the Detail section.

  10. Move the following fields into the order as illustrated in the following screenshot. Remove the remaining columns.
  11. Add descriptive labels to each row as seen in the following screenshot:

     

  12. Now we want to create two Styles in our library and embed them into the report. This way the styles will be available in other reports also. Open BugzillaReportsLibrary.rptLibrary.
  13. Under the Themes section, create a new style called BugDescriptionHeaderLabel.
  14. Use the following attributes.Use the following settings for Font:
    • Color: White
    • Size: Large
    • Weight: Bold

    For Background:

    • Color: Blue

    For Border:

    • Top: Solid
    • Bottom: Solid
    • Left: Solid
    • Colors: Black
  15. Create a new style called BugDescriptionHeaderData.Under the Border tab:
    • Top: Solid
    • Bottom: Solid
    • Right: Solid
    • Colors: Black
  16. Save the library.
  17. We have to close BugDetailReport.rptDesign before the new styles become visible. Go ahead and do so and reopen BugDetailReport.rptDesign.
  18. In the Outline view, select the root element. Apply the bugZillaTheme theme.

  19. In the Report Designer, select the column containing the labels.
  20. In the Property Editor, select the Advanced tab. Under style, select BugDescriptionHeaderLabel.
  21. Select the column containing the data and apply the BugDescriptionHeaderData style.

So we have created the header for the report. Now, we want to see the details of this bug. The details are basically the bug history of who changed fields, added fields, changed the bugs status, and a resolution.

  1. Create a new dataset called getBugHistory using the following query:

    select
    bugs_activity.bug_when,
    bugs_activity.added,
    bugs_activity.removed,
    profiles.realname,
    fielddefs.name
    from
    bugs_activity,
    profiles,
    fielddefs
    where
    bugs_activity.who = profiles.userid
    and bugs_activity.fieldid = fielddefs.fieldid
    and bug_id = ?

  2. Add in a new parameter and bind it to the report parameter bugID.

    Creating a Reporting Site using BIRT

  3. Drag the new dataset over to the Report Designer.
  4. Update the header labels as illustrated in the following screenshot. In the screenshot, we have also moved the column containing the Field Updated field to the second column.

  5. Now, we need to create styles for the detail row. We want to create these in the library also so that they are reusable in our other reports. Open the BugzillaReportsLibrary.rptLibrary.
  6. Create a new style called DetailTableHeader.
  7. Use the following settings for the style:Under the Font tab:
    • Weight: Bold

    Under the Background tab:

    • Background color: RGB(128,128,255)
  8. Create a second style called DetailTableRow. Under the Edit Highlight tab, enter row_rownum % 2 as the expression and set the Background color to Silver.
  9. Save the library. Again, close BugzillaDetailReport.rptDesign and reopen.
  10. In the header row for getBugHistory table, apply the DetailTableHeader style.
  11. In the detail row, apply the DetailTableRow style.
  12. Save the report and preview it.
    Creating a Reporting Site using BIRT

Continued part of this article

Joomla! Social Networking with JomSocial Learn how to develop a high quality social network using JomSocial
Published: July 2010
eBook Price: $20.99
Book Price: $34.99
See more
Select your format and quantity:

About the Author :


John Ward

John Ward is a consultant for Innovent Solutions, specializing in BIRT and e-commerce search and navigation solutions. Prior to that, John was an Assistant Vice President for Citibank, North America, managing the training MIS group and overseeing development of new technology-based training initiatives. John actively works with and tests BIRT—an open-source reporting platform built on Eclipse—including development work based on BIRT reports and the BIRT APIs.

John also maintains The Digital Voice blog at http://digiassn.blogspot.com

Books From Packt


JasperReports 3.6 Development Cookbook
JasperReports 3.6 Development Cookbook

Liferay Portal 6 Enterprise Intranets
Liferay Portal 6 Enterprise Intranets

Microsoft Dynamics NAV 2009 Application Design
Microsoft Dynamics NAV 2009 Application Design

Drupal E-commerce with Ubercart 2.x
Drupal E-commerce with Ubercart 2.x

Pentaho 3.2 Data Integration: Beginner's Guide
Pentaho 3.2 Data Integration: Beginner's Guide

IBM Cognos 8 Report Studio Cookbook
IBM Cognos 8 Report Studio Cookbook

Oracle Siebel CRM 8 Installation and Management
Oracle Siebel CRM 8 Installation and Management

Oracle SOA Suite 11g R1 Developer's Guide
Oracle SOA Suite 11g R1 Developer's Guide


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
i
h
H
z
w
P
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software