Standardize Your MDX Parameter Queries

By Dan Meyers

One thing that I have found useful when writing a lot of Reporting Services reports that have parameters and use MDX is to create some standard calculated members in my cube for the Label and Value properties of the parameters. Just like any other calculated measures that get built into the cube script, you get the advantage of reusing them instead of doing them over and over again in the WITH clause of your query.  The code below is dynamic and is not specific to a particular dimension or anything so it will work with whatever you put on ROWS in your data set query for your report parameters.

Below is the MDX for the calculated members and a sample query.

Insert this code into your cube script (at the bottom)

CREATE MEMBER CURRENTCUBE.[Measures].[ParameterValue] AS
    Axis(1).Item(0).Item(0).Dimension.CurrentMember.UNIQUE_NAME,
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[ParameterCaption] AS 
    String(Axis(1).Item(0).Item(0).Dimension.CurrentMember.Level.Ordinal * 1 , ' ' ) + Axis(1).Item(0).Item(0).Dimension.CurrentMember.Member_Caption,
VISIBLE = 1;

Sample Query
SELECT
      {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON 0,
      {[Date].[Calendar].ALLMEMBERS} ON 1
FROM
      [Adventure Works]

.