Paramters in WITH part of MDX

Hi,

I need a parameter within the WITH part of an MDX Statement in Reporting Services. I tried several types, but all will bring an error. I will do it like:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure) SELECT { [Measures].[Amount] } on columns, ......

The @.MyMeasures should be a combobox with the values like

Name: Sales Volume (kg) Value: [Measures].[Sales Volume KG]
Name: Sales Volume (m2) Value: [Measures].[Sales Volume KG]
... and so on.

What's my failure?

Thanks
Hans

Whats the error?|||

Hi Adam,

The error is:

The query will not be retrieved from the query builder. Check the query for syntax error.
The syntax of

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure)

is not correct, but I didn't find the correct syntax to make a dynamic selectable measure from a combobox.

Thanks
Hans

|||I've seen a lot of these posts where using a parameter in the WITH section throws an exception. The only solution I've found so far is to revert to connecting via the OLEDB provider and using an expression based MDX query. See this thread for full details http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=726232&SiteID=1|||

Hi @.All,

After a lot of testing, I got it to work. The solution is, you have to mask the Parameter with apostrophes like this:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER("" + @.SalesFigure + "")
SELECT NON EMPTY .....

The key is, to mask it with double apostophes to escape the "single apostroph". Now this part of my many starting problems works!

Hans