Scenario : Olap based SSRS reports with one or more cascaded parameters.
One of the parameters is Top Count letting the user specify whether to display Top 5 or Top 15 or ...Top N of best selling products or most profitable companies, or the like.
The list of values is predefined according to the business requirements.
Hereby the main steps and the pseudocode for implementing the solution using the Analysis Services data provider:
1) Create one dimension, TopN. It may have one attribute TopN.
Source it from a view or a named query with a trivial t-sql like :
select <first value> as TopN
union
select <second value> as TopN
......
union
select <last value> as TopN
Set the property IsAggregatable = False for the attribute TopN, so the attribute cannot be aggregated in any hierarchy.
The dimension is not related to any measure groups.
Process the dimension, browse it's only attribute and verify that there is only one level, and no (All) level at all.
2) Create a calculated member [Top N Value] as :
CREATE MEMBER CURRENTCUBE.[Measures].[Top N Value]
AS [Top N].[Top N].currentmember.member_caption,
VISIBLE = 1 , DISPLAY_FOLDER = '<your folder>';
If your reporting solution further needs sets for filtering based on the newly created [Top N Value] you may add them as :
CREATE DYNAMIC SET CURRENTCUBE.[Your set]
AS TOPCOUNT([Dimension].[Hierarchy].[Attribute].MEMBERS,[Measures].[Top N Value],[Measures].[Your measure]), DISPLAY_FOLDER = '<folder for diplaying sets>';
Deploy and process the cube.
In your SSRS report:
3) create a parameter, let's call it TopNTopN.
Your parameter is normally one of latest in the chain of cascading parameters.
That is why the Source MDX for the available values may include some subcubes :
WITH MEMBER [Measures].[ParameterCaption] AS [Top N].[Top N].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS
[Top N].[Top N].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Top N].[Top N].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Top N].[Top N].ALLMEMBERS ON ROWS
FROM ( SELECT ( STRTOSET(@<param1>, CONSTRAINED) ) ON COLUMNS
...........
FROM ( SELECT ( STRTOSET(@<paramn>, CONSTRAINED) ) ON COLUMNS
FROM [Your cube]) ...)
Select one value among <first value> , <second value> , ... <last value> as the default parameter value. You must be now ready for testing your report.
For an alternative solution using the OLE DB Provider for Analysis Services, please see the pages 583-584 from the book 'Applied Microsoft Reporting Services' by Teo Lachev.
