Using Stored Procedures in DB2 Web Query for i: Part 2
The first installment in this two-part series demonstrates how a stored procedure with multiple result sets can be created―and how data is consumed―in a dashboard using DB2 Web Query for i. (Read part 1 here.)
To review, a dashboard is a document containing several pieces of related information, typically from reports and charts. In part one, the content consists of hypothetical sales-related information for Stores, Plants and Sales Reps, depicting top performers from each category. It's retrieved from DB2 by a stored procedure with multiple result sets, both for convenience and speed.
The SQL used in the stored procedure limits the number of rows of summarized data that are returned, sorting the data to report the top 10 items in each category by descending revenue amount. Four groups of data are returned in their respective SQL result sets, and Web Query shows them in the data pane as ANWERSET1, 2, 3 and 4. These ANSWERSETs are seen in (figure 1). Part one omits the fourth result (figure 2) produced by the stored procedure, which was generated by the C4 cursor, because it differs from the first three. Let’s examine those differences.
Doing More with More Complex SQL
The C4 DECLARE statement utilizes a slightly more complex SQL statement to accomplish several things. It:
establishes time buckets by extracting year and month from the order date,
calculates revenue and cost amounts,
summarizes profit as revenue-cost, by year and month; and,
using sorting and fetching in SQL, the statement returns the 12 most recent periods.
What this yields is a result set with two fields from which we can present a chart to show profit by period, or over time. You can see that ANSWERSET4 contains the Period and the summarized Profit dollars for the period. As time passes and additional records are added to the database, they will be detected when the SQL runs. This sort of rolling-month presentation is useful to show the most recent activity without the need to pass parameters to filter the date selection. It also frees the report creator from maintaining logic for checking date range information.
As a side note, although I used SQL to extract the year and month from a database date field, Web Query offers both metadata and built-in functions as additional ways to decompose dates, times or timestamps into constituent parts when these fields are needed in a report.
The Web Query InfoAssist designer features many possible chart types. To chart profit over time, one approach is to utilize a line chart. In the line chart, plotting is accomplished by placing profit dollars on the Y-axis and time values on the X-axis, as shown in (figure 3). This is a simple presentation that uses a single data group of X-axis values. Web Query could chart more than one group if, for example, we need to show a comparison between several data groups over the same time span, with multiple lines. We can also create a multiple Y-axis chart to present two different data sets with differing scales (left and right), where each axis has a different range (e.g., plotting numeric amounts to contrast quantity good versus quantity scrap manufactured over a period of time).
A completed dashboard document consisting of seven different reports is seen in (figure 4). (It's also included at the end of part one.) That document was created with HTML chosen as type of output.
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.