SSRS mdx report: calculated member with maximum rank value for passes multivalue parameter

Question

Here is a simple mdx query to MS OLAP cube, which outputs sale step stats for 3 cities with ranking of each sale stage, it works fine:

WITH 
MEMBER [Measures].[rank] AS 

    case [Sales_step].currentmember.member_caption         
    when 'Contacts' then 1    
    when 'Clients' then 2
    when 'Funded' then 3 
    else 0 end

SELECT {[Measures].[rank],
        [Measures].[qnt]} ON COLUMNS,

NON EMPTY     
       crossjoin({[City].CHILDREN},                            
                 {[Sales_step].CHILDREN}) ON ROWS 

FROM ( SELECT ( STRTOSET(@[Sales_step], CONSTRAINED) ) ON COLUMNS  
FROM [SALES_PIPE])

The output is:

Now I want to build totals for each city without separate sale steps, but showing maximum archived sales stage rank only. The result must be:

I tried the following code to do that:

WITH 
MEMBER [Measures].[rank max] AS 

    case [Sales_step].currentmember.member_caption         
    when 'Contacts' then 1    
    when 'Clients' then 2
    when 'Funded' then 3 
    else 0 end

SELECT {[Measures].[rank max],
        [Measures].[qnt]} ON COLUMNS,

NON EMPTY [City].CHILDREN ON ROWS 

FROM ( SELECT ( STRTOSET(@[Sales_step], CONSTRAINED) ) ON COLUMNS  
FROM [SALES_PIPE])

It does not generate error, but returns null values for calculated member [Measures].[rank max]:

It works only when I pass one value to @[Sales_step] parameter. While I need a multivalued param run. When I changed this snippet in case clause:

case [Sales_step].currentmember.member_caption

to:

case strtomember(@[Sales_step]).member_caption

it throwed an error "The STRTOMEMBER function expects a member expression for the 1 argument. A tuple set expression was used". Errors fire both for one- and multy-param when I use this too:

case strtoset(@[Sales_step]).currentmember.member_caption

How do I need to modify calculated member [Measures].[rank max] to get desired result with maximum rank for passed @[Sales_step] multivalue param?

Try: FROM ( SELECT ( STRTOSET(@Sales_step, CONSTRAINED)) ON COLUMNS I am unsure about your syntax @[Sales_step]. — alejandro zuleta
this is now empty [Sales_step].currentmember ....there is no currentmember for this hierarchy in your second query, as you have taken [Sales_step].CHILDREN out of the script — whytheq

whytheq whytheq · Accepted Answer · 2016-03-30T21:11:51

I wonder if something like this works:

WITH 
SET [S] AS
    NonEmpty(
       EXISTING [Sales_step].CHILDREN,
       ([City].CURRENTMEMBER, [Measures].[qnt])   //<<I think that [City].CURRENTMEMBER is probably redundant in this tuple
    )
MEMBER [Mx] AS   
    CASE         
      WHEN INTERSECT([S], {[Sales_step].[Funded]}).COUNT = 1 THEN 3 
      WHEN INTERSECT([S], {[Sales_step].[Clients]}).COUNT = 1 THEN 2 
      WHEN INTERSECT([S], {[Sales_step].[Contacts]}).COUNT = 1 THEN 1
    END 
...
...

SSRS mdx report: calculated member with maximum rank value for passes multivalue parameter

1 Answers