SUBNM

SUBNM returns the member of a set corresponding to the IndexOrName argument.

If you include the optional alias parameter to this function, the function returns the alias for the selected member.

When you double-click a cell containing a SUBNM function and the data source that contains the SUBNM function dimension is selected, the Set Editor opens.

When you click a cell containing a SUBNM function that references a set, a drop-down arrow is displayed. Click the down arrow to select a different member from the set. This feature is available when you are logged on to a data source that contains the dimension.

Syntax

SUBNM(server:dimension, set, IndexOrName, [alias])
Table 1. SUBNM syntax

Argument

Description

 Required/Optional

server:dimension

The name of the TM1® server and a dimension name.

 Required

set

The name of a set in the dimension. If the value of the set argument is an empty string, the value is considered subset all.

 Required

IndexOrName

An index into the set or the name of a member in the set.

If an index, a positive integer less than or equal to the total number of members in the specified set. If a name, a string representing the name of a member of the set. You can also use the alias of a member.

 Required

alias

The name of an alias that exists for the set. If it is used, the specified alias is applied when the Set Editor opens and the function returns the alias for the selected member.

 Optional

Example

The following examples use the GO_New_Stores TM1 server, a dimension called Country and Region, and a set called Countries Only.
  • Countries Only
    • Americas
    • Central Europe
    • Northern Europe
    • Southern Europe
    • Asia-Pacific
SUBNM("GO_New_Stores:Country and Region", "Countries Only",
"Americas")

In this example, the function returns Americas.

SUBNM("GO_New_Stores:Country and Region", "Countries Only",2)

In this example, the function returns Central Europe, which is the second member in the Countries Only set of the Country and Region dimension.

The following examples use the Planning Sample TM1 server, a dimension called plan_department, and a set called All Departments. The members in this dimension are named with department ID numbers. The Department alias contains the department names.

The following table lists the members in the All Departments set. The first column lists the member names. The second column shows the Department alias of each member.

member name Department
1000 Total Organization
100 Sales
200 Marketing
300 Engineering 
SUBNM("Planning Sample:plan_department","All Departments",
"300","Department")

In this example, the function returns Engineering. The third argument is the member name, 300, and the fourth argument, Department, is the name of the alias to retrieve. The Department alias for 300 is Engineering.

SUBNM("Planning Sample:plan_department","All Departments",
"100","Department_Spanish")

Suppose another alias, Department_Spanish, contains department names in Spanish. In this example, the function returns Ventas. The third argument is the member name, 100, and the fourth argument, Department_Spanish, is the name of the alias to retrieve. The Department_Spanish alias for 100 is Ventas.

When you double-click the cell containing this example, the Set Editor opens. The Available Members list displays the Spanish aliases for the plan_department dimension and its members.