Creating a Reporting Site using BIRT-An Extension

Exclusive offer: get 50% off this eBook here
BIRT 2.6 Data Analysis and Reporting

BIRT 2.6 Data Analysis and Reporting — Save 50%

Create, Design, Format, and Deploy Reports with the world's most popular Eclipse-based Business Intelligence and Reporting Tool

£16.99    £8.50
by John Ward | July 2010 | Java Open Source

In the previous article How to Create a Reporting Site

In this article series by John Ward,author of BIRT 2.6 Data Analysis and Reporting we will be looking at :

  • Bug Status- A Report to show open issues compared to all bugs
  • Developer issues reports-this report will accomplish two things—shows us a Pie Chart showing fixed versus non-fixed bugs and gives us a list of bugs in an open status assigned to that developer.

Bug status

The next report we are going to undertake is the report to show open issues compared to all bugs. What we are trying to build here is a graph that shows, by category, the bugs that are open in relation to the total number of bugs for that category. No other grouping is required. Under the graph, we also want to show a list of open bugs that we can click on and have them drill down to the detail for that bug so that we can see the history of changes to that bug.

What this means is we need a bar graph showing all the status.

  1. Open BugzillaReportsTemplate.rptTemplate and save as bugStatusReport.rptDesign.
  2. Drag getAllBugs over to the Report Designer.
    Creating a Reporting Site using BIRT-an extension
  3. Delete all columns except the bug_status field and short_desc field.
    Creating a Reporting Site using BIRT-an extension
  4. In the table, group data by the components' name.
    Creating a Reporting Site using BIRT-an extension
  5. With the new category created in the Table, insert a new column on the right. Delete the header labels. Move the data fields to look like the following screenshot:
    Creating a Reporting Site using BIRT-an extension
  6. In the Outline tab, select the root element. Apply bugZillaTheme.
  7. In the group header row with the name, apply the DetailTableHeader style.
    Creating a Reporting Site using BIRT-an extension
  8. In the Detail row, apply the DetailTableRow style.
  9. In the header row, select all the cells and merge them.
    Creating a Reporting Site using BIRT-an extension
  10. In to the new merged cell, insert a chart.

    Creating a Reporting Site using BIRT-an extension

  11. Select a Bar Chart and change the Output Format to PNG.

    Creating a Reporting Site using BIRT-an extension

  12. Open the Select Data tab.
  13. Set the Inherit Data from Container drop-down list to Inherit Columns Only.
  14. Drag the bug_status field to the Optional Y Series Grouping slot.
  15. Drag the name field to the Category (X) Series slot.

    Creating a Reporting Site using BIRT-an Extension

  16. Click on the Edit group and sorting button.
  17. In the Group and sorting dialog, check the Enabled checkbox.
  18. Set the Type to Text.
  19. Set the Interval to 0.
  20. Set the Aggregate Expression to Count.

    Creating a Reporting Site using BIRT-an Extension

  21. As the Value (Y) Series, enter 1.
    Creating a Reporting Site using BIRT-an Extension
  22. Under the Format Chart tab, go to Title. Enter the title as Bug Status Report.

    Creating a Reporting Site using BIRT-an Extension

  23. Select the Axis option.
  24. Under X-Axis, check the Stagger checkbox.
    Creating a Reporting Site using BIRT-an Extension
  25. Click Finish.
  26. Resize the chart to fit the number of categories.
    Creating a Reporting Site using BIRT-an Extension
  27. The last thing we need to do is add the drill-through from the descriptions to the bug detail. Select the short_desc data item in the report designer.

    Creating a Reporting Site using BIRT-an Extension

  28. Under the Property Editor, select the Hyperlink tab.
    Creating a Reporting Site using BIRT-an Extension
  29. Click on the Edit... button next to Link To.
  30. From the Hyperlink dialog, select the Drill-through as Hyperlink type.
  31. Select BugzillaDetailReport.rptDesign as the target report.

    Creating a Reporting Site using BIRT-an Extension

  32. Set up the target report parameter bugID to be linked to row["bug_id"].

    Creating a Reporting Site using BIRT-an Extension

  33. Click OK and save the report
    .
    Creating a Reporting Site using BIRT-an Extension
BIRT 2.6 Data Analysis and Reporting Create, Design, Format, and Deploy Reports with the world's most popular Eclipse-based Business Intelligence and Reporting Tool
Published: July 2010
eBook Price: £16.99
Book Price: £27.99
See more
Select your format and quantity:

Developer issues reports

