Purpose of Document
This document describes the steps to create a value prompt on a measure data item in an IBM Cognos BI Report Studio report using a relational package.
The following technique was tested using IBM Cognos 10.2.1.1 but should apply to earlier versions as well.
Exclusions and Exceptions
Creating measure prompt values from larger fact tables might present performance issues. The technique suggested in this document is to satisfy those specific requirements where a user wants to have measure prompts created and it is beneficial and suited to the data set.
Target readers should have experience authoring reports with IBM Cognos BI Report Studio and are familiar with the user interface.
In certain scenarios, a user may want to select a measure value that exists in the data and use that value to filter the report. For example, you may want to see certain products that had returns equal to a specific return amount found in the fact data itself. This would prevent users inputting an amount that does not exist in the data and thereby returning an empty report. The prompt control we will create here will give you an idea of the types of return numbers that actually have occurred and you can further look at the data that matches the criteria selected from the prompt.
Prompts created on measure data items need additional property settings to display the data as expected when compared to prompts created with attributes. If measure prompts are created with the default settings, then the values in the prompt drop-down are aggregated and hence not useful as a report filter for the scenario we are interested in.
This document provides the steps to design a value prompt using a measure with the necessary property settings to meet our requirement as well as using a sub-query technique to reduce the number of queries sent to the database.
Create the Report
In this example the Returned items (query) namespace under the Sales and marketing (query) folder of the GO Data Warehouse (query) package is used.
- Open IBM Cognos BI Report Studio and create a new List report using the GO Data Warehouse (query) sample package.
- In the Query Explorer, select Query1.
- Expand the Sales and Marketing (query) folder, and then from the Returned items (query) namespace, drag Month (caption), Product, Return quantity from the Time, Products, and Returned items fact query subjects respectively to the Data Items pane.
Figure 1 – Query1 with query items added to the Data Items pane
- Rename the query to qryBase.
Tip: Click in the white space between the Data Items pane and the Explorer fly out pane to bring focus to the query’s property pane.
- From the Query Explorer, click Queries, and then from the Toolbox tab, drag a Query object to the Query pane.
- Name the new query qryList.
Figure 2 – Query Pane showing qryBase and qryList
- Drag qryBase to the right side of qryList to create a query reference (also known as a feeder query).
Figure 3 – Query Explorer showing qryBase query acting as a feeder query for the qryList query
This technique works on both Dynamic Query Mode (DQM) and Compatible Query Mode (CQM) and is useful in reducing the number of queries sent to the database. In this case, the prompt will be based off the qryBase query and the list off the qryList query. The qryBase query will be sent to the database and the results will be cached locally on the BI server to be used by the qryList query.
- Select qryList, and then in the Properties pane, set the Use Local Cache property to Yes. This will ensure that the local cache is used regardless of what the default setting is in the underlying Framework Manager model.
- Double-click qryList and drag all query items from qryBase to the Data Items pane.
Figure 4 – The qryList query showing data items added from the qryBase query
- From the Data Items pane, drag Return quantity to the Detail Filters pane.
- In the Expression Definition pane of the Detail Filter Expression dialog, set the expression to
[Return quantity] = ?Select Quantity?
Figure 5 – Detail Filter Expression dialog showing the new filter expression in the Expression Definition pane
- Click OK, select the new filter, and then in the Properties pane, change the Usage property to Optional. Note that because the underlying query that feeds the qryList query is already summarized, it does not require setting the Application property to After Auto Aggregation for this example.
- From the Page Explorer, select Page1, select the List object, and then in the Properties pane, change the Query property from qryBase to qryList.
- From the Data Items tab, add all query items from qryList to the list report.
Figure 6 – List report with all items from qryList added to it
Create the Measure Prompt
You will now create a value prompt using a measure from qryBase as the use value.
- From Toolbox tab, drag a Value Prompt object to the left of the List object on the report page.
- In the Prompt Wizard dialog, choose Use existing parameter, select the Select Quantity parameter from the drop-down, and then click Finish.
- Click on the Value Prompt created in the previous step and in the Properties pane, set the Query property to qryBase.
- Set the Sorting property to sort on Return Quantity in ascending order.
- Set the Use Value property to Return quantity, the Required property to No, and the Auto Submit property to Yes.
- Run the report.
- Click on the value prompt drop-down list and notice the result. The prompt only displays one overall aggregated value.
Figure 7 – Report output with value prompt showing only one aggregated value
This is not the behavior desired for this report. The requirement is to display individual return values aggregated to each month in the data. To make the prompt show unique values instead of an aggregated value, you need to set the Properties property of the value prompt.
- Close the report, and then in Report Studio, in the Properties pane for the Value Prompt object, click the ellipsis for the Properties property.
- Select the check boxes for Month (caption) and Product.
Figure 8 – Properties window showing check boxes of Month (caption) and Product data items selected
- Click OK.
Test the Report
You will now test the final result to ensure the report is behaving as expected.
- Run the report again.
- Click on the value prompt drop-down list. Notice that it now displays values that represent the groupings found in the report - in this case aggregated values for month and product.
Figure 9 – Value Prompt now displaying values for the month and product grouping
- Select 24 from the drop down list. The report is now filtered on only months and products that had a return quantity of 24 items.
Figure 10 – Report now displaying only months and products where returned items equal 24