This project has moved. For the latest updates, please go here.

StrToSet

The functions in this class are designed to allow the StrToSet function to receive shorter strings by passing in just a list of keys, not the entire member unique name.

KeysStrToSet(string Hierarchy, string KeysCommaSeparated)

The following two functions return the same set:
StrToSet("{ [Product].[Product].&[1],[Product].[Product].&[2] }")
ASSP.KeysStrToSet("[Product].[Product]", "1,2")


CompositeKeysStrToSet(string Hierarchy, string KeysCommaSeparated)

The following two functions return the same set:
StrToSet("{ [Geography].[City].&[Austin]&[TX],[Geography].[City].&[Baltimore]&[MD] }")
ASSP.CompositeKeysStrToSet("[Geography].[City]", "&[Austin]&[TX],&[Baltimore]&[MD]")



Being able to pass in more concise representations of sets can be useful when writing Reporting Services reports. By default, the parameter value is the full unique name (i.e. [Geography].[City].&[Austin]&[TX]). For large multi-select parameters, that set can be quite large.

StrToSet in Reporting Services
The standard way of parameterizing an MDX query in Reporting Services would look like:
StrToSet(@Product)

The query to populate that Product parameter would look like:
with member [Measures].[ParameterValue] as
 [Product].[Product].CurrentMember.UniqueName


KeysStrToSet in Reporting Services
Parameterizing an MDX query in Reporting Services using KeysStrToSet would look like:
ASSP.KeysStrToSet("[Product].[Product]", @Product)

The query to populate that Product parameter would look like:
with member [Measures].[ParameterValue] as
 [Product].[Product].CurrentMember.Member_Key

KeysStrToSet will not work if the members have composite keys, or if the members have keys which are strings that may have a comma in them, or if the member is the all member or the unknown member, or if member keys are not unique across all levels of a user-defined hierarchy, or if the members have a nullable key (because the unique name for a member with a null key looks like [Dimension].[Attribute].&).


CompositeKeysStrToSet in Reporting Services
Parameterizing an MDX query in Reporting Services using CompositeKeysStrToSet would look like:
ASSP.CompositeKeysStrToSet("[Geography].[City]", @City)

The query to populate that City parameter would look like:
with member [Measures].[ParameterValue] as
 Mid([Geography].[City].CurrentMember.UniqueName, Len([Geography].[City].UniqueName)+2)

CompositeKeysStrToSet will work with any member keys and should be used under the scenarios outlined above when KeysStrToSet will not work.


For an example of a working report using KeysStrToSet and CompositeKeysStrToSet, see "KeysStrToSet Example.rdl" in the report project that is distributed with the source code.

For a more complete discussion of the scenarios in which this sproc can be helpful, please read this blog post.

Last edited Nov 8, 2013 at 3:33 PM by furmangg, version 6