BIRT 2.6 Data Analysis and Reporting
BIRT is an open source business intelligence and reporting tool, built on top of the Eclipse Framework. BIRT is used by developers for building reports that can best represent data and tell a story of that data that is easy to follow. In addition, BIRT can be integrated into a product, to allow that product to provide reporting capabilities. This book will walk the user through the basics of building reports with BIRT and introduce them to the various sections of the BIRT environment.
What This Book Covers
Chapter 1, Getting Started, is an introduction to BIRT. It gives the reader an idea of the BIRT features, community landscape, and the various websites out there that are dedicated to BIRT.
Chapter 2, Installing BIRT, guides the user through the various ways of installing BIRT.
Chapter 3, The BIRT Environment and First Report, introduces the BIRT workspace environment by walking the reader through a simple report example.
Chapter 4, Visual Report Items, presents us with various visual report items that are available for use in a report design.
Chapter 5, Working with Data, discusses how to retrieve data from databases, fl at text files, web services, and other data sources.
Chapter 6, Report Parameters, explains how to get input from the report user, along with how to apply these report parameters in filtering data.
Chapter 7, Report Projects and Libraries, looks at reusing report sections and sharing resources through libraries and report projects.
Chapter 8, Charts, Hyperlinks, and Drilldowns, shows how to build summary data using charts and linking sections of charts to reports. In addition, hyper linking used in online reports to link and show detailed data in other reports is discussed here.
Chapter 10, Deployment, looks at how does a developer can get reports out into the world for consumption.
Charts, Hyperlinks, and Drilldowns
It has been said that a picture is worth a thousand words. In the world of reporting, we call these pictures as Charts. With the help of charts, one can present a large amount of data with relative ease, in comparison to presenting with the help of huge data outputs. Charts are useful when used with raw data to drive home points such as percentages, or when used with other charts to build dashboards.
BIRT has an exceptional charting engine. BIRT can create a number of different Chart types, including the tried and tested pie chart, bar chart, and line charts. In addition, there are several other chart types that were extras to BIRT’s commercial counterpart such as the Meter Chart and Gantt chart.
In the following sections, we are going to look at building a few different charts centered around employee sales performance. First, we will build a pie chart that will illustrate the top employees based on sales. We will then create a gauge chart that will show the progress an employee has made to meet a quota. Finally, we will create a bar chart that will show sales performance for employees across a time period.
In the following exercise, we are going to look at how to build a pie chart. A pie chart is a very common report type used in business to display percentages. In this example, we will create a pie chart to show what percentage an employee contributed to the total amount of sales. This will give us a visual representation of who the top employees are in terms of sales.
- Create a new report titled Employee_Sales_Percentage.rptDesign.
- From the ClassicCarsLibrary.rptDesign library, add the dsClassicCarsdata source to the report.
- Create a new dataset called totalSales using the following query:
- Create two report parameters called startDate and endDate as date types and link them to the two report parameters. Use 2005-01-01 as the startDate’s default value, and 2005-05-01 as the endDate’s default value.
- Drag a chart component from the Palette to the Report Designer.
- In the Chart dialog, select Pie chart. Change the Output format from SVG to PNG. Typically, iuse either PNG or JPEG, as SVG is not a universally
supported format. However, SVG does have a distinct advantage over both PNG and JPEG in that SVG images are smaller in size due to the format’s vector nature.
- Open the Select Data tab. Under the Select Data section, select Use Data Set and choose the totalSales dataset.
- We can drag the column headers from the dataset to the slice definitions. Drag the SALES column to the Slice Size Definition and the NAME column to the Category Definition. To make selecting the correct header easier, we can use the Show data preview checkbox to see what data is in each column. Without it, the preview box will show only the column names.
- Select the Format Chart tab. Under the Title section, enter Employee Sales
Percentages as the chart title. In this editor, we change how the chart looks and feels.
- Click Finish.
- Resize the chart to take up report page. This can be done by dragging the corner of the chart or by entering the height and width values in the property editor.
- The chart will look something like the following screenshot when previewed:
select CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER, CLASSICMODELS.EMPLOYEES.LASTNAME || ', ' || CLASSICMODELS. EMPLOYEES.FIRSTNAME name, sum(CLASSICMODELS.ORDERDETAILS.PRICEEACH) sales from CLASSICMODELS.EMPLOYEES, CLASSICMODELS.ORDERS, CLASSICMODELS.ORDERDETAILS, CLASSICMODELS.CUSTOMERS where CLASSICMODELS.CUSTOMERS.SALESREPEMPLOYEENUMBER = CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER and CLASSICMODELS.ORDERS.CUSTOMERNUMBER = CLASSICMODELS. CUSTOMERS.CUSTOMERNUMBER and CLASSICMODELS.ORDERDETAILS.ORDERNUMBER = CLASSICMODELS. ORDERS.ORDERNUMBER and CLASSICMODELS.ORDERS.ORDERDATE between ? and ? group by CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER, CLASSICMODELS.EMPLOYEES.LASTNAME, CLASSICMODELS.EMPLOYEES.FIRSTNAME
So, we have built a fairly simple report so far. This report is a pie chart with a legend
that shows us the employee’s color code, with an excerpt that shows us the value of that slice.
Pie chart with explosions
While the pie chart we saw in the preceding section is nice, it would be a little easier
to view if the slices were exploded, and if the slices had an outline. Let’s take a look
at how to modify some of the chart’s properties to do this.
- Double-click on the chart in the report design. This will reopen the chart dialog. Another option is to right-click on the chart and choose Format Chart….
- Open the Format Chart… tab.
- Open the Value Series section.
- Under the Slice section, change the By Distance value to 6.
- For Slice Outline, choose the color black from the palette.
- Save the chart and preview it. The following screenshot shows what the chart
looks like with the explosion and slice outline added.
Pie charts—working with percentages
Things are starting to look better. But let’s say we want to change the chart to display
percentages instead of numbers and add a little interactivity to the chart to display the sales numbers when a section is clicked on.
- Double-click on the chart to edit it.
- Select the Value Series section.
- Click on the Labels button.
- Under Values, remove the Value Data option.
- Under Values, add Percentile Value Data.
- Click the Interactivity button.
- Choose Mouse Click from the Event listbox.
- From the Action listbox, choose Invoke Script.
- Click the Expression Editor button.
- Using the editor, click on the Variables | Chart Data Point | Value Data option to insert the valueData variable into the editor. Surround it with an alert() method call. It should look like the next screenshot:
- Click OK. Now click on the << arrows in the Series Interactivity window.
- Click Finish in the Chart Editor.
When we preview the report, the chart pops up, formatted in the manner we want. If we click on one of the pie slices, an alert window will pop up showing the value for that series.
With the next chart, the guage chart, we will expand on the interactivity a bit and demonstrate how drill-downs work. In addition to viewing the static chart, we want the user to be able to click on the chart and have it pull up an external report with the details for a particular user. This is called a drilldown.
The following meter chart will demonstrate an employees sales vs. a target amount per month, let’s say 3000, on a 5000 dollar scale. This will be broken out and grouped monthly in the query statement. When the user clicks on the chart, it will bring them to the detail report we created last chapter for Employee sales.
- Create a new report titled Employee_Sales_Guage.rptDesign.
- From the library, drag over the dsClassicCars data source.
- Create a new dataset called employeeSales using the following query:
- Link the two dataset parameters to the report parameters, startDate and endDate respectively.
- Drag the employeeSales dataset to the report designer.
- Delete the last two columns from the table.
- Create a group called GroupByEmployee in the table and set it to group on the EMPLOYEENUMBER.
- Create another group below GroupByEmployee and call it groupByDate. Set it to group on the ORDERDATE field.
- Merge all of the cells in the orderDate details row into one large cell. Be sure to delete any data items that are still present such as the NAME and EMPLOYEENUMBER items.
- In the new large cell, create a chart element.
- Select Meter as the type, and select SuperImpose Meter as the chart type. If we need to know which type of chart we are picking, hover the mouse over the chart for a second and its description will pop up. Set the Output Format
- In the Select Data tab, create another dial by selecting the Meter Value Definition drop-down list and selecting <New Series..>.
- For the expression for Dial 1, select row["SALES"] by either dragging the column over from the Data Preview pane or by using the Expression Editor.
- The value for Dial 2 should be set to 3000.
- As Category Definition, use row["NAME"] by either dragging the column over from the Data Preview section or using the Expression Editor.
- In the Format Chart tab, under the Value Series section, click the Scale button.
- Enter 0 for the Min value and 5000 for the Max value. We have now set up the scale.
- On the Edit Chart tab, under the Legend section, uncheck the Visible checkbox.
- Select Chart Area.
- Click the Interactivity button.
- Select Mouse Click from the Event listbox and select Hyperlink from the Action listbox.
- Click the Add Button.
- In the Name field, insert NewHyperlink.
- Click the Edit Base URL… button.
- Select Drill-through as the hyperlink type.
- For the Report Design, navigate and select the report Employee Sales Details Report.rptDesign. This report was created in the preceding chapter.
- Select the startDate and endDate parameters under the Parameters drop down, which selects the parameters in the target report. Under Values, enter params["startDate"].value and params["endDate"].value, or whatever names that have been used used for the current report’s startDate and endDate parameters.
- Under Step 2, select Target Bookmark. From the drop-down list, select row["EmployeeNumber"].
- Under Step 4, select New Window.
- We can skip Step 5 as we are sticking to our default output format, which is HTML. We could have the target report open in any format that we have an emitter registered for. Emitters are plugins for BIRT that produce output in different formats such as HTML, PDF, Microsoft Excel, or Microsoft Word.
- Click OK to exit the dialog and save the report.
- Delete the table header.
- Delete the EMPLOYEENUMBER data element.
- Drag the EMPLOYEENUMBER column from the Data Explorer, next to the OrderDate report item.
- Now run the report. When we click on any of the charts, a detailed report will open and the control will automatically jump to the employees section, as specified in the Bookmark in the target report.
select CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER, CLASSICMODELS.EMPLOYEES.LASTNAME || ', ' || CLASSICMODELS. EMPLOYEES.FIRSTNAME name, sum(CLASSICMODELS.ORDERDETAILS.PRICEEACH) sales, rtrim(char(year(CLASSICMODELS.ORDERS.ORDERDATE))) || '-' || rtrim(char(month(CLASSICMODELS.ORDERS.ORDERDATE))) orderDate from CLASSICMODELS.EMPLOYEES, CLASSICMODELS.CUSTOMERS, CLASSICMODELS.ORDERS, CLASSICMODELS.ORDERDETAILS where CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS. ORDERDETAILS.ORDERNUMBER and CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER = CLASSICMODELS. CUSTOMERS.SALESREPEMPLOYEENUMBER and CLASSICMODELS.ORDERS.CUSTOMERNUMBER = CLASSICMODELS. CUSTOMERS.CUSTOMERNUMBER and CLASSICMODELS.ORDERS.ORDERDATE between ? and ? group by year(CLASSICMODELS.ORDERS.ORDERDATE), month(CLASSICMODELS.ORDERS.ORDERDATE), CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER, CLASSICMODELS.EMPLOYEES.LASTNAME, CLASSICMODELS.EMPLOYEES.FIRSTNAME
Of course, this example can be heavily modified to simplify things. The charts could be consolidated into single chart, with more needles for each month instead of separate charts for each month, by moving the chart into the EmployeeNumber group’s header or footer row and adding a group by date in the chart editors dialog under the Select Data tab. It is also possible to make the needles interactive and to filter down to a specific user and date range by modifying the bookmarks in the target report, but I will leave that up to the reader to discover. The following is a modified version of the the report that has created different series in the chart itself for the date groupings.
In the earlier section, we saw some of the power that charting can have with reports,by adding both the graphical representation of data and the interactivity for users to see the details of that data in a separate report. We also saw how to pass data through to the target report by using the expression. In this report, the hyperlink will take us to an internal bookmark, which means it will jump us to a location inside the same report containing the details for our report. We will also look at aggregating values inside the chart so that we can provide a simple query that just retrieves the necessary details and lets BIRT handle the tricky stuff for us.
- Create a new report called EmployeeSalesPerformanceReport.rptDesign.
- Use the dsClassicCars data source in the library.
- Create a new dataset called employeeSales, using the following query:
- Map the dataset parameters to report parameters startDate and endDate.
- Drag a table element over from the palette.
- Set Number of columns to 3 and Number of details to 1, and map the
dataset element to the employeeSales dataset.
- In the header row, insert a new row.
- In the top most header, merge all the cells into one large cell.
- Insert a chart element into the large cell.
- Select Bar chart as the type.
- Select the stacked Bar Chart as the subtype.
- Set the Output Format to PNG.
- Go to the Select Data tab.
- For the series value, drag in the PRICEEACH column.
- For the Category (x) Series, use the following expression:
- For the Optional Y Series Grouping, drag in the NAME column.
- In the Inherit Data From Container drop-down list, select Inherit Columns only. This will set up the chart to ignore any grouping at the table level and let the chart engine do its down grouping.
- Next to the Category (x) Series expression editor button , there is a button to
edit group and sorting. Click that button.
- Select Ascending from the Data Sorting drop down.
- In the Grouping checkbox, check the Enable option.
- Keep the other values at their defaults and click OK.
- Click Finish and save the report.
- Select the table, and create a group on EMPLOYEENUMBER.
- Create a grouping on OrderDate.
- For the detail row, drag over the PRODUCTNAME into the second column and PRICEEACH into the third column.
- From the Palette, drag over an Aggregation element into the groupFooter
for the OrderDate grouping.
- Create the aggregation on the PRICEEACH field for the salesDateGroup.
- Add in any formatting that might spruce up the look of our report.
- Select the OrderDate groupings header row . Select the Bookmark tab under the property editor. Use the following expression for the bookmark:
- Double-click on the chart to enter the Chart Editor.
- Select the Format Chart tab.
- In the Edit Chart window, select the Value (Y) Series section and click the
- In the Event drop-down list select Mouse Click, whereas in the Action
listbox select Hyperlink.
- Click the Add button.
- Enter ChartHyperlink for the Name.
- Click the Edit base URL… button.
- In the hyperlink dialog box, select Internal Bookmark as the type.
- Select the following from the drop down:
- Click OK to save the report. Now run it.
select CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER, CLASSICMODELS.EMPLOYEES.LASTNAME || ', ' || CLASSICMODELS. EMPLOYEES.FIRSTNAME name, CLASSICMODELS.ORDERDETAILS.PRICEEACH, CLASSICMODELS.ORDERS.ORDERDATE, CLASSICMODELS.PRODUCTS.PRODUCTNAME from CLASSICMODELS.EMPLOYEES, CLASSICMODELS.CUSTOMERS, CLASSICMODELS.ORDERS, CLASSICMODELS.ORDERDETAILS, CLASSICMODELS.PRODUCTS where CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS. ORDERDETAILS.ORDERNUMBER and CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER = CLASSICMODELS. CUSTOMERS.SALESREPEMPLOYEENUMBER and CLASSICMODELS.ORDERS.CUSTOMERNUMBER = CLASSICMODELS. CUSTOMERS.CUSTOMERNUMBER and CLASSICMODELS.PRODUCTS.PRODUCTCODE = CLASSICMODELS. ORDERDETAILS.PRODUCTCODE and CLASSICMODELS.ORDERS.ORDERDATE between ? and ?
(row["ORDERDATE"].getYear() + 1900).toString() + '-' + (row["ORDERDATE"].getMonth() + 1).toString()
This may not make much sense now, but we will explain it further in the next chapter.
row["EMPLOYEENUMBER"] + row["ORDERDATE"]
row["EMPLOYEENUMBER"] + row["ORDERDATE"]
Now, when we click on any of the different colored sections of the bar chart, it will bring us to the details section within our own report.
In this chapter, we have seen how to create several different chart types. We have seen how we can create interactivity within a single report and how to drill down into a separate report using charts and passing through parameters. We have seen how to aggregate data within a chart to serve as a summary for the more detailed data. We have opened the dialog to edit properties of charts and seen how a few of the properties affect the look and feel of charts as well as the behavior.
In the next chapter, we are going to explore the basic of scripting in BIRT reports.
Throughout this book we have been using a basic type of script called an expression. We will explore this in following chapters and expand into event handling as well.