Functions

Functions tab extends sheets with TM1 specific functionality and all the actions will be performed on the current/active sheet

TEAMONE recalculate

img

Recalculates all TEAMONE formulas on the current sheet. TEAMONE formula allows to bring data from TM1 server directly to sheets in 2 steps

STEP 1. Define a formula

TEAMONE formula expects the next arguments:

  • cube connection - "TM1 connection:cube name" string, where TM1 connection is one of the configurated connections. You can specify only the cube name to fetch data from the currently selected TM1 connection in TeamOne sidebar.
  • rangeRows - select the cells represeting your rows
  • rangeColumns - select the cells represeting your columns
  • rangeTitles - select the cells represeting your context elements For rows and columns you can select multiple cells, a single cell or even no cells, use an empty string or leave the argument empty:
// Revenue cube in TM1QA environment
=TEAMONE('TM1QA:Revenue', A4:A5, B2:C3, A2:A3)

// No connection name. No rows (argument #2 is skipped). Hardcoded title
=TEAMONE('Revenue', , C1:F1, 'Gross Revenue')

Range

A range should represent TM1 dimension element names or aliases. You can use 2 dimensional ranges for rows and columns (to use multiple cube dimensions), but titles range should only include either a single cell or a one dimensional range. You should specify at least 1 row or 1 column or 1 title range

Repetitive elements

If you have multiple rows and/or columns, you don't have to specify repetitive elements for each row or column. TeamOne will automatically use the same element name if the next rows/columns are blank

img

See ::TEAMONE formula example

STEP 2. Recalculate the values

Go to TeamOne sidebar > Functions tab and click TEAMONE recalculate. Depending on how many formulas you have on the current sheet and your TM1 model and data complexity, it may take a few seconds or much longer to fetch all the requested data.

Hints

Dimensions and elements

You do not need to include elements for all the cube dimensions. TeamOne will select the default member for ech mitted dimensions.

Alternative hierarhies

For each first range element, you can specify the dimension and hierarchy using the next syntax: dimension:hierarchy:element This is also recomended for better performance and in case when you use element(s) with the same name existing in other dimensions

img

Autogenerate formula

You can easily autogenerate a formula using the cube/view menu button on the Model tab

img

Increase indent

Being a really great tool, sheets are missing 2 simple functions to increase and decrease cell indentation. Increase indent adds 1 space (on the left) to the selected cells. This function can help you when defining a TM1 dimension hierarchy using space indentation img

Decrease indent

Opposite to the function above, Decrease indent trims 1 space (from the left) of the selected cells.

Add code

Though PAW provides a web interface to do TM1 modeling, it is still very tedious to develop and update your models, document your changes and deploy them to other environments. TeamOne allows to create/update tm1 cubes, views, dimensions, hierarchies, subsets, elements, attributes, manage users, groups, permissions, object security... without a single line of code. It is like you use Sheets to write a documentation and the documentation is used to update your model automatically. Moreover:

  • you can collaborate with all your team members when you have new TM1 requirements or change requests
  • a model draft gathered during reqruiements discussions can replace up to 95% of the development
  • you can have maximum involvement of analysts and business users, for instance, you can ask somebody to translate element captions to a different language and then you will be only one click to get the new localized captions syncronized in TM1.
  • your documentation is created before the development has even started, moreover, it gets updated as soon as you change anything
  • you can prototype and implement real models in seconds

Add code will open an interface where you can add a template to:

  • Create/update cube
  • Create/update view
  • Create/update dimension hierarchy
  • Create/update subset
  • Manage cube security
  • Manage dimension security
  • Manage element security
  • Manage application security
  • Manage process security
  • Add prolog code
  • Add epilog code

You fill out the basic template form and click ADD TO ACTIVE SHEET button, where you will be able add more fields and customize the code block. Once done, you can format and run code.

::cube

Use ::cube block to create and update cubes. You specify the cube name in the second column

Option / parameterDescription
-recreateInclude this option if you want to recreate cube (you don't need to specidy any value in column B). This option is mainly used in the dev environment when you need to change cube dimensions. IMPORTANT: recreating the cube will also delete all cube data.
-captionAllows to specify a cube caption. You can localize the cube name by adding multiple caption options in a format -caption:locale, for instance -captions:en, -caption:fr, etc. The locale should exist in }Cultures dimension.
Dimension 1-NUse a separate row to specify the needed cube dimension names in the order you want them to be in the cube. Remember you can have up to 256 dimensions in TM1 cube.

