TM1SubsetToSet

This function returns the members of a TM1® subset. TM1SubsetToSet is equivalent to the <dimension>.<subsetname> expression, but does not require string literals. Instead, TM1SubsetToSet lets you use expressions that resolve to the appropriate dimension and subset.

If a private and a public subset have identical names, enter the scope parameter to specify the scope of the search as private or public.

Syntax

TM1SubsetToSet(Dimension_exp, Subset_exp, Scope);

Argument

Description

Dimension_exp

An expression that resolves to a valid TM1 dimension name.

Use [dimension] or [dimension].[hierarchy] to specify a dimension.

Subset_exp

An expression that resolves to a valid subset of the dimension returned by Dimension_exp.

When resolving an expression for a subset, the server searches first in the private subset list and then in the public list.
Scope (optional)

The subset list to search, which is specified as private or public.

Specify public to search the public list only.

If this parameter is not specified and a private and a public subset have the same name, the private subset is returned.

Example

MDX sample code with dimension name [Corp Planning Hry] in the last line:

{INTERSECT(EXCEPT(DESCENDANTS([Corp Planning Hry].[Fixed Assets]),
TM1FILTERBYLEVEL(DESCENDANTS([Corp Planning Hry].[Fixed Assets]),0)),{
TM1SubsetToSet([Corp Planning Hry],"elist")}),[Corp Planning Hry].[FixedAssets]}

MDX sample code with fully qualified dimension name [Corp Planning Hry].[Corp Planning Hry] and [Corp Planning Hry].[FixedAssets] in the last line:

{INTERSECT(EXCEPT(DESCENDANTS([Corp Planning Hry].[Fixed Assets]),
TM1FILTERBYLEVEL(DESCENDANTS([Corp Planning Hry].[Fixed Assets]),0)),{
TM1SubsetToSet([Corp Planning Hry].[Corp Planning Hry],"elist")}),
[Corp Planning Hry].[FixedAssets]}

Example

MDX sample code with two subsets, one private, one public, both named 'MySub' under a Markets dimension. The following queries on columns from [QB] return the content of the private subset:

select TM1SubsetToSet( [QBMarkets].[QBMarkets], 'MySub' )

Or:

select TM1SubsetToSet( [QBMarkets].[QBMarkets], 'MySub', 'private' )

The following query on columns from [QB] returns the content of the public subset:

select TM1SubsetToSet( [QBMarkets].[QBMarkets], 'MySub', 'public' )