COGNOiSe.com - The IBM Cognos Community

Legacy Business Intelligence => COGNOS ReportNet => Report Studio => Topic started by: pikachu on 25 Jun 2007 04:28:56 AM

Title: how to merge the results of multiple prompts into 1 filter
Post by: pikachu on 25 Jun 2007 04:28:56 AM
Is it possible to merge the values of multiple prompts into a single filter?

The prompts are from different tables but will result in common column which is going to filter my fact table.

Can this be done? is it via tabular set? I cannot figure this out. someone please help. I am on ReportNet MR3.

Thanks!
Title: Re: how to merge the results of multiple prompts into 1 filter
Post by: mrcool on 25 Jun 2007 06:22:29 AM
If all the promps are varchar....u can concatenate all the promp values in filter.
For Example ur fact table column which has concatenated  value is XYZ
Create a filter like XYZ=?Prompt1?||?Prompt2?

Hope This works....
Title: Re: how to merge the results of multiple prompts into 1 filter
Post by: pikachu on 25 Jun 2007 07:38:40 AM
hi mrcool,

Thanks for your reply. My prompts are all numbers (IDs). If so, how do I concatentate the list together?

I've tried XYZ in (?Prompt1? , ?Prompt2?, ?Prompt3? ) but it doesn't work.. the prompts are optional but when I put that code in the filter, the prompts become mandatory..

Title: Re: how to merge the results of multiple prompts into 1 filter
Post by: mrcool on 25 Jun 2007 11:23:12 PM
How does the column in ur fact table look....Is that concatenation of all the prompt values.

Suppose prompt1=123
               prompt2=234
How does the column look in ur fact table XYZ=123234
My assumption is correct?
Title: Re: how to merge the results of multiple prompts into 1 filter
Post by: pikachu on 26 Jun 2007 11:23:08 AM
No that's not the right assumption. The scenario is :

Prompt1  will list
123
456
789

Prompt2 will list
321
654
987

User may select 123 from Prompt1 and 987 from Prompt2.

I want to filter (include) 123, 987 in my fact table.

ie. it will 2 rows for 123 and 987.

Can this be done?
Title: Re: how to merge the results of multiple prompts into 1 filter
Post by: JGirl on 27 Jun 2007 05:22:30 AM
Do you need to have 2 separate prompts?  You could union the 2 prompt datasets producing a single query, then the only filter you would need would be XYZ in (?Prompt1?)

J