Dynamic MDX in SSRS

  • Hi,

    I am trying to write a dynamic MDX query to be used for a report. There are currently two reports which share similar datasets except for the name of one dimension, which is different for both of them. I am attempting to reduce them to one report by dynamically passing the name of the dimension through a parameter and make the query reusable. I am unable to get it to work that way. The query is below

    ="SELECT NON EMPTY { [Measures].[Slot Bills], [Measures].[Slot Coin] } ON COLUMNS, {[Slot Bank].[Slot Bank]} ON ROWS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!subreport.Value,",") & "}', CONSTRAINED) ) ON ROWS FROM [DWS])"

    The subreport parameter will pick up the value provided by the user and substitute it in the query. The dataset is an OLE DB type with MSOLAP provider. I am very new to MDX and hence any help, suggestions, corrections would be greatly appreciated.

  • Your FROM clause requires a cube, not a query.

    You should be supplying the parameterized dimension to either a row, a column or as a contraint

    Kind Regards, Will

  • Hi

    The above is using a sub cube which is valid, the designer generated mdx uses them all the time. I think what you want is something like this:


    MEMBER [Measures].[SelectedDimensionLabel]

    AS StrToMember(@SelectedDimension + '.MEMBER_CAPTION')

    SET DynamicDimension

    AS StrToSet(@SelectedDimension + '.MEMBERS')

    SELECT {[Measures].[SelectedDimensionLabel], [Measures].Other Measures]} ON COLUMNS,

    DynamicDimension ON ROWS

    FROM MyCube

    The value of the query parameter @SelectedDimension would be a string like [MyDimension].[MyHierarchy].


  • That works great. Thanks so much Ben and Will!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply