Passing unCONSTRAINED Set and Member parameters between reports in Reporting Services

By default SSRS MDX queries get a StrToMember or StrToSet functions with a CONSTRAINED flag. However, many developers do not quite know why it is there or what it actually does. Books-On-Line contains this statements:

StrToMember

  • When the CONSTRAINED flag is used, the member name must be directly resolvable to a qualified or unqualified member name. 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 a qualified or unqualified member name, the following error appears: “The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated.”
  • When the CONSTRAINED flag is not used, the specified member can resolve either directly to a member name or can resolve to an MDX expression that resolves to a name.

    • StrToSet

  • 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.”
  • When the CONSTRAINED flag is not used, the specified set specification can resolve to a valid Multidimensional Expressions (MDX) expression that returns a set.

    • Therefore, if you have a CONSTRAINED flag you have to specify exact members or sets (e.g. [Date].[Year].[2009], or {[Date].[Year].[2009],[Date].[Year].[2010]}). If you omit the flag, you can pass to the StrToMember an expression, which evaluates to a member (e.g. [Date].[Year].[Year].Members.Item(0)), and to StrToSet an expression, which evaluates to a set (e.g. NONEMPTY([Date].[Year].[Year].Members, [Measures].[Amount]).

    The flexibility which removing CONSTRAINED offers can be quite powerful when passing parameters between reports. In example, we may want to pass a parameter to a drill-through report from two different summary reports, where each of those uses a different subset of dimension members, which in turn can be derived by different set expressions.

    Major drawbacks of using this approach is the severe performance hit it leads to, as well as a possible “MDX injection” vulnerability. Since in most cases we would  be using the passed parameters in a subcube expression or on the slicer axis (WHERE clause), this should not lead to as bad performance as we would get if we would use it inside a calculation. However, when we need to use a parameter directly in a calculated measure, we would be better off avoiding an unCONSTRAINED function. 

     Therefore, we may instead use SetToStr on the summary reports and pass a String parameter to a CONSTRAINED StrToSet function in the drill-through report. This way we are first resolving the set expression once and passing it on to the underlying report as a string. We could do that in a calculated measure returning a String, then passed on as a Field to the drill-through parameter. However, in the rare case where we have many rows travelling from the SSAS server to the SSRS server, this could be slow.

    So, whether we use a StrToSet without CONSTRAINED flag, or a String parameter constructed by a SetToStr function, is dependent on the actual scenario, but it is good to have both options in our arsenal of tools and techniques when we need to implement some non-quite-standard piece of functionality.

    9 thoughts on “Passing unCONSTRAINED Set and Member parameters between reports in Reporting Services”

    1. Dear Boyan,

      Thanks for the eloquent and comprehensive explanation, however can you please show an example of passing a multi-value parameter that can be used by an MDX Select for an SSRS report?
      I.e. can a simple WHERE StrToSet(@Subcomponents, contrained) be used to filter the set or something else should be done to split the parameters?

      Thanks in advance,

      Maxim.

      Like

    2. It depends. On what the @Subcomponents parameter contains. To use it directly, it would have to contain something like:

      “{[dim].[hier].[lvl].[member1], [dim].[hier].[lvl].[member2]}” -> which is a string which explicitly defines the set

      So, if your SSRS parameter’s value is a UniqueName, it should just work. However, if it contains the Value – e.g. External, Non-Internal, Internal, etc. it will not since your @Param will look like:
      “External, Non-Internal, External”, and this does not translate well into a MDX set.

      Hope it helps – give it a go and display the parameter in the report just to see what’s it’s contents – if you get it to show you what you want – then try the STRTOSET function.

      Like

    3. Hi All , I need your immediate help please!!
      Thanks for those nice articles. I have one more question I have an SSRS report that uses SSAS cube. And I wanted to use two date parameters in the report. One is RecievedDate and the other is VaccinationDate and I have a string data type in both. And both dates als have Start and EndDate. I have formated the parameter to datetime to pick a date calander and worked for one of the dates but when I am using both dates with the same formatting and everything the error generates error.
      I apperciate your immediate help.Thanks,Yene

      Like

      1. Hi,

        Please send me your email address through the contact form on this blog – I will contact you back and you can send me your MDX and/or RDL file which will be needed to find the problem.

        Like

    4. Hi,
      Very clear, much appreciated this explanation. I would have a question regarding CONSTRAINED flag.
      I have a multivalue parameter that already contains 3 members enclosed by braces. That’s OK when I select only one, but if I select 2 values the member name is not qualified anymore and I have the error message: “The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.”
      How can I use a multivalue parameter (that already has more than 1 member name) in a CONSTRAINED function, please?
      Thank you!
      Georgeta

      Like

      1. Hi Georgeta,

        In order for STRTOMEMBER to work with explicitly specified set and the CONSTRAINED flag, your parameter string must be in the format: “{,…}” – in other words, with the curly brackets on both sides of the set and with explicit member definitions. You can debug by replacing the STRTOSET function with the actual string in SSMS and executing the query.

        Like

    5. @Boyan Penev

      Hi, thank you for your fast answer. I’ll try to give you an example:
      I don’t have time dimension in my cube and I need to create quarter,
      so Q1 = “{[period].[period].[period].&[1],[period].[period].[period].&[2],[period].[period].[period].&[3]}”
      Q2 = “{[period].[period].[period].&[4],[period].[period].[period].&[5],[period].[period].[period].&[6]}”
      … and so on.
      It’s working fine when I select 1 quarter but my parameter is multivalue and when I select 2 quarters the STRTOSET constrained gives me the error message above. Is there any way to solve that?
      Thank you!

      Like

      1. First, you should really get a proper time dimension in your cube…

        But, you could still achieve what you want by doing StrToSet(Union(@parameter)). Without the constrained flag.

        Like

    6. Thank you!
      I can’t change the cube, that’s the client’s. I changed the MDX query without CONSTRAINT flag and it works. I just asked myself what are the implications of that (performance, “MDX injection” vulnerability, or what else)
      Thank you again!

      Like

    Comments are closed.