Functions #

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

Recalculate #

Recalculates all TEAMONE formulas and exploration on the current sheet. TEAMONE formula allows to bring data from IBM Planning Analytics (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
• options - flags to "A"utofit the columns or use "F"ormatted values. You can combine flags: "AF" - fetch formatted values and autofit columns

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, return formatted values and autofit columns
=TEAMONE("TM1QA:Revenue", A4:A5, B2:C3, A2:A3,"AF")

// No connection name. No columns (argument #3 is skipped). Hardcoded title
=TEAMONE('Revenue', A4:A5, , 'Gross Revenue')

See ::TEAMONE formula example

STEP 2. Recalculate the values #

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

Hints #

Submit #

After you update the exploration data in Google Sheets you can upload it back to IBM PA. Use the Skip non-updatable flag to ignore all consolidated and rule calculated cells.

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

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

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:

See ::cube example

::view #

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

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

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

See ::view example

::rule #

Use ::rule block to create and update a cube rule

::dimension #

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:

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:

See ::dimension example

We can quickly add a Fiscal Quarter alternative hierarchy

See ::hierarchy example

::subset #

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

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)

See ::subset example

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:

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:

::element.rename #

Use ::element.rename block to rename dimension/hierarchy elements without loosing the data.

See ::element.rename example

::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?/

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*/

See ::element.security example

::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

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*/

See ::dimension.security example

::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?/

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*/

See ::cube.security example

::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?/

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*/

See ::application.security example

::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?/

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*/

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:

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:

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

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.