IBM i > ADMINISTRATOR > DB2

Linking Reports With DB2 Web Query for i


In a prior article we discussed ways DB2 Web Query for i can be used to improve reporting when contrasted with Query/400 or RPG programming. This article focuses on development of linked reports. Utilizing summary and detail reports with links between them allows for navigation between related reports or filtering of information by business segment or category. In this example, we allow users to zoom in on Product segments based on Country.

(Note: Your reports, charts or graphs would probably contain more columns of data than those used in the example reports depicted here. These are simple, keeping the focus on linking and drill- down, not the specific data.)

The portal and InfoAssist designer are all that's needed to do a simple drill-down (e.g., from ReportA to ReportB to ChartC). Report combinations are limited only by your creativity. You might drill-down by multiple fields by adding report hyperlinks from several fields in one report to other related reports or charts. This is part of what makes Web Query such a flexible reporting solution.

For these examples we’ll utilize data from Web Query’s sample database, which exists in the QWQCENT library. If you’ve installed Web Query but haven't installed QWQCENT, you should be able to find a save file in the QWEBQRY library, which loads during the Web Query licensed program installation. A restore library command typically restores the library of sample data from the SAVF to QWQCENT:

RSTLIB SAVLIB(QWQCENT) DEV(*SAVF) SAVF(QWEBQRY/QWQCENT) MBROPT(*ALL) ALWOBJDIF(*ALL)

IBM has detailed information about this restore process. Just do a browser search on QWQCENT.

Web Query sample data includes several tables linked together by referential integrity constraints. Referential constraints are a database technique to enforce relationships between database tables. The relevant *FILE objects in QWQCENT are Inventory, Orders, Plant and Stores.

When viewed in iNavigator (figure 1), we can see the tables and constraints that link them. When linked together (or joined), they form a simple example of a data warehouse, enabling us to write various reports by plant, by store or by product, to list a few possibilities.

It's important to emphasize two things: First, DB2 Web Query must understand the tables available. This is accomplished via the creation of Metadata (what WQ calls a Synonym), defining both location and content of the database tables. WQ doesn’t duplicate the data, it points to it. Second, this one-time activity per table or set of linked tables is accomplished within the Web Query portal, utilizing the Metadata > Create option.

For tables linked by referential integrity constraints, WQ can generate a single synonym for the connected tables. The Orders table was selected to create a synonym including the four related tables. A prefix, CEN_, was entered and the resulting metadata is named CEN_ORDERS. Web Query creates the metadata files in the IFS.

(Note: While further discussion of metadata is beyond the scope of this article, you can see an example of the join syntax by reviewing the CEN_ORDERS metadata.)

Once created, you’re ready to use the InfoAssist Designer to create reports. Sites using Web Query will eventually have many synonyms representing data sources for your DB2 objects. All will be available when you launch InfoAssist.

If the tables you’re working with aren’t linked by referential constraints, you have these options:

  • create a DB2 view (where SQL logic joins the tables) and create metadata of the view object;
  • generate metadata for each table (joining them in InfoAssist at report-creation time), or; edit the WQ metadata joining tables manually. This assumes deeper knowledge of the syntax required to write metadata.

In the IBM sample data, because the tables are linked by constraints, WQ creates metadata for the four tables with join linkages. InfoAssist is launched from the WQ portal by selecting New > Report from a folder in the portal. Once InfoAssist prompts for the data source, you create a report and save it to the folder. By selecting the CEN_ORDERS data source discussed above, the tool accesses the tables plus all the data fields mentioned within the metadata.

The InfoAssist designer window looks like this (figure 2).

In InfoAssist, adding fields to a report is easy. You’ll see the fields in the Data pane. Drag these fields to the Preview pane, or other panes, to make things happen. You can choose whether a field will be listed or summarized, sorted or pivoted across. The Across capability makes fast work of period-to-period comparison reports. Either double-click a field in the data pane or select one or more fields and then perform a drag/drop to the Preview pane.

