Hi Guys,
I'm using an object from the Framework which is contained within a query subject that has an always prompt applied to it.
I'm trying to create two queries in report studio that use the same objects but require different reponses to the always prompt, is this possible or do I need to create a new query subject without the always prompt to achieve this?
Many Thanks
Rich
Quote from: tr5ad on 06 Feb 2015 04:25:57 AM
Hi Guys,
I'm using an object from the Framework which is contained within a query subject that has an always prompt applied to it.
I'm trying to create two queries in report studio that use the same objects but require different reponses to the always prompt, is this possible or do I need to create a new query subject without the always prompt to achieve this?
Many Thanks
Rich
Hi,
The "Always prompt" is driven under the covers by an expression that uses a parameter - ie a name encapsulated in question marks - eg ?muppet?. It sounds like this is used in an embedded filter in your query subject.
If you think about it in those terms, you can see that a single parameter cannot have two different values at the same time. It can contain a response that spans multiple values (if the filter is coded in a way that permits this - ie using an 'in' operator rather than =) but if you want two queries driven by different prompt responses, this isn't how it works.
Is there a reason you need two queries rather than one query with both values in the parameter? Can you explain the requirement?
Regards,
MF.
Thanks I thought that was the case, just took a look at the generated sql and can see the problem.
I'm working on a very poorly designed DWH, it contains four rows for every fact record to store different variants (this is why the always filter is applied to select the appropriate record). I need to retrieve two different records but they're the same objects with a different where clause, I was creating two queries then joining on the key fields to return both results on the same line. I guess I will have to make a new Query Subject without the filter to get around this problem.
Thanks for your help.
I guess they haven't heard of denormalising.
I would suggest making a new query subject without the filter.
Assuming the different types are called A,B,C,D.
Add 4 new measures like this;
If type = A then value else 0
If type = B then value else 0
If type = C then value else 0
If type = D then value else 0
That way you'll be able to use whichever measures you need and the grouping and summing will take care of the rest.