IBM Business Analytics Proven Practices: IBM Cognos BI Report Studio - Measure Based Value Prompt

Product(s): IBM Cognos BI Report Studio; Area of Interest: Reporting

Create a value prompt on a measure data item in an IBM Cognos BI Report Studio report using a relational package and prevent the aggregation of those values in the prompt selection, using the steps described in this article.

Share:

Kalyani Raut, Cognos BI Developer, IBM

Kalyani is a Cognos BI developer in the Predictive Maintenance and Quality team (BA). She has worked on Cognos FM modeling and designed various types of Report Studio reports. She has 8 years of experience.



15 May 2014

Introduction

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.

Applicability

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.

Assumptions

Target readers should have experience authoring reports with IBM Cognos BI Report Studio and are familiar with the user interface.


Overview

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.

  1. Open IBM Cognos BI Report Studio and create a new List report using the GO Data Warehouse (query) sample package.
  2. In the Query Explorer, select Query1.
  3. 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
    Figure 1 – Query1 with query items added to the Data Items pane
  4. 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.
  5. From the Query Explorer, click Queries, and then from the Toolbox tab, drag a Query object to the Query pane.
  6. Name the new query qryList.
    Figure 2 – Query Pane showing qryBase and qryList
    Figure 2 – Query Pane showing qryBase and qryList
  7. 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
    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.
  8. 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.
  9. 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
    Figure 4 – The qryList query showing data items added from the qryBase query
  10. From the Data Items pane, drag Return quantity to the Detail Filters pane.
  11. 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
    Figure 5 – Detail Filter Expression dialog showing the new filter expression in the Expression Definition pane
  12. 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.
  13. From the Page Explorer, select Page1, select the List object, and then in the Properties pane, change the Query property from qryBase to qryList.
  14. 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
    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.

  1. From Toolbox tab, drag a Value Prompt object to the left of the List object on the report page.
  2. In the Prompt Wizard dialog, choose Use existing parameter, select the Select Quantity parameter from the drop-down, and then click Finish.
  3. Click on the Value Prompt created in the previous step and in the Properties pane, set the Query property to qryBase.
  4. Set the Sorting property to sort on Return Quantity in ascending order.
  5. Set the Use Value property to Return quantity, the Required property to No, and the Auto Submit property to Yes.
  6. Run the report.
  7. 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
    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.
  8. Close the report, and then in Report Studio, in the Properties pane for the Value Prompt object, click the ellipsis for the Properties property.
  9. Select the check boxes for Month (caption) and Product.
    Figure 8 – Properties window showing check boxes of Month (caption) and Product data items selected
    Figure 8 – Properties window showing check boxes of Month (caption) and Product data items selected
  10. Click OK.

Test the Report

You will now test the final result to ensure the report is behaving as expected.

  1. Run the report again.
  2. 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
    Figure 9 – Value Prompt now displaying values for the month and product grouping
  3. 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
    Figure 10 – Report now displaying only months and products where returned items equal 24

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics
ArticleID=971205
ArticleTitle=IBM Business Analytics Proven Practices: IBM Cognos BI Report Studio - Measure Based Value Prompt
publish-date=05152014