Content

The Content tab lists sheets that include TeamOne content so you can quickly navigate TeamOne Explorations and formulas. From here you can trigger actions on the active sheet—Expand/Collapse exploration rows, Upload data back to IBM Planning Analytics (TM1), Drill-through to detailed data, Increase/Decrease indent—and you can Recalculate all sheets that contain TeamOne content.

Recalculate

Recalculates all TEAMONE() formulas and the Exploration on the current sheet. Refresh time depends on your IBM Planning Analytics model complexity, the number of formulas on the sheet, and the volume of exploration data. It may take a few seconds or longer.

Expand/Collapse

Expands or collapses the selected exploration cell—equivalent to expanding/collapsing a consolidation element in the dimension subset editor in Explorer.

TeamOne will toggle the element state (collapsed → expanded or expanded → collapsed), then save and refresh the current sheet’s exploration.

Upload data

After you edit Exploration or TEAMONE() values in Google Sheets, you can upload (submit) the changes back to IBM Planning Analytics.

Drill-through

If drill-through links are defined in IBM Planning Analytics, you can drill through from TeamOne Explorations to view detailed data (cube views, databases, or files). Select an exploration cell in Google Sheets and choose Drill-through from the menu.

Increase indent

Google Sheets lacks built-in increase/decrease cell indentation. Increase indent adds one leading space to selected cells. This is helpful when defining a TM1 dimension hierarchy using space indentation.

Decrease indent

The opposite action—Decrease indent removes one leading space from the selected cells.

Recalculate all

Use Recalculate all to recalculate all sheets—or multiple selected sheets—that contain TeamOne content.

Add code

While PAW offers a web UI for TM1 modeling, it can still be tedious to build, document, and deploy changes. TeamOne lets you create/update TM1 cubes, views, dimensions, hierarchies, subsets, elements, and attributes; manage users, groups, permissions, and object security—without writing TI code by hand. In practice, you draft your model in Sheets (which doubles as living documentation), and TeamOne applies it to TM1 automatically.

Benefits:

Collaborate with your whole team on new requirements or change requests.
A requirements draft can replace up to 95% of manual development.
Involve analysts and business users (e.g., translate element captions) and sync them to TM1 in one click.
Documentation exists before implementation and stays updated as you change anything.
Prototype and implement real models in seconds.

TIP

This functionality requires the TM1 ADMIN group. The UI is hidden automatically for non-admin users.

Add code opens a template picker for:

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

Fill out the template and click ADD TO ACTIVE SHEET. You can then refine fields directly in the code block. When ready, Format and Run the code.

::cube

Use ::cube to create or update cubes. Specify the cube name in Column B.

Option / parameter	Description
`-recreate`	Recreate the cube (no value needed in Column B). Useful in DEV when changing cube dimensions. Important: recreating deletes all cube data.
`-caption`	Set a cube caption. Localize with multiple options in the form `-caption:locale` (e.g., `-caption:en`, `-caption:fr`). The locale must exist in the }Cultures dimension.
Dimension 1–N	One row per cube dimension, in the desired order (TM1 supports up to 256 dimensions).

Example: create an XR cube with 3 dimensions and an Exchange Rates caption. You can add other localized captions later.

See ::cube example

TIP

For existing cubes, generate a cube definition from the Model tab’s cube menu.

::view

Use ::view to create or update a cube view. Specify the view name in Column B.

Option / parameter	Description
`-cube`	Cube name.
`-subset`	Default subset in the form `$SubsetName`. Applied to omitted dimensions. If omitted, the $ALL keyword is used.
`-alias`	Default alias applied to each created subset. Existing subsets keep their alias.
`-recreate`	Recreate the default subset specified by `-subset`. For example, if `-subset` is `[$LEAVES](#subset-keywords)`, this recreates LEAVES for each omitted dimension.
Dimension 1–N	Put a dimension name in Column A, the subset/expression in Column B, and subset options in Column C. Use `R:dimensionName` or `C:dimensionName` to mark row/column axes.

Column B accepts:

