CODEWITHSUNDEEP
cognos-10
Dingo mk
Dingo mk

Reputation: 11

Cognos Report with Multiple value prompts

I currently have a report that have 9 value prompts items,so far I can only load the prompts with the values that the user can select from, however the desired results should be based on user selection for example if user selected from value prompt1 then only values that are related to value prompt1 should be populated in other value prompts and so on. Your assistance in this regards will be highly appreciated, Thanks in advance.!!!

Upvotes: 1

Views: 2265

Answers (2)

Dingo mk
Dingo mk

Reputation: 11

Thanks for all the valuable inputs, I only needed to enter a measure in my query so that the filtering can only happen where the is volume in those filters

Upvotes: 0

dougp
dougp

Reputation: 3089

If I understand correctly, you want ValuePrompt2 to populate after a value is selected in ValuePrompt1 and be filtered by that selection. You want ValuePrompt3 to populate after a value is selected in ValuePrompt2 and be filtered by that selection and the selection in ValuePrompt1. Etc.

Given object relationships that follow this pattern: QueryN : ValuePromptN : ParamN : QueryItemN

You need to set up Param1 as a Cascade Source for ValuePrompt2. Cognos figures out the filtering for this. You also need to set up Param2 as a Cascade Source for ValuePrompt3. But that won't filter ValuePrompt3 by the selection(s) in ValuePrompt1. To do that, you'll need to make Query3 more specific by adding filters that use Param1 and Param2.

QueryItem1 = ?Param1?
QueryItem2 = ?Param2?

(For clarity, you'll probably want to filter Query2 in a similar manner.)

So when you get to ValuePrompt9, it will be sourced from Query9 and have these filters...

QueryItem1 = ?Param1?
QueryItem2 = ?Param2?
QueryItem3 = ?Param3?
QueryItem4 = ?Param4?
QueryItem5 = ?Param5?
QueryItem6 = ?Param6?
QueryItem7 = ?Param7?
QueryItem8 = ?Param8?

...and its Cascade Source property will be set to Param8.

Consider the design of your queries. A prompt page like you propose may be very slow.

Updated based on new requirement...

If you need to select the values in any order, all of the source queries should have filters to use all of the other parameters and none of the prompts should have the cascade source property set. Then, create a new prompt (PromptNH) for each prompt (PromptN), set its Cascade source property to the related parameter (ParamN), set the Auto-submit property of the source prompt (PromptN) to Yes, and hide the prompt (PromptNH). This forces a reprompt after each selection. Keep in mind that in this scenario the prompt page will quickly filter itself into an unusable state.

Upvotes: 2

Related Questions