Tutorial 12 - Building the ProductSalesStats Report Procedure - part III

The ProductSalesStats2 report, created in the Tutorial 11, takes Product ID as an input parameter, and produces a bar chart with a single series of values (bars). The user may find it inconvenient to have to remember the numerical ID number of every product that needs to be bar-charted, and it may also be desirable to show multiple products (series) in a single bar chart for comparison.

Pop-up list as the Parameter

The first limitation can be overcome by creating a Value List with names of the products as they are spelled out in the Products table, and then using this Value List as an input parameter in the Report Procedure. The following steps show how this could be done:

1. Create a new Value List, using Edit > Edit Value Lists menu item:

2. Change the Start component to the Parameter of Text data type, and in the Inspector set it to the newly created Value List:

3. Change the Retrieval Search criteria to match the ProductName field with the parameter:

Now, at the run-time, the input parameter will be presented to the user as a pop-up list with Product names to select from:

Stop and Show

The approach described in the previous section leaves the issue of multi-product bar chart unresolved. We can solve this problem with the help of the Stop-and-Show feature of the database Retrieval component.

By default, when the Retrieval component executes, it loads all rows that match the Search criteria from the database table into its internal stack, and then dispenses them one by one, with every pass through the retrieval loop. The process of loading rows into the stack happens at the first pass through the Retrieval component, in one go.

However, the Retrieval component can be instructed to stop at the point of loading the table rows and ask the user to manually select the rows that will be put into the stack. In other words, the user can do a manual fine-tuning of the list of rows returned according to the Selection criteria, and further narrow the list down. With this in mind, we open the ProductSalesStats2 report Workspace, and drag in the Retrieval component:

The "Stop and show" checkbox should be checked, to enable the manual selection of the returned rows. In addition to this, the returned ProductName fields should be sorted alphabetically, in ascending order (not shown in the figure).

Our main database Retrieval component should now have the Search criteria based on the returned ProductName, as in the figure below:

and the Retrieval should be done in a loop, once for each ProductName. The complete Report Procedure is shown in the figure below:

(Click on the image to enlarge)

Save the Report Procedure as Query/ProductSalesStats3.

At the run-time, the report displays the row selection panel, asking the user to select one or more rows to process; in the context of the report the selection panel shows the list of all Product names found in the Products table:

The user can select multiple rows by holding the Ctrl key down, clicking on the desired entries and then pressing the Select button ("Select all" will have an effect of clicking on all returned rows, thus reducing the Stop-and-Show to an ordinary non-stop Retrieval). The following is an example of the Bar Chart with multiple Product series: