Using multi value parameters in measure group at SSRS

Question

I have a SSRS-report (2008 R2) which uses SSAS-cube as it's data source. At the report I have a data set with a MDX-query.

At the report I have defined parameters where some of them allow multiple value and at the MDX-query they're usually restricting the data set as follows:

    WHERE (
         STRTOMEMBER(@PARAM),
         STRTOSET(@MULTI_VALUE_PARAM)
    )

At one of the data sets i need to use the value parameter at calculated member like this:

    MEMBER [Measures].[MY_MEASURE] AS (
        [Measures].[MEASURE_FROM_MY_CUBE],
        STRTOSET(@MULTI_VALUE_PARAM)
    )

This doesn't work and I don't know why?

If I change the parameter, that it doesn't allow multiple values, and use it like this:

    MEMBER [Measures].[MY_MEASURE] AS (
        [Measures].[MEASURE_FROM_MY_CUBE],
        STRTOMEMBER(@MULTI_VALUE_PARAM)
    )

it works

So it seems I can't restrict the measure with multi value parameter. How come?

FrankPl FrankPl · Accepted Answer · 2013-08-23T20:01:37

Try

MEMBER [Measures].[MY_MEASURE] AS Aggregate(
    STRTOSET(@MULTI_VALUE_PARAM),
    [Measures].[MEASURE_FROM_MY_CUBE],
)

The syntax ( [Measures].[MEASURE_FROM_MY_CUBE], <something> ) that you use defines a tuple in MDX, and that may only contain a single member from each hierarchy. And a set is not a member. The Aggregate MDX function takes a set as first parameter, and an expression as the second, and then aggregates the set according to the cube definition - which in most cases is summing, but might be different for some measures.

Using multi value parameters in measure group at SSRS

1 Answers