A semicolon-delimited element list, e.g. Actual;Budget;Forecast
An existing subset name as $SubsetName
System keywords $ALL, $LEAVES, $ROOTS (TeamOne creates these if missing using the MDX under Subset keywords)
A valid MDX expression, e.g. {[Calendar].[2022].Children}
Wildcard patterns: /*substring*/ or /2022 Q?/*
A subset keyword

You can combine elements, patterns, MDX, keywords, and MDX mods in a semicolon-delimited list, e.g.: Current period;Previous period;{[Calendar].[2022].Children};/2022 Q?/* If Column B is empty, the -subset option is used.

Column C holds subset options, e.g. -name:Total Company -alias:Caption_Base. See more under ::subset.

See ::view example

TIP

For existing cubes, generate a view definition from the Model tab’s cube or view menu.

::rule

Use ::rule to create or update cube rules.

Option / parameter	Description
`-cube`	Cube name.
Rule line 1–N	Add as many lines as needed to define calculation rules.

::dimension

Use ::dimension to create or update dimensions. Put the dimension name in Column B. Starting Column C, define element attributes as AttributeName:AttributeType:AttributeLocale:

Type: S (string, default), A (alias), N (numeric)
Locale: element from the }Cultures dimension (optional)

You may omit Type and/or Locale; defaults to a non-localized string attribute. For example, TeamOne can create Caption (alias), MonthNumber (numeric), and MonthName (string) attributes:

Option / parameter	Description
`-hierarchy`	Create an alternative hierarchy; if omitted, updates the default hierarchy.
`-recreate`	Rebuild elements (no value in Column B). DEV only—deletes and rebuilds hierarchy. Important: data for deleted elements is lost.
`-componentSort`	`ByInput` (default) or `ByName`.
`-componentOrder`	`Ascending` (default) or `Descending`.
`-elementSort`	`ByHierarchy` (default), `ByInput`, `ByName`, or `ByLevel`.
`-elementOrder`	`Ascending` (default) or `Descending`.
`-caption`	Set/localize a dimension caption via multiple `-caption:locale` entries. Locales must exist in }Cultures.
`-subset.alias`	Alias to use for Default and system subsets (ALL, LEAVES, ROOTS).
`-subset.expandabove`	Enable “expand above” on Default and system subsets.
`-subset.dynamic`	Make Default and system subsets dynamic.
`-subset.leaves`	Expression for LEAVES; falls back to $LEAVES.
`-subset.all`	Expression for ALL; falls back to $ALL.
`-subset.roots`	Expression for ROOTS; falls back to $ROOTS.
Element 1–N	One row per element. Build hierarchy either with space indentation in Column A or by specifying parent in Column B. Append `:S` for string element or `:-1` for child weight −1, etc. If attributes are defined in headers, populate values in their columns.

Example: build a Period dimension with 2022/2023 months, add en and fr captions, and create Default/system subsets:

See ::dimension example

Add a Fiscal Quarter alternative hierarchy:

See ::hierarchy example

::subset

Use ::subset to create or update subsets. Put the subset name in Column B.

Option / parameter	Description
`-dimension`	Target dimension.
`-hierarchy`	Target hierarchy (optional); default hierarchy if omitted.
`-alias`	Subset alias.
`-expandabove`	Set the subset “expand above” flag (use `False` to disable).
`-dynamic`	Create a dynamic subset.
Element 1–N	One row per selection. See syntax below.

Column A accepts:

Element name
Semicolon-delimited list, e.g. Actual;Budget;Forecast
MDX, e.g. {[Calendar].[2022].Children}
Wildcards: /*substring*/ or /2022 Q?/*
Subset keywords

You can combine elements, patterns, MDX, keywords, and MDX mods, e.g.: Current period;Previous period;{[Calendar].[2022].Children};/2022 Q?/*

For instance, here are a few subsets for the Period dimension: (https://docs.google.com/spreadsheets/d/1wOI8onSk2GoOoVzSDh_EPbZj5pN5iQPPK62cDV3sr7k/edit#gid=416332645)

See ::subset example

TIP

For existing dimensions, generate a dimension or hierarchy definition from the Model tab’s dimension or hierarchy menu.

Subset keywords

TeamOne simplifies ALL, LEAVES, and ROOTS via keywords. Where supported (e.g., ::view, ::dimension), you can use a keyword instead of a subset name or MDX. TeamOne will create the subset if missing:

Keyword	Meaning / MDX used when creating
`$ALL`	“All members (hierarchized)”: `{HIERARCHIZE({[dimensionName].[hierarchyName].members})}`
`$LEAVES`	“Level 0 members”: `{TM1FILTERBYLEVEL({[dimensionName].[hierarchyName].members}, 0)}`
`$ROOTS`	“Top-level (no parent) members”: `{FILTER({[dimensionName].[hierarchyName].members}, [dimensionName].[hierarchyName].CurrentMember.Parent.Name = "")}`

MDX mods

Instead of writing full MDX, you can modify a base set using short MDX mods. This makes expressions easier for non-technical users.

Full MDX:

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

Equivalent with MDX mods:

FY2025 >> expand >> level=0 >> quarter=Q3

Wildcards are supported:

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

Append mods to a set after >>. Supported mods:

MDX mod	Description
`ancestors`	First parent for each member. Wraps as: `{HIERARCHIZE({GENERATE(${mdx}, UNION({[Dim].[Hier].CurrentMember.Ancestors}, {[Dim].[Hier].CurrentMember}))})}`
`ancestorsall`	All parents for each member. Similar to above, using `.AncestorsAll`.
`expand`	Drill down all members: `{TM1DRILLDOWNMEMBER(${mdx}, ALL)}`
`children`	Direct children: `{GENERATE(${mdx}, [${dim}].[${hier}].CurrentMember.Children)}`
`descendants`	All descendants: `{DESCENDANTS(${mdx})}`
`distinct`	Remove duplicates: `{DISTINCT(${mdx})}`
`first=<n>`	First n members: `{HEAD(${mdx}, <n>)}`
`last=<n>`	Last n members: `{TAIL(${mdx}, <n>)}`
`level=<n>`	Filter by level: `{TM1FILTERBYLEVEL(${mdx}, <n>)}`
`sortasc`	Sort ascending: `{TM1SORT(${mdx}, ASC)}`
`sortasc=<Attribute>`	Sort ascending by attribute: `{ORDER(${mdx}, [Dim].[Attribute], ASC)}`
`sortdesc`	Sort descending: `{TM1SORT(${mdx}, DESC)}`
`sortdesc=<Attribute>`	Sort descending by attribute: `{ORDER(${mdx}, [Dim].[Attribute], DESC)}`
`<attribute>=<Val1\|Val2,...>`	Attribute filter. Multiple values via pipe: `quarter=Q1\|Q2`. Wildcards allowed. Maps to `FILTER` or `TM1FILTERBYPATTERN` depending on value.
`toggle=<Elm1,Elm2,...>`	Toggle (expand/collapse) specific elements: `{ToggleDrillState(${mdx}, {[Dim].[Hier].[Elm1]}, {[Dim].[Hier].[Elm2]}, ...)}`

::element.rename

Use ::element.rename to rename dimension/hierarchy elements without losing data.

Option / parameter	Description
`-dimension`	Dimension name.
`-hierarchy`	Hierarchy (optional; default hierarchy if omitted).
Element 1–N	Put existing element names in Column A and new names in Column B. Non-existing elements are skipped (e.g., already renamed or typo).

See ::element.rename example

TIP

You can combine ::element.rename and ::dimension blocks on the same sheet to manage a dimension from one place.

::element.security

Use ::element.security to define element-level security.

Define group sets starting from Column C. In each group column you may use:

A subset name like $SubsetName
A single group name
A semicolon-delimited list, e.g. Group 1; Group 2; Group 3
MDX, e.g. {[}Groups].members}
Wildcards, e.g. /*substring*/ or /Sec*Level?/*
A subset keyword

You can combine patterns, MDX, keywords, and MDX mods within the same cell.

Option / parameter	Description
`-recreate`	Wipe all existing element security first.
`-createGroups`	Create non-existing groups if needed.
`-dimension`	Required dimension name.
`-hierarchy`	Optional hierarchy name (default hierarchy if omitted).
Element 1–N	One row per element selector. Under each group column, set privileges: `NONE`, `READ`, `WRITE`, `RESERVE`, `ADMIN`, or `LOCK`. Blank defaults to `NONE`.

Element selector (Column A) supports:

A single element name
A semicolon-delimited element list (e.g. FY2022 Jan;FY2022 Feb;FY2022 Mar)
MDX (e.g. {[Period].[Fiscal Quarter].[FY2022 Q1].children})
Wildcards (e.g. /*substring*/ or /FY202? */*)
A subset keyword

See ::element.security example

TIP

Generate ::element.security from the Model tab’s dimension or hierarchy menu.

::dimension.security

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

Group definitions start at Column C. In each group column you may use subset names, single names, lists, MDX, or wildcards (same options as element security).

Patterns are great for alternate hierarchies—for example, /Product:/* targets all hierarchies of the Product dimension.

Option / parameter	Description
`-recreate`	Wipe all existing dimension/hierarchy security first.
`-createGroups`	Create non-existing groups if needed.
Dimension/Hierarchy 1–N	One row per target in the form `dimension:hierarchy` (hierarchy optional). Under each group column, set privileges: `NONE` or `READ`. Blank defaults to `NONE`.

Selector syntax:

Dimension name (or dimension:hierarchy)
Semicolon list (e.g. Calendar:Fiscal Year;Employee;Job Type Assumption)
MDX (e.g. {[}Dimensions].members})
Wildcards (e.g. /*substring*/ or /Revenu?:*/)
Subset keywords

