Db2 Tools for z/OS

 View Only

QMF Visualization Part 3 Ad hoc reports

By shawn sullivan posted Mon December 20, 2021 01:31 PM


Ad Hoc reports are similar to Drill-down paths but thy show all the charts in parallel instead of in series, like this:
When any wedge in the pie chart is clicked, it sends the name of the region to the three other images and acts as a filter (like a JOIN condition in SQL) to  limit the content to that Region. For example, here is what happens when you click on SOUTH:
Note how the sizes of the bars on the two other charts have changed and the Grid now only shows SOUTH. Also notice that the wedge for SOUTH in the pie chart is highlighted. If that wedge is clicked again it undoes the filtering.
Likewise the other two charts will broadcast the value clicked on and further filter the data

Here I have clicked on SOUTH from the PIE, Utah from the Horizontal Bar chart and City from the column chart. The Grid shows only SOUTH>Utah>City

Here is how the Ad Hoc Report is generated.

1) Write and test a query and save it to a folder in the repository
Next use the menu File>New>other to start an Ad Hoc report

It is the first element under QMF Objects

Give it a name

A blank Ad hoc Report canvas is presented, with a toolbar across the top Click on the “tree” button

 This will give you a list of folders and queries in the repository
The Query will always comeinto the Ad Hoc report as a Grid. This button will expose the list of visualizations for the data. 

Here is the menu for charts. We will start with a Pie chart of the Region vs sum(Amt)

 Now the Grid is a chart. You can resize it and it will auto-scale.

 For the next chart you could add a different query, but in keeping parallel to the drill-down path example I will use the same query again

IYou get a list of charts

This time the choice may be a horizontal bar chart

The selection are STATE by sum(AMT)

 This chart may also be resized.
This time it will be DIVISION by sum(AMT)

The next step will be to put in the broadcasting of the item selection like SOUTH, or Utah. Or City

 This is done with the green chain link icon:

Single click on it and drag a connecting line to the other images and single click there.
A create Link dialog comes up and you specify the source column from the first chart and then make sure it is matched in the second chart with a column that is appropriate. Click the Add button.

Repeat this single click and drag to the other chart and the grid and specify REGION for each.

Finally link the Division Chart to the Grid making sure you match DIVISION to DIVISION in the JOIN

Now collapse the main toolbar with the Left pointing arrow in the left corner and try your report.

If it works well then you can save it and share it. Of course, it can be made into a Weblink or it can be based on a Dynamart for faster performance.