In the next example let's create XR cube that consists if 3 dimensions. We will also add Exchange Rates caption. Later we can add other localized captions: img

See ::cube example

TIP

For existing cubes you can automatically generate a cube definition from the model tab cube menu

::view

Use ::view block to create and update a cube view. You specify the view name in the second column

Option / parameterDescription
-cubecube name
-subsetdefault subset in a format $SubsetName, will be used for all the ommited dimensions. $ALL subset keyword will be used if this option is omitted
-aliasdefault subset alias, if exists the alias will be applied to each created subset. If subset already exists, its current alias will not be updated
-recreaterecreate default subset, it is used with the -subset option above. For instance, you specify -subset option as '$LEAVES'. Then it will recreate the LEAVES subset for each dimension you omitted in the current view definition
Dimension 1-Nenter a dimension name in column A, subset\expression in column B and subset options in column C. Use the next syntax to set a view row or column dimension: R:dimensionName, C:dimensionName. See below how to define elements\subset\mdx for each dimension subset in column B

In column B for each dimension you can define a subset or MDX expressions as:

  • a comma delimited element list, i.e.: Actual,Budget, Forecast
  • an existing subset name in a format $SubsetName
  • $ALL, $LEAVES and $ROOTS system subsets. TeamOne will create a system subset if it does not exist using the next MDX expressions:
    • ALL: {HIERARCHIZE({[dimensionName].[hierarchyName].members})}
    • LEAVES: {TM1FILTERBYLEVEL( {[dimensionName].members}, 0)}
    • ROOTS: {FILTER( {[dimensionName].members}, [dimensionName].CurrentMember.Parent.Name = "")}
  • a valid MDX expression, i.e.: {[Calendar]}.[2022].Children}
  • a pattern using "*" for any characters and "?" for any one character, i.e.: /*substring*/ or /2022 Q?/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: Current period,Previous period,{[Calendar]}.[2022].Children,/2022 Q?/ If you leave Column B value empty, the -subset option will be used

img

In column C you can enter subset options in the next format: '-name:Total Company -alias:Caption_Base', which will name the subset as Total Company and will set the Caption_Base alias. See more subset options below

img

See ::view example

TIP

For existing cubes you can automatically generate a view definition from the model tab cube or view menu

::dimension

img Use ::dimension block to create and update dimensions. You specify the dimension name in the second column. Starting from column 3 you can specify element attributes in a format: AttributeName:AttributeType:AttributeLocale, where AttributeType is one of the following:

  • S (default) - string
  • A (default) - alias
  • N (default) - numeric

AttributeLocale is an element in }Cultures dimension.

You can ommit AttributeType and/or AttributeLocale, TeamOne will assume a string non-localized attribute, for example on the next screenshow TeamOne will create create Caption alias, MonthNumber numeric and MonthName string attributes: img

Option / parameterDescription
-hierarchyIf this option is specified, it will create an alternative hierarchy, if omitted the default hiearchy will be created
-recreateInclude this option if you want to recreate the dimension elements (you don't need to specidy any value in column B). This option is mainly used in DEV environment when you need to delete the elements first before re-building the dimension hierarchy. IMPORTANT: all the data for the deleted elements will be lost
-componentSortSet the component sorting. Values: ByInput (default), ByName
-componentOrderSet the component sorting order. Values: Ascending (default), Descending
-elementSortSet the element sorting. Values: ByHierarchy (default), ByInput, ByName, ByLevel
-elementOrderSet the element sorting order. Values: Ascending (default), Descending
-captionAllows to specify a dimension caption. You can localize the dimension name by adding multiple caption options in a format -caption:locale, for instance -captions:en, -caption:fr, etc. The locale should exist in }Cultures dimension.
-subset.aliasSpecify the alias in Column B to be used for the Default and system subsets (ALL, LEAVES, ROOTS). No alias will be used if omitted
-subset.expandaboveWill expand above the Default and system subsets (ALL, LEAVES, ROOTS)
-subset.dynamicWill make dynamic the Default and system subsets (ALL, LEAVES, ROOTS)
-subset.leavesThe value in Column B will be used as the LEAVES subset expression. $LEAVES subset keyword will be used if omitted.
-subset.allThe value in Column B will be used as the ALL subset expression. $ALL subset keyword will be used if omitted.
-subset.rootsWill create ROOTS subset. The value in Column B will be used as a subset expression. $ROOTS subset keyword will be used if omitted.
Element 1-NUse a separate row to specify the dimension element names in the order you want them to be in the dimension. There are 2 ways to organize elements into a parent-child hierarchy: you can eiher use a space indentation in column A or you can specify the parent element in column B. You can specify the element type and weight after a semicolon, for example: ElementName:-1 will add ElementName child element with -1 weight; Comment:S will create a string element. If attributes are defined above, you can specify the element attribute values in the corresponding columns

