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:
- Create a new reporting project called Bugzilla Reports.
- Create a new library called bugzillaReportsLibrary.rptLibrary in the newly created project.
- Switch to the Outline view in bugzillaReportsLibrary.rptLibrary. Create a new JDBC data source called bugzillaDataSource.
- 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:
- Input the correct JDBC URL and driver for Bugzilla.
- Select the Themes option under the Outline tab.
- Change the name of the theme to bugZillaTheme.
- Create a new custom style called masterPageHeader.
- 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)
- Select the Master Pages element under the Outline tab. Change the name of the Master Page from Simple Master Page to BugzillaMasterPage.
- Select the Master Page tab in the Report Designer.
- Insert a grid into the header with 1 column and 1 row.
- Insert a Label component into the grid cell.
- Enter the text as Bugzilla Reports.
- 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:
- 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 - In the project, create a new template called BugzillaReportsTemplate.rptTemplate.
- 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.
- From the Outline, delete the Simple Master Page from the template.
- Save the template.