This topic seems to be a request that comes in on every project that I am on and since I haven’t had a blog post in awhile, I thought it would be a quick and useful blog . Well, the ability to pass through a value into a WebI doc without having it to correspond to any dimensions/details/measures is a pretty simple process and can be easily done using a predefined query filter in Designer.
We really have two things we need to look at and that is the logic and the syntax.
Top N Pass Through Prompt
Logic
All that we are really doing to get this to work is creating two conditions that will be applied on the end of our query.
Joined by an OR, both conditions will be in the same predefined query filter and one will be always true while the other will always be false.
The first condition, which I always make false, will contain our prompt as well as any dimension that is in the universe so we get our SQL to parse (i.e. dimension does not equal prompt).
The second condition can contain anything that is true (i.e. 2>1, *=*, etc.).
Average Pass Through Prompt
Syntax
The syntax will end up changing a bit depending on what database you are using and really should only apply to the second condition in your filter (I am using SQL Server).
Our false condition will contain the two functions @Select and @Prompt. The @Select will be the dimension that we are using to keep our parse from failing and the @Prompt is going to be the pass through value that we want.
Our true condition will contain any values that will always be a true statement. This can be anything from 1=1 to *=* to 8675309 <> 1.
Here are two examples of the finished product:
@Select(Accounts\User Desc) = @Prompt(‘Type Top Number (* for All):’,'A’,,mono,free,,{’25′}) OR 2>1
@Select(Invoice\Travel Type) = @Prompt(‘Enter Standard Average’,'A’,,mono,free,,{’0′}) OR ‘*’ = ‘*’
Monday, March 8, 2010
Tutorials, Universe Design