I have a prompt page with 10 prompts, all of them driven off the first
prompt which is a multiselect for customer. So what I want to do is
run the query for the first prompt, and have that query return all the
fields I need for all the other prompts, and instead of going to the
database each time to provide the selections for the other prompts, i
want to use the result set of the first query as the base of what I
query for the other prompts, basically creating a temp table that can
be queried. Is that possible? If not, would you have any other idea
of a workaround?
Thanks
I've done things like this before.
One question - are you on a dimensional or relational model? It can make a difference in exactly how you do it...
My suggestion - from many hours of trying otherwise - put the first prompt on its own page, before the other ones on a second prompt page. I've tried Cascading prompts, but it never seems to work. That way, the first query will be resolved correctly before the other 9 prompts are populated.
So, on prompt page 1, Prompt1 goes to Query1. Resolve that one - maybe even with a second query. I sometimes do "X Prompt" and then "X Prompt result" for this kind of thing, just to be on the safe side, where the first one gives me, in your case, Customers, and the second query is the result set of which customers.
Then - are the other 9 prompts from one query? If so, you can just include an "in" filter on that query, and voila, those 9 prompts should then be correct for the Customers suggested. If they are from 9 queries, well, same thing, only 9 times.
If you're doing relational reporting, I've done this before:
Query 2 - a filter on Customer in (Query1.Customers, or Query1Results.Customers) from the prompt.
This may also work via prompt macros - I should really learn how to do those at some point...