urgent ......cascading prompts

[vijayleads]

Hello,

I am trying to set up cascading prompts but I cannot figure out why this doesn't work. I looked up the Great Outdoors prompt example to see what they did and I couldn't find anything different there. I did the exact same thing they did but I am not able to make this work.

Here is what happens...I have set up 3 reports, R1,R2 and R3. R1 has a prompt on a field in R2. R2 has a prompt on a field on R3.

When I run R1, i get the prompt I set on R2 which is fine. I make a selection and then I get the prompt I set on R1. After I select a value the result I get includes even rows that I did not want from the propmt in R2. So what happens is the propmt on R2 seems to get cancelled or neutralized.

Please can anyone tell why this is happening?

 

[ADB1]

Not sure if this is what you are after, but we had a very similiar problem.

I have come to the conclusion cascading prompts only work if the data you are querying is mutually exclusive. For example, the fact that I have two different carriers that deliver to Australia will always mean that in my final user report I will always have all of these carriers, not the distinct carrier I specified in my first prompt.
The reason the Great Outdoors data works is the fact that the products are unique to the different product lines. I added the 'tents' product line to the Environmental Line product Type, and when the final report is retrieved, despite the fact that I have already specified I only want to see Outdoor Products (in my first prompt), Environmental Line also appears as the final SQL passed to the database doesn't carry through the first prompts. It only sends the SQL relevant to the final report, therefore ignoring any prior report prompts.

Hope this makes sense, Cognos couldn't really answer my question.

Adam.

 

[griffindm]

I'm not really sure about the description of the problem, but I have used cascading prompts successfully for many years. In a typical three report scenario, with cascading report picklist prompts, what you refer to as R1 would be the final 'real' report for which you are trying to get an exclusive filter value into. R2 would be referenced by R1 and would itself call R3 as a report picklist. R3 may contain prompt filters or just have a hardcoded filter.

A real world example might be a report on project cost status. R1 would show the actual current period status. R2 might be a report to pass a single project manager name to R1, and R3 might be a report to select a department, in which project managers are assigned. Thus the end user running the report gets first a prompt for department from a list of valid departments, then a list of valid project mangers from the selected department, and finally a series of project cost reports from projects assigned to that manager.

Hopefully this makes sense. I hope this helps.

Dave Griffin

 

[vijayleads]

Hey Dave,

I tried doing what you said but I can't get it to work. Wonder if you read Adam's response about mutual exclusivity. Let me know what you think about this.

Vijay

 

[Pommie]

I think what the problem is that the prompt only returns the last data entered so when you have cascading prompts only the last prompt is returned which is why the mutually exclusive data works.
What I do is to save the results of the previous prompt concatenated with the current prompt result as a field on each succesive prompt (hope that makes sense). Then use this concatenated key when finally filtering the data.
eg. 1st prompt asks for 'Country', 2nd prompt asks for 'Region' and stores 'Country+Region', 3rd prompt asks for 'City' and stores 'Country+Region+City' and the report is built after filtering the data on the key 'Country+Region+City'. This method can also be used to filter the selection values on each prompt. The only problem I've found is some performance issues with building concatenated keys.
Check out the Cognos KnowledgeBase articles 78278/79687 for details.

John

 

[dm21]

John,
What would you do if you prompts has multiple selection ability.

i.e. for country you would choose (Canada, USA)
and for city you choose (Toronto, Newyork)
How would you separate them in your last report.

 

[griffindm]

dm21,

It's not really a problem if you are presenting a concatenation of multiple dimension categories in each picklist report. For example, if you selected both UK and Canada from an initial picklist report, you might get 2nd picklist reports on cities of: "London - UK" and "London - CAN". The user can pick the specific instance of London that they want and the final report uses both the country and city in determining the filter.

Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com