how to merge the results of multiple prompts into 1 filter

[pikachu]

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!

[mrcool]

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....

[pikachu]

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..

[mrcool]

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?

[pikachu]

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?

[JGirl]

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