CODEWITHSUNDEEP
reporting-servicesparametersmdxolapmember
Globe
Globe

Reputation: 292

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

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:

enter image description here

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:

enter image description here

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]:

enter image description here

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?

Upvotes: 2

Views: 474

Answers (1)

whytheq
whytheq

Reputation: 35557

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 
...
...

Upvotes: 2

Related Questions