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

  • prabhu.st

    SSCrazy

    Points: 2552

    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")+"]"

  • prabhu.st

    SSCrazy

    Points: 2552

    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

    SSC Enthusiast

    Points: 160

    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!

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

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