Tuesday, April 20, 2010

Implementing Optional Tables / Filters Inclusion based on Prompt

Requirement : Prompt the user to select either Order Method or Retailer Name and based on selection filter the data for Order Method = Fax or Retailer Name = 'ActiForme'.

Solution :

Create a filter in the query as follows:

Case when ?UserSelection? = 1
then ([Sales (query)].[Retailer].[Retailer name] = 'ActiForme')
([Sales (query)].[Order method].[Order method] = 'Fax')

where 1 - Retailer Name and 2 is Order Method.

Another approach you would mention for the above requirement is to use a filter as below:

(?UserSelection? = 1 and [Sales (query)].[Retailer].[Retailer name] = 'ActiForme')
(?UserSelection? = 2 and [Sales (query)].[Order method].[Order method] = 'Fax')

The disadvantage with this approach is that your query will include both the Retailer and Order Method Tables. Now assume your requirement was to optionally display data either from Detail Fact Table or Summary Fact Table that includes a filter on Detail / Summary fact table based on selection.

For this requirement, if you had created a filter like the second one mentioned your query would have included both the detail and summary tables thus resulting in a stitched query that would be hitting 2 fact tables. This would lead to performance problems. Hence the 1st approach is beneficial in such cases.


Scott L Holmes said...
This comment has been removed by the author.
Scott L Holmes said...

Should be:
(?UserSelection? = 1 and [Sales (query)].[Retailer].[Retailer name] = 'ActiForme')
(?UserSelection? = _2_ and [Sales (query)].[Order method].[Order method] = 'Fax')

Zephyr said...

Oops..Sorry about that...Thanks for correcting me.

Sehgal's said...

Thanks for posting so much useful information on your blogs.

I want to clarify 1 thing about this post.
The 1st approach given here is normally not advisable as the WHERE clause shouldn't have if-then-else OR Case statements. They should be present only in the Select clause.

Correct me if I am wrong!!

Sumit Sehgal

Zephyr said...

Thanks Sehgal for your kind words.

You are right in saying that the 1st approach is not advisable.

But in this case we had to go with this approach as the second one wasnt too suitable with the disadvantage described and the only other option was to use a render variable and have 2 different queries satisfy the requirement which I didn't want to go ahead with as there were a lot of objects in the reports and a lot of other render variables..So didn't want to add on to that and make maintenance a headache...