Use of Cascading Prompt- in filter definition

[Coglearner]

Hi,

I have a cascading prompt based on the following columns
1.Brand
2.Flavor.

Examples of the values for these columns..

Brand Flavor
------- --------
Coca Cola Diet
Coca Cola Normal
Pepsi Normal
Pepsi Diet


In my report I have filter for flavor,so when I run the report, first it is asking Brand , if I select the brand then it will ask the flavor ..if I select the flavor
it should display only for the brand and flavor which I selected, but it is giving the all the brands which is having the same flavor since I am using only flavor in my filter defn.

Can I capture the brand which I am selecting in the Cascading prompt also in my filter.


In simple, I want to use the Brand also in my filter condition.The value for this Brand will be the same as the cascading prompt Brand.


Please let me know if any work arounds for this..


Thanks
Coglearner

 

[griffindm]

Coglearner,

The problem stems from the 2nd cascading prompt picklist only returning the flavor, rather than the brand and flavor. The final report only gets the flavor, and dutifully reports all of the brands that have that flavor.

To avoid this, have the 2nd cascading report picklist contain both the flavor, and a concatenation of brand and flavor. Show the flavor to the user, but use the concatenation as the value returned to the final report, and construct a similar concatenation there to measure against this returned value.

This avoids the problem and gets the result you want, without the end user ever seeing the new concatenated column.

Regards,

Dave Griffin


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

http://www.decisionsupportgroup.com

 

[Coglearner]

Thanks Dave,

I have tried the concatenation method which you told, but it does not return the results.But if get the query from Profile-->sql and run the same on the data base , it is fetching the correct records.Please let me know how to solve this

Thanks
Coglearner

 

[griffindm]

Coglearner,

Not sure why you are not getting your results. The final report should have a column, call it brand-flavor, which is constructed as :
brand + flavor ...
or alternatively as
brand + ' - '+flavor ...
(which is easier to read).

The second picklist report should use a similarly constructed column, while showing only the flavor. This should ensure that the final report is filtered on both the brand and the filter.

If you are not getting results back, check in the SQL for the final report and check that this concatenation and string are in the filter and what the text string is that is being used for the comparison.

Let us know what you find.

Regards,

Dave Griffin




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

http://www.decisionsupportgroup.com

 

[Coglearner]

I have created a new report and its working fine now..
I dont know why it didnt work in my previous report which also had the same columns..


Regards
Coglearner