See ::dimension.security example

TIP

Generate ::dimension.security from the Model tab’s dimensions menu.

::cube.security

Use ::cube.security to define cube-level security.

Group definitions start at Column C. You may use subset names, single names, lists, MDX, or wildcards (same options as above).

Option / parameter	Description
`-recreate`	Wipe all existing cube security first.
`-createGroups`	Create non-existing groups if needed.
Cube 1–N	One row per cube. Under each group column, set privileges: `NONE`, `READ`, `WRITE`, `RESERVE`, `ADMIN`, `LOCK`. Blank defaults to `NONE`.

Cube selector supports:

Cube name
Semicolon list (e.g. Calendar;Employee;Job Type Assumption)
MDX (e.g. {[}Cubes].members})
Wildcards (e.g. /*substring*/ or /Revenu?/*)
Subset keywords

See ::cube.security example

Cube dimension security

When you grant cube access (e.g., READ on Revenue to Group B), TeamOne ensures each dimension in that cube has at least READ—but preserves any stronger existing access (e.g., WRITE on Product remains WRITE).

TIP

Generate ::cube.security from the Model tab’s cubes menu.

::application.security

Use ::application.security to define application entry security.

Group definitions start at Column C. Same input options as other security blocks (subsets, names, lists, MDX, wildcards).

Option / parameter	Description
`-recreate`	Wipe all existing application security first.
`-createGroups`	Create non-existing groups if needed.
Application 1–N	One row per application entry. Under each group column, set privileges: `NONE`, `READ`, or `ADMIN`. Blank defaults to `NONE`.

Application selector supports:

Entry name, e.g. Analysis\Employee.view
Semicolon list (e.g. Analysis\Employee.view;Analysis\Expenses.view)
MDX (e.g. "{TM1DRILLDOWNMEMBER( {[}ApplicationEntries].[Analysis]}, ALL, RECURSIVE )}")
Wildcards (e.g. /Reports\*/* or /Reports.????/*)
Subset keywords

See ::application.security example

Default application security

Use -recreate to default all application entries to READ, then restrict specific entries with NONE, READ, or ADMIN.

::process.security

Use ::process.security to define process security.

Group definitions start at Column C. Same input options as other security blocks (subsets, names, lists, MDX, wildcards).

Option / parameter	Description
`-recreate`	Wipe all existing process security first.
`-createGroups`	Create non-existing groups if needed.
Process 1–N	One row per process. Under each group column, set privileges: `NONE` or `READ`. Blank defaults to `NONE`.

Process selector supports:

Process name
Semicolon list (e.g. copy_version;create_version;cache_views)
MDX (e.g. {[}Processes].members})
Wildcards (e.g. /*substring*/ or /*vers?ion*/*)
Subset keywords

See ::process.security example

::code.prolog

Use ::prolog.code (or simply ::code) to write prolog code. This is handy with Sheets formulas—for example, generate and populate TM1 functions based on cell values:

See ::code.prolog example

::code.epilog

Use ::epilog.code to write epilog code. This is also useful with Sheets formulas—for example, finalize/clean up after TM1 actions:

See ::code.epilog example

Comments

You can use single-line and multi-line comments in ::code blocks:

# comments a single line.
#[ and #] wrap a multi-line comment block.

See comments example

Format code

Formats code to read like documentation:

Code block headers: white text on indigo background
-options: italic text
Comments: grey text
Alternating row backgrounds
Row grouping by code block

Run code

Executes the code blocks on the current sheet.

Content ​

Recalculate ​

Expand/Collapse ​

Upload data ​

Drill-through ​

Increase indent ​

Decrease indent ​

Recalculate all ​

Add code ​

::cube ​

::view ​

::rule ​

::dimension ​

::subset ​

Subset keywords ​

MDX mods ​

::element.rename ​

::element.security ​

::dimension.security ​

::cube.security ​

::application.security ​

::process.security ​

::code.prolog ​

::code.epilog ​

Comments ​

Format code ​

Run code ​