 | Level: Intermediate Craig Tomlyn (tomlyn@us.ibm.com), Senior Software Engineer, IBM
Abdul Rasid (abdul.rasid@in.ibm.com), Software Engineer, IBM
Yan Larsen (ylarsen@us.ibm.com), Software Engineer, IBM
09 Aug 2007 The open source, Eclipse-based Business Intelligence and Reporting Tools project brings advanced reporting capabilities to Information Management products such as DB2® Data Warehouse Edition and WebSphere® RFID Information Center. This article shows you how to go beyond the basics to implement additional functions to meet the detailed reporting needs of your user community. The instructions in the article are based on BIRT V2.1.2.
Introduction
The Business Intelligence and Reporting Tools (BIRT) project is an open source project based on Eclipse technology. BIRT provides report design features that allow a report developer to create sophisticated reports for users to view in their Web browsers. Find details about BIRT from the link to the Eclipse Web site in the Resources section of this article.
You can enable many reporting functions using BIRT’s report designer graphical user interface. In addition, BIRT also gives you the ability to extend these features using JavaScript.
This article describes a set of techniques that you can use to add additional function to a BIRT report beyond the function that is provided by the standard BIRT report designer. For a foundation in BIRT, we recommend that you start with the following two books about BIRT: BIRT A Field Guide to Reporting and Integrating and Extending BIRT, both published by Addison-Wesley.
This article is written for solution developers who want to provide advanced features to the users of their BIRT reports. We assume that the reader is:
- Familiar with most aspects of the BIRT report designer and is able to create a report design from scratch by:
- Creating a data source and a dataset
- Adding report elements like grid and table to the design
- Customizing the report with styles, fonts, colors, and so on
- Adding text and annotation to the report
- Specifying parameters for a report
- Familiar with the JavaScript language and how to insert JavaScript code into the BIRT report design
- Able to deploy a BIRT report to WebSphere Application Server.
Each section below describes a different advanced feature that might be required in a BIRT report. The section describes the feature and then describes steps to implement the feature. Each section is independent of the others so may be referred to in isolation.
Single user-sortable columns
Description
When viewing a report, the user often wants to sort the data in the table based on the values in one column where the sorting is different from the default sort order provided in the report design. For example, a report of product information may, by default, be sorted by product name, but the user may want to view the data sorted by delivery date.
This section describes two techniques you can use to customize a BIRT report so that the user can specify the sort order for the report data.
Implementation – Option 1
In this implementation, the sorting is pushed down to the database by modifying the SQL query used by the dataset. The user specifies which column is to be sorted by clicking on the header of the column to be sorted.
- Add the following code to the initialize handler for the overall report definition. To do this, open the layout editor, click in the margin of the report (so that no report element is selected), click on the Script tab, In the drop down list select initialize. After adding the code, replace the dataSetName in the first line with the name of the dataset you want to sort on.
dataSetName = "EPCLifecycleDataSet";
sortCol = reportContext.getHttpServletRequest().getParameter("sortCol");
sortDir = reportContext.getHttpServletRequest().getParameter("sortDir");
currentURL = reportContext.getHttpServletRequest().getRequestURL() + "?" +
reportContext.getHttpServletRequest().getQueryString();
sortClause = "";
targetURL = "";
abbrSortCol = "";
if (sortDir != null)
{
if (sortDir.indexOf("ASC") != -1)
sortDir = "DESC";
else
sortDir = "ASC";
}
else
sortDir = "ASC";
if (sortCol != null && sortCol.length != 0)
{
sortClause = " ORDER BY " + sortCol + " " + sortDir;
lastIndex = sortCol.lastIndexOf(".");
if (lastIndex == -1)
abbrSortCol = sortCol;
else
abbrSortCol = sortCol.substring(lastIndex + 1);
}
reportContext.getReportRunnable().getDesignInstance().
getDataSet(dataSetName).queryText += sortClause;
if (currentURL.indexOf("__sorting=") != -1 )
targetURL = currentURL.substring(0, currentURL.indexOf("__sorting") - 1);
else
targetURL = currentURL;
targetURL = targetURL + "&__sorting=true&sortDir=" + sortDir + "&sortCol=";
|
- Select the column header of a column you want to allow the user to sort, as shown in Figure 1.
Figure 1. Selected column header
- In the Property Editor view:
- Click the Properties tab.
- Click Hyperlink from the table,
- Click the... button next to the Link To: entry field.
- In the Hyperlink Optionsdialog, select Hyperlink Type to be URI.
- Click the ... button next to the Location: field.
- In the Expression Builder, enter the following as the expression for the hyperlink:
targetURL + "VIEWSCHEMA.EPC_LIFECYCLE.EPC";
Change the column name in the expression to be the name of the column from the relational database you want to sort on for the particular column you selected.
- Do steps 2 and 3 for each column you want to allow the user to sort on.
- If you want an image to be displayed to indicate which column is sorted and the direction of sorting, add the following code to the onCreate handler of each of the column headers to be sorted. To do this, in the layout editor, click the column header text that you want the user to be able to sort on, so the header text is highlighted as shown above. Click on the Script tab and then select onCreate from the drop down list and add this code:
var sortedColName = abbrSortCol;
var sortedDirName = sortDir;
if (sortedColName != null)
{
if (sortedColName == "EPC")
{
if (sortedDirName == "DESC")
this.getStyle().backgroundImage = "images/up.gif";
else
this.getStyle().backgroundImage = "images/down.gif";
this.getStyle().backgroundRepeat = "no-repeat";
this.getStyle().backgroundPositionX = "98%";
this.getStyle().backgroundPositionY = "50%";
}
else
this.getStyle().backgroundImage = null;
}
|
- Add images to the appropriate images directory that display what you want to be used to indicate the sort direction.
Implementation – Option 2
This implementation allows the user to specify the sort order and direction (ascending or descending) as parameters for the report.
- Create a report parameter sortColumn with data type String anc click the ... button next to the Linked to Report Parameter field.
- Add the column names from the report as static values. Set a column as the default value as shown in Figure 2. Here values are the names of the columns on which sorting can be done.
Figure 2. Sort column
- Create a report parameter called sortOrder, as shown in Figure 3, with static values of ascending and descending. Set one of these to be the default you prefer.
Figure 3. Sort order
- Add the following JavaScript code in the beforeOpen event handler of the dataset that is used to provide the data for the report.
queryString = this.queryText;
queryString = queryString +
" order by " +
reportContext.getParameterValue("sortColumn") +
" " +
reportContext.getParameterValue("sortOrder");
this.queryText = queryString ;
|
Controlling pagination
Description
While viewing a report, a user may want to select the number of rows to be displayed per page. You should be able to select the number of rows to be displayed from a drop down list.
This section describes how you can customize the report using BIRT report parameter and grouping functionality to enable dynamic pagination by the user. During report design time, you will set some values, such as 20, 30, and 40. When the report is run in the BIRT Report Viewer, the user will be prompted with a report parameter list box with the set of values specified during design time.
Note: This feature can only be tested in the Report Viewer deployed in WebSphere Application Server.
Implementation
This feature is enabled in a report by grouping data rows by the desired number of rows, and then inserting a page break after every group. To group the rows in a specific number, every row is indexed.
- Select the table in the report that contains the data, and in the Property Editor, choose Binding to create a new column binding. Specify Total.runningCount() as the expression. This aggregate function will provide the current row number as BIRT processes each data row. Figure 4 shows the column binding row_index that uses Total.runningCount().
Figure 4. Running count specification
- Select Group in the Property Editor for the table and add a group on the column binding row_index as shown in Figure 5. The value of the field Range will be set dynamically from user input through JavaScript. Name the group Rows_group and make sure that the Repeat Header option is checked and Page Break After is set to the value Always.
Figure 5. Group details
- In the layout editor, delete the data element that BIRT automatically adds to the group header. If you do not, the report displays the row number at the beginning of every group.
- Create a report parameter RowsTobeDisplayed and add static values that are the number of rows you would like the user to specify for each page of their report, as shown in Figure 6.
Figure 6. Report paramater
- In the outline editor, select the group Rows_group, as shown in Figure 7, created in step 2, and click the Script tab. Select the onPrepare event handler from the drop down and enter the following JavaScript code:
NoOfRows = reportContext.getParameterValue("RowsToBeDisplayed");
NoOfRows = parseInt(NoOfRows) + parseInt(1);
NoOfRows = NoOfRows * 1;
this.intervalRange = NoOfRows;
|
Figure 7. Group selection
 |