The next report is a combination of the last two reports in our list. Given a developer ID, this report will accomplish two things—shows us a Pie Chart showing fixed versus non-fixed bugs and gives us a list of bugs in an open status assigned to that developer. Let's now build the report:

  1. Open BugzillaReportsTemplate.rptTemplate and save as DeveloperPerformanceReport.rptDesign.
  2. Modify the getAllBugs query to look like the following:

    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 profiles.userid = ?

  3. Bind the dataset parameter to a report parameter called developerID.
  4. We want to use a drop-down list for the developer ID parameter. Create a new dataset to display the unique developer IDs using the following query and call it developerIDList:

    SELECT distinct
    profiles.userid,
    profiles.realname
    FROM
    profiles

    Creating a Reporting Site using BIRT-an Extension

  5. Open the report parameter developerID for editing.
  6. Change the Display type to Combo Box.
    Creating a Reporting Site using BIRT-an Extension
  7. For the Selection list values option, change the radio buttons to Dynamic. Under the list of datasets, select developerIDList.

    Creating a Reporting Site using BIRT-an extension

  8. For the value column, select userid.
  9. As the display text, use the following expression:

    dataSetRow["userid"] + " - " + dataSetRow["realname"]

  10. As the Default value, enter 1. The dialog should look like the following:

    Creating a Reporting Site using BIRT-an Extension

  11. Click on OK to save our changes.
  12. Drag getAllBugs dataset over to the Report Designer.
  13. Delete all columns except bug_id and short_desc.
    Creating a Reporting Site using BIRT-an Extension
  14. Insert a new row in the header above the column labels.

    Creating a Reporting Site using BIRT-an Extension

  15. Merge all the cells in the new row.
  16. Insert a chart into the new large cell.
  17. Select a Pie Chart and set the Output Format to PNG.

    Creating a Reporting Site using BIRT-an Extension

  18. Select the Select Data tab.
  19. Set the Inherit Data from Container drop down item to Inherit Columns only.
  20. Under the Category Definition, use the following expression:

    if (row["bug_status"].toUpperCase() == "RESOLVED")
    "Fixed";
    else
    "Open";

  21. Click on the Edit Group and Sorting button.
  22. Check the Enabled option under Grouping tab.
  23. Set the Type to Text.
  24. Set the Interval to 0.
  25. Set the Aggregate Expression to Count.

    Creating a Reporting Site using BIRT-an Extension

  26. Click OK.
  27. Under the Slice Size Definition, enter 1 for the value.

    Creating a Reporting Site using BIRT-an Extension

  28. Under the Format Chart tab, go to Title.
    Creating a Reporting Site using BIRT-an Extension
  29. Change the Title to Open Issue Chart.
  30. CLick Finish
  31. Resize the chart.
  32. Change the labels for Bug ID and Bug Short Description.

    Creating a Reporting Site using BIRT-an Extension

  33. Select the Detail row of the chart.
    Creating a Reporting Site using BIRT-an Extension
  34. Under the Property Editor, select the Visibility tab.
  35. Check Hide Element.
  36. Use the expression, illustrated in the following screenshot:
    Creating a Reporting Site using BIRT-an Extension
  37. Select the bug_id data element.
  38. Under the Property Editor, select the Hyperlink tab.
  39. Click the Edit... button.
  40. Create a drill-through to the detail report using the column binding's bug_id as the parameter value.

    Creating a Reporting Site using BIRT-an Extension

  41. Select the root of the report in the Outline tab, and apply bugzillaTheme.
  42. Apply TableHeaderStyle to the header row.
    Creating a Reporting Site using BIRT-an Extension
  43. As we used the visibility expression to hide rows that are resolved, this will throw the Highlight used in the DetailRow style off. So, we are going to use a little bit of scripting to apply our highlight. Open up the Outline view.
  44. In the Report Designer, open the Script tab.
  45. Select the Reports Root element and, in the Script editor, choose the initialize method.
  46. Type the code shown in the following screenshot in to the Script editor:

    Creating a Reporting Site using BIRT-an Extension

  47. Select the Detail row in the Outline tab.

    Creating a Reporting Site using BIRT-an Extension

  48. In the onRender method, type the following code:

    if (this.getRowData().getColumnValue("bug_status").toUpperCase() == "RESOLVED")
    {
        displayedRow++;
    }

    Creating a Reporting Site using BIRT-an Extension
  49. In the Property Editor, select the Highlight tab.
  50. Create a Highlight using the following code:

    displayedRow % 2 instead of row.__rownum

    Creating a Reporting Site using BIRT-an Extension
  51. Save and preview the report.

The reason this worked is because we needed to get the values in the data rows before the visibility rule takes effect. Once we have those components created, we check in the Render phase to see if the value of bug_status is set to the RESOLVED value. If not, then we advance the counter that would normally be advanced by ROWNUM. This keeps it nice and uniform.

Creating a Reporting Site using BIRT-an Extension

Summary

In this article we looked at a realistic example of a series of reports that could be used in a real life project.

We have illustrated just about every major element of reporting with BIRT, from Report Projects, Report Components, Charts, Scripting, and Formatting. We even looked at how to look at requirements and find reports with similar specs and data, and how to combine reports. If a single report can take the place of multiple reports and tell a more complete story, it is always beneficial to do so. In addition, we also introduced the Visibility rule, which is very similar to the Highlight rule. These reports can now be deployed to a report platform and be used for production reports.

BIRT 2.6 Data Analysis and Reporting Create, Design, Format, and Deploy Reports with the world's most popular Eclipse-based Business Intelligence and Reporting Tool
Published: July 2010
eBook Price: £16.99
Book Price: £27.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


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