Converting mdx parameter to ssrs between dates

Question

I have several section of similar MDX code that has been created in Microsoft SQl Server Management Studio using cube data and I now need to use the code in a SSRS paginated report. I'm getting the following error:

"Query (4, 2) The restrictions imposed by the CONSTRAINED flag in the 
STRTOMEMBER function were violated."

This code works fine in Management Studio and SSRS using a date but as soon as I change the date to a parameter I get the error.

MEMBER [Measures].[Sales in Period2] AS
AGGREGATE (
{STRTOMEMBER("[Paid Date].[Date].&[2020-11-01]", CONSTRAINED) : STRTOMEMBER(" 
[Paid Date].[Date].&[2020-11-30]", CONSTRAINED) }
, [Measures].[Paid Amount]
),FORMAT_STRING = "#.00;(#.00);0;0"

I've tried changing:

[2020-11-01] to [@StartDate1], 
[2020-11-01] to [" + @ParameterName + "],
STRTOMEMBER to STRTOSET, and
remove CONSTRAINED.

Not sure about the MDX syntax, but when all else fails you can use the Dataset Expression builder to put the query together as a sting. ="MEMBER [Measures].[Sales in Period2] ... {STRTOMEMBER(""[Paid Date].[Date].&[" & FORMAT(@Parameter, "yyyy-MM-dd") & ""]", CONSTRAINED) ... — Hannover Fist

Subbu Subbu · Accepted Answer · 2021-03-06T13:49:04

One possibly way to approach this:

This error says that your @ParameterName is not in the correct format. In your example you have the date as 2020-11-30, i.e., yyyy-MM-dd format. So, your parameter should also have the same format. If the format is same you can use it &[@ParameterName] in your StrToMember.

To achieve this, you can change the Dataset of your parameter to format it in the required format.

Since you are using MDX to get the data, you can look at the option of getting the parameter values directly from your main data set or the date dimension, Paid Date in your example.

In my sample below using the AdventureWorks sample, you can see the date format is different. You may want to show the date format in a different format to the users, but internally you would want it in the same format as your Cube wants it.

I would suggest you look at this link as well to see an example end to end flow with MDX parameters: https://blog.pragmaticworks.com/writing-parametrized-mdx-for-reporting-services

Good luck

Converting mdx parameter to ssrs between dates

1 Answers