Post reply

MDX error : The restrictions imposed by the CONTRAINED flag in the STRTOSET function were violated

  • December 21, 2015 at 5:46 am

    #303393

    Hi,

    I'm getting the error "The restrictions imposed by the CONTRAINED flag in the STRTOSET function were violated" when I select multiple values on one of my parameter called Niche for a dataset with the query below.

    The Niche parameter (@ConsultantPointInTimeConsultantNiche)

    Can someone help how can I resolve this error.

    SELECT NON EMPTY

    { [Measures].[# Of Treasury Client Excl Zero Balance],

    [Measures].[# Of Treasury Accounts Closing Excl Zero Balance],

    [Measures].[Treasury Book Size Closing] } ON COLUMNS,

    NON EMPTY {{[Basel Category].[Basel3 Client Type].[All]} * {[Funding Junk].[Client Name Hierarchy].[All]},

    [Basel Category].[Basel3 Client Type].[Basel3 Client Type].ALLMEMBERS * [Funding Junk].[Client Name Hierarchy].[P1 Code Consultant].ALLMEMBERS,

    [Basel Category].[Basel3 Client Type].[Basel3 Client Type].ALLMEMBERS * [Funding Junk].[Client Name Hierarchy].[Consultant Team].ALLMEMBERS,

    [Basel Category].[Basel3 Client Type].[Basel3 Client Type].ALLMEMBERS * [Funding Junk].[Client Name Hierarchy].[Consultant Niche].ALLMEMBERS,

    [Basel Category].[Basel3 Client Type].[Basel3 Client Type].ALLMEMBERS * [Funding Junk].[Client Name Hierarchy].[Consultant Branch].ALLMEMBERS,

    [Basel Category].[Basel3 Client Type].[Basel3 Client Type].ALLMEMBERS * {[Funding Junk].[Client Name Hierarchy].[All]},

    ([Basel Category].[Basel3 Client Type].[Basel3 Client Type].ALLMEMBERS * [Funding Junk].[Client Name Hierarchy].[Client Name].ALLMEMBERS )

    } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

    FROM ( SELECT ( STRTOSET(@ConsultantPointInTimePCodeConsultantName, CONSTRAINED) ) ON COLUMNS

    FROM ( SELECT ( STRTOSET(@ConsultantPointInTimeConsultantTeamName, CONSTRAINED) ) ON COLUMNS

    FROM ( SELECT ( STRTOSET(@ConsultantPointInTimeConsultantNiche, CONSTRAINED) ) ON COLUMNS

    FROM ( SELECT ( STRTOSET(@ConsultantPointInTimeConsultantBranchName, CONSTRAINED) ) ON COLUMNS

    FROM ( SELECT ( STRTOMEMBER(@FromPeriodFinancialCalendarHierarchy, CONSTRAINED) : STRTOMEMBER(@ToPeriodFinancialCalendarHierarchy, CONSTRAINED) ) ON COLUMNS

    FROM [IPBDMP2Funding])))))

    WHERE ( IIF( STRTOSET(@ConsultantPointInTimeConsultantBranchName, CONSTRAINED).Count = 1,

    STRTOSET(@ConsultantPointInTimeConsultantBranchName, CONSTRAINED),

    [Consultant - Point In Time].[Consultant Branch Name].currentmember ),

    IIF( STRTOSET(@ConsultantPointInTimeConsultantNiche, CONSTRAINED).Count = 1,

    STRTOSET(@ConsultantPointInTimeConsultantNiche, CONSTRAINED),

    [Consultant - Point In Time].[Consultant Niche].currentmember ),

    IIF( STRTOSET(@ConsultantPointInTimeConsultantTeamName, CONSTRAINED).Count = 1,

    STRTOSET(@ConsultantPointInTimeConsultantTeamName, CONSTRAINED),

    [Consultant - Point In Time].[Consultant Team Name].currentmember ),

    IIF( STRTOSET(@ConsultantPointInTimePCodeConsultantName, CONSTRAINED).Count = 1,

    STRTOSET(@ConsultantPointInTimePCodeConsultantName, CONSTRAINED),

    [Consultant - Point In Time].[P1 Code - Consultant Name].currentmember )

    ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

  • December 22, 2015 at 4:05 am

    #1847379

    If there are multiple values, you need to include curly braces...

    Example:

    SELECT StrToSet (

    '{[Geography].[Geography].[Country].[Germany]

    ,[Geography].[Geography].[Country].[Canada]}'

    , CONSTRAINED)

    ON 0

    FROM [Adventure Works]

    gsc_dba

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

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