The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated.

Question

The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated.

prabhu.st

SSCrazy

Points: 2552
More actions
July 23, 2015 at 5:31 am

#300567

Hi,
I am trying to implement the "calendar picker" functionality in my report, obviously the steps for this functionality has implemented successfully, but, while running the report I am getting error as below. Please help..
Thanks in advace,
Prabhu
Error:
Query (31, 5) The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated.
//Query to get the date value (DataSet)
WITH MEMBER
[Measures].[ParameterCaption] AS [BeginTime].[CALENDAR DAY].CURRENTMEMBER.MEMBER_CAPTION MEMBER
[Measures].[ParameterValue] AS [BeginTime].[CALENDAR DAY].CURRENTMEMBER.UNIQUENAME MEMBER
[Measures].[ParameterLevel] AS [BeginTime].[CALENDAR DAY].CURRENTMEMBER.LEVEL.ORDINAL
SELECT{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS ,
[BeginTime].[CALENDAR DAY].ALLMEMBERS ON ROWS
FROM [DataCube]
//Main DataSet
SELECT
NON EMPTY
{
[Measures].[xxxxx],
[Measures].[bbbb],
[Measures].[cccc],
[Measures].[ddddd], [Measures].[yyyyy]
}
ON COLUMNS,
NON EMPTY
{
(
[xxx].[xxx].[xxx].ALLMEMBERS *
[BeginTime].[CALENDAR DAY].[CALENDAR DAY].ALLMEMBERS [Data Source].[Data Source].[Data Source].ALLMEMBERS
)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM
(
SELECT
(
STRTOMEMBER(@FromBeginTimeCALENDARDAY, CONSTRAINED) :
STRTOMEMBER(@ToBeginTimeCALENDARDAY, CONSTRAINED)
) ON COLUMNS
FROM [DataCube]
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
//Parameter expressions used in the Main Dataset properities as below
="[BeginTime].[CALENDARDAY].&["+format(Parameters!FromBeginTimeCALENDARDAY.Value,"yyyyMMdd")+"]"
="[BeginTime].[CALENDARDAY].&["+format(Parameters!ToBeginTimeCALENDARDAY.Value,"yyyyMMdd")+"]"

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

prabhu.st SSCrazy Points: 2552 More actions · Answer 1

I found the reason, though there are several reasons for this error from MDX query through SSRS report, what I found is, the functionality of STRTOMEMBER / STRTOSET is getting violated, if the value passed through does not meet the following

1) doesn't match with the datatype of the Tuple

2) the expression was built with error (while designing the expression itself we may miss something)

3) may be some conversion error over the expression value

-x-

in my case the building the expression was the issue.

="[BeginTime].[CALENDARDAY].&["+format(Parameters!FromBeginTimeCALENDARDAY.Value,"yyyyMMdd")+"]"

in the above [CALENDARDAY] should be given like [CALENDAR DAY] so the space between CALENDAR & DAY have given me this headache.

hope this info may help someone.

MarksmanWaugh Old Hand Points: 300 More actions · Answer 2

Mighty helpful, thank you. I know it has been several years on, but this is exactly what was happening with me.

In one report against SSAS, I had it working just fine, as my field happened to be of the datetime variety. My SSRS expression to turn into a datetime for date picker was:
="[Event Outcome Date].[DATE].&["+format(Parameters!FromDimOutcomeDateDATE.Value,"yyyy-MM-ddT00:00:00")+"]"

When building another report, we naturally copied and pasted the expression but missed the fact that this particular date field was of a date type, no time included. Originally had:
="[Calendar].[Date].&["+format(Parameters!FromCalendarDate.Value,"yyyy-MM-ddT00:00:00")+"]"

but received this error. Moment of facepalm and updated it to:
="[Calendar].[Date].&["+format(Parameters!FromCalendarDate.Value,"yyyy-MM-dd")+"]"

and, of course, works. Posting this only as a reference to double check you are not including or excluding time based on your requirements!