MDX – Getting Jiggy with Top 10 Somethings in a SSRS Parameter

You want your SSRS parameter serve up only the Top 10 somethings you have a Analysis  Services Multidimensional cube.  The following sample MDX in your Parameter’s dataset should get you started along the right track.  Simply substitute the dimension attribute you need, your actual measure and the various parameters you might like to feed into the dataset.  Remember to enable multi select on the report parameter and set a default to the dataset’s parameter value if you want the default to be all Top 10 members.

WITH
MEMBER [Measures].[ParameterValue] AS [Dimension].[Attribute].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterCaption] AS [Dimension].[Attribute].CURRENTMEMBER.MEMBER_CAPTION
 MEMBER [Measures].[ParameterLevel] AS [Dimension].[Attribute].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {Measures.[Amount],
Measures.ParameterValue,

Measures.ParameterCaption,
Measures.ParameterLevel} ON 0,
ORDER(
 NONEMPTY(

TOPCOUNT(
 [Dimension].[Attribute].Children,
 10,
 [Measures].[Amount]
 ),[Measures].[Amount]
 )
,[Measures].[Amount],BDESC) ON 1

FROM ( SELECT ( STRTOSET(@Parameter1, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@Parameter2, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@Parameter3, CONSTRAINED) ) ON COLUMNS

FROM [YourCube])))

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s