Expanding and collapsing detail data
Description
The user may wants to switch back and forth between a detailed report and a summary of that report. For example, imagine a report with three columns: PRODUCTLINE (Category), PRODUCTNAME and BUYPRICE. The report is grouped on PRODUCTLINE and has a total BUYPRICE for every category. The user wants to initially view the report with details which show all the product names and prices but then wants to switch to view a summary report showing just categories and total prices.
Implementation
We will enable this function by using the hyperlink properties of report element that will link to another report which is the summary of the detailed report.
- Create a new report for the detailed report and create a data set with query like this:
select CLASSICMODELS.PRODUCTS.PRODUCTLINE,
CLASSICMODELS.PRODUCTS.PRODUCTNAME,
CLASSICMODELS.PRODUCTS.BUYPRICE
from CLASSICMODELS.PRODUCTS
|
- Bind the columns of the data set with a table in the palette view of the Report Designer.
- In the layout view, select the table created in the earlier step and click the Groups tab.
- Create a new group as shown in Figure 8.
Figure 8. Group details
- Select the report in the layout view and in the initialize event handler add the following two lines:
totalPrice = 0 // For storing the total buy price of each category
allCategoryTotal = 0 // For storing the total buy price of all
// categories |
- Select the detailed row of the table as shown in Figure 9 and in the onCreate event handler add the following code:
total = this.getRowData().getExpressionValue("row[BUYPRICE]");
total = total * 1;
totalPrice = total + totalPrice;
allCategoryTotal = allCategoryTotal + total;
|
Figure 9. Detail row selection
- Add a Label in the Group Footer Row as shown in Figure 10.
Figure 10. Add label
- Add the following lines in the onCreate event handler of the label:
this.text = "Total BUY Price-----" + totalPrice
totalPrice = 0 // initializing to zero every group
|
- Create a label in the report layout below the table and add the following code. It shows the total of all categories.
this.text = "ALL CATEGORY TOTAL=" + allCategoryTotal |
- Create a new report to show a summary of the detailed report using the following query for the dataset:
select CLASSICMODELS.PRODUCTS.PRODUCTLINE,
SUM(CLASSICMODELS.PRODUCTS.BUYPRICE) as TOTAL
from CLASSICMODELS.PRODUCTS
group by CLASSICMODELS.PRODUCTS.PRODUCTLINE
|
Follow the steps described for the detailed report earlier to display the Total of all categories.
- Add a label called Summary to the first detailed report and hyperlink to the summary report as shown in Figures 11 and 12.
Figure 11. Hyperlink to summary report
Figure 12. Hyperlink details
- Similarly add a label in the summary report Go back to details and hyperlink to the detailed report.
|
Data with blank selection criteria
Description
Parameters can be specified for BIRT reports that require the user to provide a value for the parameter. These parameters are often used to filter the report data rows in some way. In some situations you may want to specify a parameter for filtering the rows of the report but also allow the user to indicate that they would like to see all rows and not use the filter parameter value. One way to provide this is to allow the user to leave the parameter value blank; the report then interprets a blank parameter to mean no filtering is required on that parameter.
For example, imagine a report of three columns: PRODUCTLINE, PRODUCTNAME and BUYPRICE. The report also has a parameter which lets the user enter a specific PRODUCTLINE value on which the report is to be filtered. So when the user enters a value for the parameter, only the rows where the PRODUCTLINE value equals the parameter value are displayed.
Now, if the user chooses to leave the PRODUCTLINE parameter value blank, then all rows will be displayed regardless of their PRODUCTLINE values.
Implementation
This can be enabled in a report by using a report parameter and some JavaScript in the beforeOpen event handler of the dataset used by the report.
- Create a data set called "DataSet" which will be used to display data on the report and define the SQL query of the data set as follows:
select CLASSICMODELS.PRODUCTS.PRODUCTLINE,
CLASSICMODELS.PRODUCTS.PRODUCTNAME,
CLASSICMODELS.PRODUCTS.BUYPRICE
from CLASSICMODELS.PRODUCTS
|
This will be executed when the selection criteria on PRODUCTLINE left blank.
- Create another data set called "ProductLine" and define the SQL query as follows:
select CLASSICMODELS.PRODUCTS.PRODUCTLINE
from CLASSICMODELS.PRODUCTS
|
This will be used to provide selection values for the parameter.
- Create a report parameter "Product Line" and bind it to the column PRODUCTLINE of the dataset ProductLine. While creating the report parameter, make sure the Allow blank values option is checked as shown in Figure 13.
Figure 13. Edit parameter
- Add the following JavaScript code to the beforeOpen event handler of the data set DataSet which will be displayed in the report. This set of scripts alters the queryText whenever a criterion is selected.
productLine = reportContext.getParameterValue("Product Line");
if (productLine != '')
{
queryString = this.queryText;
queryString = queryString +
" where CLASSICMODELS.PRODUCTS.PRODUCTLINE = " +
"'" +
productLine +
"'";
this.queryText = queryString;
}
|
|
Multiple user-sortable columns
Description
When viewing a report, the user may want to sort the data on multiple criteria. Sometimes the user may want to sort the data only on one of the available selection criteria and leave the others blank or select no criteria to sort the data by leaving all the criteria blank.
For example, in a report of three columns, CUSTOMERNAME, COUNTRY and CITY, the user may want to sort the data first on COUNTRY and then sort on CITY within COUNTRY, or the user may want to sort the data only on CITY, or provide no sorting at all by leaving all sort criteria blank.
Implementation
This section describes how the solution developer can customize the BIRT report and enable this functionality using BIRT report parameters and a JavaScript event handler.
- Create a data set called DataSet, which will be used to display data in the report, with an SQL query like this:
select CLASSICMODELS.CUSTOMERS.COUNTRY,
CLASSICMODELS.CUSTOMERS.CUSTOMERNAME,
CLASSICMODELS.CUSTOMERS.CITY
from CLASSICMODELS.CUSTOMERS
|
- Create a report parameter called First Sort Criteria as shown in Figure 14.
Figure 14. First sort parameter
- Create another report parameter called Second Sort Criteria as shown in Figure 15.
Figure 15. Second sort parameter
- Select the data set DataSet in the outline view as shown in Figure 16 and click on the Script tab.
Figure 16. Select data set in outline
- Select the event handler beforeOpen in the script tab and add the following code:
sortC1 = reportContext.getParameterValue("First Sort Criteria")
sortC2 = reportContext.getParameterValue("Second Sort Criteria")
if (sortC1 != '')
{
queryString = this.queryText;
queryString = queryString + " order by " +
reportContext.getParameterValue("First Sort Criteria");
this.queryText = queryString;
}
if (sortC2 != '')
{
if (sortC1 != '')
{
this.queryText = this.queryText +
" , " +
reportContext.getParameterValue("Second Sort Criteria");
}
else
{
this.queryText = this.queryText +
" order by " +
reportContext.getParameterValue("Second Sort Criteria");
}
}
|
.
|
Hide or display columns
Description
When a user is viewing a report they may wish to customize the report by removing columns and subsequently adding the columns back to the report.
Implementation
You can enable this feature in BIRT by using a report parameter and the visibility property of the columns. Here we will show a report of FIRSTNAME, LASTNAME, and EMAIL. The user can hide or unhide any of these columns by checking the appropriate check box in the report parameter window.
- Create the Data Source and a Data Set with a query like this:
select CLASSICMODELS.EMPLOYEES.FIRSTNAME,
CLASSICMODELS.EMPLOYEES.LASTNAME,
CLASSICMODELS.EMPLOYEES.EMAIL
from CLASSICMODELS.EMPLOYEES |
- Create a report parameter called hideFirstName as shown in Figure 17.
Figure 17. hideFirstName parameter
- Create two more report parameters called hideLastName and hideEmail for the other two columns Last Name and Email as described in step 2.
- In the layout view, select each column and in the Property Editor, select the visibility property and write the following expressions:
params [“hideFirstName"]
params [“hideLastName"]
params [“hideEmail"]
|
as appropriate, as shown in Figures 18 and 19.
Figure 18. Column visibility expression
Figure 19. Expression entry
Conclusion
The six techniques outlined above should enable solution developers to extend BIRT reports with additional interactive capabilities for the report users. Hopefully, they will also spur more ideas in your mind about how JavaScript, coupled with BIRT, can be used to further enhance and customize reports.
Resources Learn
Get products and technologies
Discuss
About the authors | 
| | Craig works on the WebSphere RFID Information Center development team in IBM Silicon Valley Lab, San Jose, CA. |
| 
| | Abdul works on the WebSphere RFID Information Center development team in the IBM’s India Software Development Lab. |
| 
| | Yan works on WebSphere RFID Information Center development team in IBM Silicon Valley Lab, San Jose, CA. |
Rate this page
| |
