One day while working on a SSRS report I got an error message “Parameter could not be resolved because it was referenced in an inner subexpression” when I was using parameterized MDX.
Here is the sample MDX where I got this error message
with member [Measures].[Jan SalesAmt] as '([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Jan " + @Year2Digit + "]"))'
member [Measures].[Feb SalesAmt] as '([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Feb " + @Year2Digit + "]"))'
select {
[Measures].[Jan SalesAmt],
[Measures].[Feb SalesAmt]
} on Columns,
non empty
[Customer].[Customer].[All Customers] on Rows
From [Adventure Works]
Here is the modified MDX...Here is the sample MDX where I got this error message
with member [Measures].[Jan SalesAmt] as '([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Jan " + @Year2Digit + "]"))'
member [Measures].[Feb SalesAmt] as '([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Feb " + @Year2Digit + "]"))'
select {
[Measures].[Jan SalesAmt],
[Measures].[Feb SalesAmt]
} on Columns,
non empty
[Customer].[Customer].[All Customers] on Rows
From [Adventure Works]
This MDX was used in one of the data set where I already declared the parameter “Year2Digit” in my dataset. After spending some time I was able to fix this problem and here is the solution – Remove single quotes from the member expression and this would fix the problem.
with member [Measures].[Jan SalesAmt] as ([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Jan " + @Year2Digit + "]"))
member [Measures].[Feb SalesAmt] as ([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Feb " + @Year2Digit + "]"))
select {
[Measures].[Jan SalesAmt],
[Measures].[Feb SalesAmt]
} on Columns,
non empty
[Customer].[Customer].[All Customers] on Rows
From [Adventure Works]
I hope this helps.
Thanks,Gaurav
Thank you very much, using this article i have resolved my problem with mdx parameters in SSRS reports
ReplyDelete