CODEWITHSUNDEEP
reporting-servicesssasmdxssrs-2008-r2
smckechnie
smckechnie

Reputation: 79

MDX Parameters in SSRS

Good Day all, hope that someone can assist I have the following member that I am creating, this is how the code looks which works hard coded.

WITH MEMBER [Measures].[CoinInRank] AS CASE 
WHEN [Measures].[Total Amount Bet] <= 5100  AND [Measures].[Total Amount Bet] > 0 THEN "Casual"
WHEN [Measures].[Total Amount Bet] > 5100 AND [Measures].[Total Amount Bet] <=  25000 THEN "Frequent"
WHEN [Measures].[Total Amount Bet] > 25000 THEN "Avid"
END MEMBER

Now I would like to pass the amount ranges i.e 5100 and 25000 as parameters from SSRS, I have tried the following with absolutely no luck.

WITH MEMBER [Measures].[CoinInRank] AS CASE 
WHEN [Measures].[Total Amount Bet] <= STRTOSET(@FrequentMinValue,CONSTRAINED)   AND [Measures].[Total Amount Bet] > 0 THEN "Casual"
WHEN [Measures].[Total Amount Bet] > STRTOSET(@FrequentMinValue,CONSTRAINED) AND [Measures].[Total Amount Bet] <=  STRTOSET(@FrequentMaxValue,CONSTRAINED) THEN "Frequent"
WHEN [Measures].[Total Amount Bet] >  STRTOSET(@FrequentMaxValue,CONSTRAINED)  THEN "Avid"
END MEMBER

Any advice would be greatly appreciated.

Upvotes: 0

Views: 111

Answers (2)

SouravA
SouravA

Reputation: 5253

You may need to re-check your syntax here. The MEMBER at the end doesn't look like it belongs here.

The syntax for CASE is

WITH MEMBER [Measures].x AS 
CASE 
   WHEN 1>2 THEN 'false'
   WHEN 2>1 THEN 'true'
   ELSE 'none'
END

Since you pull the value from report, the value is going to be in string format. I am not sure if MDX engine allows a numeric value to be compared against a string.So to be on the safer side, you might need to use the StrToValue function to convert that text value to numeric.

Your final query should look like - 

WITH MEMBER [Measures].[CoinInRank] AS CASE 
WHEN [Measures].[Total Amount Bet] <= STRTOVALUE(@FrequentMinValue,CONSTRAINED)   
AND [Measures].[Total Amount Bet] > 0 
   THEN "Casual"
WHEN [Measures].[Total Amount Bet] > STRTOVALUE(@FrequentMinValue,CONSTRAINED) 
AND [Measures].[Total Amount Bet] <=  STRTOVALUE(@FrequentMaxValue,CONSTRAINED)
   THEN "Frequent"
WHEN [Measures].[Total Amount Bet] >  STRTOVALUE(@FrequentMaxValue,CONSTRAINED)  
   THEN "Avid"
END

Hope it helps you.

Upvotes: 0

whytheq
whytheq

Reputation: 35605

StrToSet converts a string into a set. Measures is a numeric expression so comparing to a set will not work. If you get rid of the StrToSet what happens?

WITH MEMBER [Measures].[CoinInRank] AS CASE 
WHEN [Measures].[Total Amount Bet] <= @FrequentMinValue   AND [Measures].[Total Amount Bet] > 0 THEN "Casual"
WHEN [Measures].[Total Amount Bet] > @FrequentMinValue AND [Measures].[Total Amount Bet] <=  @FrequentMaxValue THEN "Frequent"
WHEN [Measures].[Total Amount Bet] >  @FrequentMaxValue THEN "Avid"
END MEMBER

Upvotes: 1

Related Questions