MDX Cell Calculations - How to parameterize its expression in SSRS

Question

I have a cell calculation in a query which I use for SSRS query and contains parameter like;

MEMBER Measures.[SatisTotalYTD] AS null
CELL CALCULATION ScopeEmulator
FOR '(order(STRTOSET(@Ay1),[DimDateView].[YearMonthNumber].currentMember.name,BDESC).item(0),Measures.[SatisTotalYTD])'
AS [Measures].[KapananAyaKadarTotalYTD]

But when I execute the query it gives me error as

Parameter could not be resolved because it was referenced in an inner subexpression

So I tried to define parameter value as a set;

with
SET [Aylar1] as STRTOSET(@Ay1)
MEMBER Measures.[SatisTotalYTD] AS null
CELL CALCULATION ScopeEmulator
FOR '(order([Aylar1],[DimDateView].[YearMonthNumber].currentMember.name,BDESC).item(0),Measures.[SatisTotalYTD])'
AS [Measures].[KapananAyaKadarTotalYTD]

Then it gives me

The definition of the Aylar1 set contains a circular reference.

So how can I use parameter in a cell calculation expression?

"{ [DimDateView].[YearMonthNumber].&[205],[DimDateView].[YearMonthNumber].&[206] }" — bsaglamtimur
what are you trying to achieve? what is the end result you're aiming for - I'm struggling to understand this question. — whytheq

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-04-21T14:12:55

You misunderstand the Scope conception. You can't do any calculations, all you can is change the tuple behaviours there. I'm not sure what's your motivation of using ScopeEmulator at all. The fallowing query must do magic considering your code above:

With

Set LastTuple as
Tail(StrToSet(@Ay1))

Member [Measures].[SatisTotalYTD] as
IIF(
    [DimDateView].[YearMonthNumber].CurrentMember is LastTuple,
    [Measures].[KapananAyaKadarTotalYTD],
    Null
)

MDX Cell Calculations - How to parameterize its expression in SSRS

1 Answers