Enhance your DB2 Web Query reports by using drill-down capabilities.
Hopefully, by now you've had a chance to install and use the new DB2 Web Query for System i tool. Hailed as the successor to the popular Query/400 product as the strategic System i query and reporting tool, DB2 Web Query can do things that Query/400 could only dream of. One of these advanced features is drill-down: the ability to select a column in a high-level (summary) report and click through (drill down) to show the next level of detail in another report.
Two types of drill-down capabilities can be implemented with DB2 Web Query:
- Programmatic drill down—This requires developing at least two reports: a high-level summary report (also called the parent) and a detail report to link to (the child). The parent report contains the information necessary to link to the child report. The child report could in turn drill down to another linked report to reveal the next level of detail. Each new level of drill-down requires the creation or existence of another linked report
- OLAP—The drill-down feature is enabled by using predefined levels of an OLAP hierarchy and activating the OLAP feature in the report options. With this method, the report developer is only required to create one report that is OLAP-enabled. All linked reports are based on the OLAP hierarchy structure and are generated automatically by DB2 Web Query. This method is not available with the DB2 Web Query base product. It requires the purchase of the OLAP add-on option (IBM product 5733QU2 option 2).
In this TechTip, I'm going to focus on the programmatic method. I'll explain how you can change your existing reports to take advantage of drill-down, and I'll include steps on passing parameters so that your reports can filter the data using other columns in the report.
To demonstrate DB2 Web Query's drill-down capability, let's start with a simple example of a high-level summary report. This report, called Revenue and Profit Summary, is shown in Figure 1.
Figure 1: Here's our example summary report. (Click images to enlarge.)
As the name would suggest, this report shows revenue and gross profit totals from the orders table, aggregated and sorted by country. Notice that the report also has two input parameters to filter the data by a specified date range.
This is a useful report, but it doesn't reveal much detail. For that, we have another report titled Revenue and Profit Detail Report (Figure 2).
Figure 2: This report detail shows more detail.
This report shows similar information but at the next level of detail (by region and state). It also includes additional fields for gross profit margin, provides conditional styling to highlight high and low gross profit margins, and is aggregated horizontally by the ORDERDATE_YEAR column. Notice that this report also includes input parameters for filtering the data by date range and a parameter to filter by country.
While these two reports may satisfy the reporting requirements, the problem is the lack of flow and efficiency from the user's perspective. After selecting the summary report and specifying the date range parameters, the user may view the results of the report and decide that the next level of detail is needed. This requires selecting the other report from the DB2 Web Query navigation menu and then re-specifying the date range parameters as well as selecting one of the countries from the drop-down list.
Wouldn't it be nice if the two reports could be linked so that all this could be accomplished with a single click of the mouse? Well, they can be! By making a few changes to the reports, you can quickly and easily enable drill-down to make your reports more efficient and useful. Let's begin by examining the design of the detail report.
1. Open this report in the Report Assistant tool and select the tab titled Selection Criteria.
What we are looking for here are the names and the order of the input parameters. They are circled in Figure 3.
Figure 3: Find the names and the order of the input parameters.
2. Record the names of these parameters because they will be specified later when modifying the summary report. Be aware that parameter order, spelling, and case-sensitivity are relevant.
Now that we know the names and order of the detail report's selection criteria parameters, we're ready to make changes to the summary report.
3. Exit this report and open the summary report in Report Assistant.
4. From the Field Selection tab, highlight the column named COUNTRY in the Sort By panel and click on the twisty button titled Show – Field Options – COUNTRY (Figure 4).
Figure 4: In the summary report, show field options.
You will see the expanded Field Options panel for the column COUNTRY.
5. Click on the Drill Down button as shown in Figure 5.
Figure 5: Click on the Drill Down button.
6. From the drill-down dialog window, select the radio button Execute Procedure (FOCEXEC). This action will cause a list of the reports in the current domain to be loaded into the dialog window.
7. Expand the domain and folder tree until you see the name of the detail report (Revenue and Profit Detail Report). Select this report (Figure 6).
Figure 6: Select report to link to.
Once selected, the name of the procedure will be loaded into the Execute Procedure field.
Next, we need to add the parameters to pass to the linked detail report. For this, we'll use the parameters that we recorded previously. Make sure the parameter order, spelling, and case match exactly what is specified in the detail report.
The first parameter is for the COUNTRY column. The value passed to the linked report will be the one selected by the user during the act of drilling down.
8. From the drill-down dialog window, click the Add button and specify the following:
· Enter COUNTRY for the parameter name.
· Select the Field radio button under Parameter Value.
· Select COUNTRY from the field drop-down list.
· Click OK.
The next two parameters will be the beginning and ending order dates. Because these values are prompted for and specified in the summary report, they can be passed to the linked report, eliminating the need to prompt for them again. Passing selection criteria parameters from one report to another is somewhat esoteric. The trick is to specify the parameter names as constant values and precede them with an ampersand. This is shown in Figure 7.
9. Back on the drill-down dialog window, click the Add button and specify the following for the beginOrderDate parameter:
· Enter beginOrderDate for the parameter name.
· Select the Constant radio button under Parameter Value.
· For the constant value, specify the &beginOrderDate.
· Click OK.
Figure 7: Pass input parms from summary to detail report.
10. Repeat step 9 for endOrderDate.
When you're finished, the drill-down dialog should look like the example shown in Figure 8.
That's it! The reports have been linked and drill-down enabled, and parameters have been passed. The new reports are ready to be put to the test.
After running the modified Revenue and Profit Summary Report, it should look like the example shown in Figure 9.
Figure 9: The summary report is now drill-down enabled.
It should look essentially the same as it did before with one exception: When you hover over the Country columns, notice that they are "hot" and can be clicked. Doing so drills down to the next level as shown in Figure 10.
Figure 10: Here's the result of the drill-down.
If you're prompted for any parameters after you click to drill down, this is likely caused by a parameter mismatch. Go back and verify that the parameters recorded in Step 2 have the exact same order, spelling, and case of the parameters entered in steps 8 through 10.
Summary
The ability to create drill-down enabled reports in DB2 Web Query is just one of the many tools available in this feature-rich product. For more information on the product, download the IBM Redbook titled Getting Started with DB2 Web Query for System i.
LATEST COMMENTS
MC Press Online