Showing posts with label MDX error referenced in an inner subexpression. Show all posts
Showing posts with label MDX error referenced in an inner subexpression. Show all posts

Friday, March 18, 2011

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

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]

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.

Here is the modified MDX...

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