SSRS - the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated

Question

I am new to SSRS, i make a report used three parameter FromDimDateDateKey,ToDimDateDateKey and InstrumentSName, and i got error that query execution failed for my DataSet and query (1,343) the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated. what should i do ? anybody can help me ?

this is My MDX Query :

SELECT 
NON EMPTY { [Measures].[Price] } ON COLUMNS,
NON EMPTY { 
    (
        [DimDate].[Date Key].[Date Key].ALLMEMBERS *
        [Dim Instrument].[Instrument Code].[Instrument Code].ALLMEMBERS *
        [Dim Instrument].[Instrument S Name].[Instrument S Name].ALLMEMBERS 
    ) 
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS 
FROM( 
    SELECT(
        STRTOSET(
            @DimInstrumentInstrumentSName,
            CONSTRAINED
        )
    ) ON COLUMNS
    FROM(
        SELECT(
            STRTOMEMBER(@FromDimDateDateKey, CONSTRAINED):
            STRTOMEMBER(@ToDimDateDateKey, CONSTRAINED) 
        ) ON COLUMNS 
        FROM [CUBE_SIAPDW]
    )
) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

What does it return @DimInstrumentInstrumentSName? Did you try: select StrToSet(@DimInstrumentInstrumentSName) on 0 from [CUBE_SIAPDW] ? Does it work? — Danylo Korostil

whytheq whytheq · Accepted Answer · 2017-09-20T13:17:25

It is telling you that this has failed:

STRTOSET(
            @DimInstrumentInstrumentSName,
            CONSTRAINED
        )

If you check-out the documentation for strtoset: https://docs.microsoft.com/en-us/sql/mdx/strtoset-mdx

It says that CONSTRAINED is optional and:

When the CONSTRAINED flag is used, the set specification must contain qualified or unqualified member names or a set of tuples containing qualified or unqualified member names enclosed by braces {}. This flag is used to reduce the risk of injection attacks via the specified string. If a string is provided that is not directly resolvable to qualified or unqualified member names, the following error appears: "The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated."

So, if using constrained, the paramenter @DimInstrumentInstrumentSName will need to be a string something like this - note the curly brackets:

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

SSRS - the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated

1 Answers