Creating a Reporting Site using BIRT

Learn how to develop a high quality social network using JomSocial

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.

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

Books to Consider

comments powered by Disqus
X

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free