For instance let's create Period dimension containing 2022 and 2023 months. Let's also add en and fr localized captions. Also let's create the default and system subset: img

See ::dimension example

We can quickly add a Fiscal Quarter alternative hierarchy img

See ::hierarchy example

::subset

Use ::subset block to create and update subsets. You specify the subset name in the second column

Option / parameterDescription
-dimensionDimension to create a subset for
-hierarchyUse this option to specify which hiearchy to create a subset in. If omitted the default hiearchy will be used
-aliasUsed to specify a subset alias
-expandaboveUse this option to set the subset expand above flag. Use False value to disable it if needed
-dynamicInclude this option if you want to recreate a dynamic subset
Element 1-NUse a separate row to specify the elements to include in the subset. See below how to define subset elements\mdx for each

In Column A on each row you can specify:

  • an element name
  • a comma delimited element list, i.e.: Actual,Budget, Forecast
  • a valid MDX expression, i.e.: {[Calendar]}.[2022].Children}
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /2022 Q?/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: Current period,Previous period,{[Calendar]}.[2022].Children,/2022 Q?/

For instance let's create a few subsets for the Period dimension: (https://docs.google.com/spreadsheets/d/1wOI8onSk2GoOoVzSDh_EPbZj5pN5iQPPK62cDV3sr7k/edit#gid=416332645) img

See ::subset example

TIP

For existing dimensions you can automatically generate a dimension or hiearchy definition from the model tab dimension or hierarchy menu

Subset keywords

TeamOne simplifies using ALL, LEAVES and ROOTS subsets by providing subset keywords. In certain places, like ::view or ::dimension definitions, you can use a subset keyword instead of subset name or MDX expression. TeamOne will automatically check if subset exists and create one if needed:

Subset keywordDescription
$ALLWill refer to the ALL subset. If the subset does not exist, it will be created using the next MDX expression: {HIERARCHIZE({[dimensionName].[hierarchyName].members})}
$LEAVESWill refer to the LEAVES subset. If the subset does not exist, it will be created using the next MDX expression: {TM1FILTERBYLEVEL( {[dimensionName].[hierarchyName].members}, 0)}
$ROOTSWill refer to the ROOTS subset. If the subset does not exist, it will be created using the next MDX expression: {FILTER( {[dimensionName].[hierarchyName].members}, [dimensionName].[hierarchyName].CurrentMember.Parent.Name = "")}

MDX mods

You can manipulate with subsets expressions using MDX mods instead of the actual MDX. It saves time, makes it easy to use by non-technical users and simple to understand the subset content. For instance, compare this MDX expression:

{FILTER( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Period].[Period].[FY2022],[Period].[Period].[FY2023]}, ALL )}, 0 )}, [Period].[Period].[quarter] = "Q3" )}

to the equivalent expression using MDX mods:

FY2022,FY2023>>expand>>level=0>>quarter=Q3

wildcard expressions are supported as well:

FY20*>>descendants>>level=0>>status=OP?N>>first=5

MDX mods can be added to subset expression after double closing angle bracket >>. Please see the table below for all currently supported MDX mods:

