Report Studio : 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')
else
([Sales (query)].[Order method].[Order method] = 'Fax')
end
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')
or
(?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.
Solution :
Create a filter in the query as follows:
Case when ?UserSelection? = 1
then ([Sales (query)].[Retailer].[Retailer name] = 'ActiForme')
else
([Sales (query)].[Order method].[Order method] = 'Fax')
end
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')
or
(?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.
No comments:
Post a Comment