In this case, we’re creating a detail report with Country, Product type and Revenue $ total. First locate the Country, Product type and Revenue fields in the Data list, then drag them (either one-by-one or as a group by holding CTRL to multi-select) to the Preview Panel area. This immediately generates a report layout with a few records from the underlying DB2 information. It’s easy to add a report header by clicking Home > Header and typing some appropriate title. To add a total for the Revenue column, click Home > Column Totals for a total line (figure 3).

This report will be the target of my drill-down, so it needs a parameter for Country. Right-click the Country field in the data pane and select Filter, and you'll see an option pane for prompt type or values. A dynamic prompt will cause the report to display possible country values from the database in a drop-down list. Selecting Static would prompt for whatever values you’ve entered.

The field name COUNTRY (case matters!) will be important for reference by the other report (the CALLING report). This process is similar to one IBM i program calling another. At any time you can select Run to test the target report by itself. Assuming the parameter for COUNTRY is specified correctly, you should see a prompt at run-time (figure 4) and a result showing only the data for the Country selected.

Use the Save As option to the target report, giving it a meaningful name (e.g., CountryDetail_Report2). Once this report is complete, we’ll create a second report, a summary report, that will drill-down to this detail report.

For the summary report, start a new report using the same data source (Note: It’s possible to link reports based on different data sources if your application requires it). This report could be named, for example, CountrySummary_Report1. The summary report needs Country, Revenue, Cost of Goods Sold fields, and we could again add a title using Home > Header. Simple so far, right?

To calculate the Profit, create a new field in the report by clicking the menu Data > Define and entering the equation for Profit on the prompt. (figure 5):

  PROFIT = REVENUE – COSTOFGOODS

I made the format of the Profit field match the format of the fields it uses, because it reduces the chance of truncation. Adhoc field definition may be as close to programming as the report writer gets.

(Note: Alternatively, a site DBA could have placed the formula for Profit in the metadata, calculating the value automatically. The ability for WQ to provide fields for reporting, that don’t exist in the database, is a powerful feature.)

Metadata improves consistency, accelerates report writing and reduces chances for differing interpretations of the data. After all, data warehouses are supposed to provide a “single version of the truth” to the enterprise!

Now we have Report1 looking like this (figure 6):

To complete this example, we will add some features:

  • A link from Country to the underlying detail report,
  • The Profit field we calculated, expressed as a number with no decimal places, and
  • The Profit field expressed as a percentage.

To add the link that hooks two reports, click the Country field on the preview pane to expose a Hyperlink icon on the menu bar. Selecting Hyperlink defines a relationship between Country on summary report 1 and a URL to our detail report2. To specify the path to report2, select the Browse button to locate the target report by name. Finally, we must define whether the report should open in a new window along with any parameters that link report1 and report2 (figure 7).

Field COUNTRY is the link, so give that name as a single parameter. Once the hyperlink is complete, you’ll notice the country value becomes a link in the preview pane.

To add Profit, drag the field to the preview pane. I reduced decimal places from two to zero digits, because cents are not necessary for the values. I dragged Profit to the panel again, and by right-clicking it, I used the aggregate function Percent to express Profit as a % rather than dollar value.

At this point, I can run my reports to check the results. I run Report1 and select a couple of countries (e.g., Spain and Germany). I expect to see Report2 open, showing more detail for the country in a separate window, for each country selected. That’s exactly what happens.

I’ve added another drill-down in Report2 from the country-specific detail to a pie-chart depicting the information in a chart. When “Show Chart” is selected, the Report3 chart appears in another browser window (figure 8).

If you're wondering whether an icon can be substituted for words in a drill-down link, the answer is yes -- provided you have a field to define an image link via an HTML tag pointing to a local directory. Selecting the resulting icon should also activate a linked report or chart (figure 9). I’ll leave you to consider how else these features can be utilized in your reporting.

That’s Drill-down in IBM DB2 Web Query for i. Next time we'll discuss Active Reports.

 

Rick Flagler is an information technology consultant, teacher and mentor.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



Advertisement

Advertisement

2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters