IBM Support

How to filter a report with the maximum value for a query if no value is selected in a prompt

Troubleshooting


Problem

[Description: How to create a prompt that by Selecting 'Yes' in a value prompt will show the max year in a report. Selecting 'No' will show all years. Solution Description: Cannot use "if"..."else" within the filter. you must use 'AND', 'OR'. Steps:

  1. Use Go Sales and Retailers package.
  2. Create list with Order Year and Revenue.
  3. Create value prompt named 'filter'. Create static values 'Yes' and 'No'. Set Auto Submit to 'Yes'. Default seleciton to 'No'.
  4. Create a filter: ?filter?='Yes' and [Order Year]=maximum([Order Year] for report) or ?filter?='No'
Keywords: maximum filter logic if else Description: In a report that has a prompt for month, if no value is selected in the prompt the report should be filtered using the Maximum month value. If a value is selected in the prompt, then the report will be filtered accordingly. Solution: Use a query item in the query associated with the report to create an If-Then-Else statement Steps:
  1. Create a prompt for the report that will be used to select a desired month to filter on. In the sample report attached the prompt is called Current_month.
  2. Add a default value to the prompt. The value entered is a value that should not exist in the database; for example for month use 999.
  3. In the query associated with the report add a query item (in the sample report Max_month) with the following expression: if (?Current_month? <> 999) then (?Current_month?) else (maximum([Sales (query)].[Time dimension].[Current month] for report))
  4. Add a filter to the report with the expression: [Sales (query)].[Time dimension].[Current month] = [Max_month]
  5. When the report runs for the first time the default value is 999, which is not a valid value in the database. The query calculation use in the filter will then use the maximum month instead of the prompt value. When a user selects a valid value in the prompt, the report will be filter against that value
[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":"Report Studio","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2;10.2.1;10.2.1.1;10.2.2","Edition":"","Line of Business":{"code":"LOB76","label":"Data Platform"}},{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":"Report Studio","Platform":[{"code":"PF033","label":"Windows"}],"Version":"8.3","Edition":"","Line of Business":{"code":"LOB76","label":"Data Platform"}},{"Product":{"code":"SS6G84","label":"IBM Cognos Analytics on Cloud"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB76","label":"Data Platform"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Historical Number

1042507

Document Information

More support for:
Cognos Business Intelligence

Software version:
10.2, 10.2.1, 10.2.1.1, 10.2.2

Operating system(s):
Windows

Document number:
624611

Modified date:
08 May 2025

UID

swg21382381

Manage My Notification Subscriptions