MDX modDescription
expandPerforma a drill down for each member of the current set. On the backend it will wrap your current mdx into {TM1DRILLDOWNMEMBER( ${mdx}, ALL )}
childrenInclude direct children elements for each member of the current set. On the backend it will wrap your current mdx into {GENERATE( ${mdx}, [${dim}].[${hier}].CurrentMember.Children )}
descendantsInclude all descendant elements for each member of the current set. On the backend it will wrap your current mdx into {DESCENDANTS(${mdx})}
distinctRemoves duplicates from the current set. On the backend it will wrap your current mdx into {DISTINCT(${mdx})}
first=%number%Get the first number elements. On the backend it will wrap your current mdx into {HEAD( ${mdx}, ${number} )}
last=%number%Get the last number elements. On the backend it will wrap your current mdx into {TAIL( ${mdx}, ${number} )}
level=%number%Will filter the current set by the specified number. On the backend it will wrap your current mdx into {TM1FILTERBYLEVEL( ${mdx}, ${number} )}
%attribute%=%value%Will filter the current set by the specified attribute & value. You can use a wildcard expression for the attribute value. On the backend it will wrap your current mdx into {FILTER( ${mdx}, [${dim}].[${hier}].CurrentMember.Properties("${attribute}") = "${value}" )} or *{TM1FILTERBYPATTERN( ${mdx}, "${wildcard value}", "${attribute}")}*if a wildcard value is provided

::element.security

Use ::element.security block to define element security

You specify groups definitions starting from column 3. In each column you can use:

  • a subset name in a format: $SubsetName
  • a single group name
  • a comma delimited group list, i.e.: Group 1, Group 2, Group 3
  • a valid MDX expression, i.e.: {[}Groups].members}
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Sec*Level?/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: Managers,Deputy,/Sec*Level?/

Option / parameterDescription
-recreateWipe all security first
-createGroupsInclude this option if you want to create non-existing security groups
-dimensionDimension name (mandatory)
-hiearchyHierarchy name (optional), if omitted, the default hierarchy will be used
Element 1-NUse a separate row to specify dimension elements, see below for syntax. Starting from column 3 (under the corresponding group definition), you can specify the security privileges, which could be on of the following: NONE, READ, WRITE, RESERVE, ADMIN, LOCK. If ommited, the default NONE privilege will be set

Element 1-N syntax:

  • an element name
  • a comma delimited element list, i.e.: FY2022 Jan,FY2022 Feb,FY2022 Mar
  • a valid MDX expression, i.e.: {[Period].[Fiscal Quarter].[FY2022 Q1].children}
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /FY202? */
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: FY2022 Jan,FY2022 Feb,FY2022 Mae,/FY2021*/

img

See ::element.security example

TIP

You can automatically generate ::element.security definition from the model tab dimension or hiearchy menu

::dimension.security

Use ::dimension.security block to define dimension and hiearchy security (hiearchy security was introduced in IBM Planning Analytics 2.0.9.5)

You specify groups definitions starting from column 3. In each column you can use:

  • a subset name in a format: $SubsetName
  • a single group name
  • a comma delimited group list, i.e.: Group 1, Group 2, Group 3
  • a valid MDX expression, i.e.: {[}Groups].members}
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Sec*Level?/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: Managers,Deputy,/Sec*Level?/

Patterns make it super easy to setup security for alternate hierarchies, for instance: /Product:/ will set the security for all the hierarchies of the Product dimension

Option / parameterDescription
-recreateWipe all security first
-createGroupsInclude this option if you want to create non-existing security groups
Dimension/hiearchy 1-NUse a separate row to specify dimensions and hierarchies (in a format dimension:hiearchy), see below for syntax. Starting from column 3 (under the corresponding group definition), you can specify the security privileges, which could be on of the following: NONE, READ. If ommited, the default NONE privilege will be set

Dimension 1-N syntax:

  • a dimension name
  • a comma delimited dimension list, i.e.: Calendar:Fiscal Year,Employee,Job Type Assumption
  • a valid MDX expression, i.e.: {[}Dimensions].members}
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Revenu?😗/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: Revenue,Employee,/*assumption*/

img

See ::dimension.security example

TIP

You can automatically generate ::dimension.security definition from the model tab dimensions menu

::cube.security

Use ::cube.security block to define cube security

You specify groups definitions starting from column 3. In each column you can use:

  • a subset name in a format: $SubsetName
  • a single group name
  • a comma delimited group list, i.e.: Group 1, Group 2, Group 3
  • a valid MDX expression, i.e.: {[}Groups].members}
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Sec*Level?/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: Managers,Deputy,/Sec*Level?/

