Cognos Report with Multiple Optional Prompts

Question

I have a report requirement where the user wishes to have 2 multi-select optional prompts.

• Scenario A. If User selects value(s) in both prompts, the list should be filtered on values selected in both prompts.
• Scenario B. If User selects value(s) in Prompt 1, and none in Prompt 2 - the list should be filtered on values selected in Prompt 1 only
• Scenario C. If User selects value(s) in Prompt 2, and none in Prompt 1 - the list should be filtered on values selected in Prompt 2 only

As both prompts are optional, the filter is kept optional.

Report works fine in scenario A when both prompt values are selected.

But in scenario B, report shows selected values from Prompt 1 (this is correct), but all the values from Prompt 2 (this is incorrect. Values that are not selected should not be shown).

In scenario C, report shows selected values from Prompt 2 (this is correct), but all the values from Prompt 1 (this is incorrect. Values that are not selected should not be shown).

Can you please provide a solution so that report will be filtered as per the scenarios above?

Answer 1

By default when no answer provided in the prompt Cognos does not apply any filter. The result is all the values. So, the result you got is the expected behaviour in Cognos.

My suggestion is to work with multiple lists and conditional String variable. So, first define a conditional variable for with the following expression:

case 
when (ParamDisplayValue('Prompt 1') is not null and ParamDisplayValue('Prompt 2') is not null) then ('A')
when (ParamDisplayValue('Prompt 1') is not null and ParamDisplayValue('Prompt 2') is null) then ('B')
when (ParamDisplayValue('Prompt 1') is null and ParamDisplayValue('Prompt 2') is not null) then ('C')
end

Of course, you have to define three 3 string results: A,B,C.

Lists: Create 3 Lists:

List A - Define filter that use prompt 1, prompt2 values as parameters

List B - Define filter that use prompt 1 as a parameter and define constant filter that will exclude all the values of prompt 2

List C - Define filter that use prompt 2 as a parameter and define constant filter that will exclude all the values of prompt 1.

Select each list, and then use the render variable on each matched list:

ListA - Render only if conditional variable string equals A.

ListB - Render only if conditional variable string equals B.

ListC - Render only if conditional variable string equals C.

Hope that helps you resolve the issue.