Option / parameterDescription
-recreateWipe all security first
-createGroupsInclude this option if you want to create non-existing security groups
Cube 1-NUse a separate row to specify cubes, see below for syntax. Starting from column 3 (under the corresponding group definition), you can specify the security privileges, which could be on of the following: NONE, READ, WRITE, RESERVE, ADMIN, LOCK. If ommited, the default NONE privilege will be set

Cube 1-N syntax:

  • a cube name
  • a comma delimited cube list, i.e.: Calendar,Employee,Job Type Assumption
  • a valid MDX expression, i.e.: {[}Cubes].members}
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Revenu?/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: Revenue,Employee,/*assumption*/

img

See ::cube.security example

Cube dimension security

TeamOne will also set READ privilege for each cube dimension if no access was defined previously. For instance, you want to give READ access for Group B to Revenue cube. Previously you gave WRITE access for Group B to Product dimension. When setting READ access to Revenue cube, TeamOne will set READ access to all the dimensions in Revenue cube, but will keep Product dimension as WRITE.

TIP

You can automatically generate ::cube.security definition from the model tab cubes menu

::application.security

Use ::application.security block to define element security

You specify groups definitions starting from column 3. In each column you can use:

  • a subset name in a format: $SubsetName
  • a single group name
  • a comma delimited group list, i.e.: Group 1, Group 2, Group 3
  • a valid MDX expression, i.e.: {[}Groups].members}
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Sec*Level?/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: Managers,Deputy,/Sec*Level?/

Option / parameterDescription
-recreateWipe all security first
-createGroupsInclude this option if you want to create non-existing security groups
Application 1-NUse a separate row to specify dimension elements, see below for syntax. Starting from column 3 (under the corresponding group definition), you can specify the security privileges, which could be on of the following: NONE, READ or ADMIN. If ommited, the NONE privilege will be set

Application 1-N syntax:

  • an application name
  • a comma delimited application list, i.e.: Analysis\Employee.view,Analysis\Expenses.view
  • a valid MDX expression, i.e.: "{TM1DRILLDOWNMEMBER( {[}ApplicationEntries].[Analysis]}, ALL, RECURSIVE )}"
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /Reports*/ or /Reports.????/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: Analysis\Employee.view,Reports*/

img

See ::application.security example

Default application security

Use -recreate option to default all application security to READ. Then specify which applications should be restricted using NONE, READ or ADMIN privilege.

::process.security

Use ::process.security block to define process security

You specify groups definitions starting from column 3. In each column you can use:

  • a subset name in a format: $SubsetName
  • a single group name
  • a comma delimited group list, i.e.: Group 1, Group 2, Group 3
  • a valid MDX expression, i.e.: {[}Groups].members}
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /Sec*Level?/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: Managers,Deputy,/Sec*Level?/

Option / parameterDescription
-recreateWipe all security first
-createGroupsInclude this option if you want to create non-existing security groups
Process 1-NUse a separate row to specify processes, see below for syntax. Starting from column 3 (under the corresponding group definition), you can specify the security privileges, which could be on of the following: NONE, READ. If ommited, the default NONE privilege will be set

Process 1-N syntax:

  • a process name
  • a comma delimited process list, i.e.: copy_version,create_version,cache_views
  • a valid MDX expression, i.e.: {[}Processes].members}
  • a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or */vers?ion*/
  • a subset keyword

You can also combine elements, patterns, MDX expressions, MDX keywords and MDX mods into a comma delimited list, i.e.: copy_version,create_version,/*create*/

img

See ::process.security example

::code.prolog

Use ::prolog.code (you can also use just ::code) block to write a prolog code It can be very handy when combining with sheets formulas, for instance, you can create and autopopulate TM1 functions based on the sheets values: img

See ::code.prolog example

::code.epilog

Use ::epilog.code block to write an epilog code It can be very handy when combining with sheets formulas, for instance, you can create and autopopulate TM1 functions based on the sheets values: img

See ::code.prolog example

comments

You can use a single line and multiline comments in ::code blocks:

  • # character will comment out a single line
  • #[ and #] will comment out multiple lines

img

See comments example

Format code

This function will format code and make it look like a documentation. Code formatting includes:

  • code block header: white text and indigo background
  • -options: italic text style
  • comments: grey text color
  • alternating colors background for all rows
  • rows grouping for code blocks

Run code

This function will execute code blocks on the current sheet.