Formulas

TEAMONE() Function

Using the TEAMONE() function you can easily retrieve data from IBM Planning Analytics. It expects the following arguments:

• cubeName - The name of the cube to retrieve data from.
• rangeRows - The selected cells representing your rows.
• rangeColumns - The selected cells representing your columns.
• rangeTitles - The selected cells representing your context elements.
• options - Flags to "A"utofit columns, use "F"ormatted values, allow "U"pdates, use "S"tatic values, "R"everse sign, "Q"uiet mode, and display "Z"ero values instead of blanks. You can combine flags: "AFSQZ" - fetch formatted static values, autofit columns, and enable quiet mode.
• solveOrder - Specifies the formula evaluation order. If omitted, the default value of 0 will be used.

For rows and columns, you can select single or multiple cells, and you can also include empty rows or columns:

// Retrieve data from Revenue cube, use formatted values and autofit columns
=TEAMONE("Revenue", A4:A5, B2:C3, A2:A3,"AF")

// No columns (argument #3 is skipped), hardcoded title element
=TEAMONE("Revenue", A4:A5, , 'Gross Revenue')

See =TEAMONE() formula example

TEAMONE and other Google Sheets functions

Please note that you can only use one TEAMONE() function per cell. However, you can combine it with other Google Sheets functions:

// Get a SUM of all values returned by TEAMONE() function
=SUM( TEAMONE(B2,B10:C16,D8:G9,B5:B7) )

// Flips the sign by multiplying each value by -1
=ARRAYFORMULA( -1 * TEAMONE(B2,B10:C16,D8:G9,B5:B7) )

See more =ARRAYFORMULA() formula examples

Autofit Columns

Use the "A" flag in the TEAMONE() function to automatically resize columns to fit the cell in each column that has the widest content.

Formatted Values

Use the "F" flag in the TEAMONE() function to retrieve formatted cube values from IBM Planning Analytics. Alternatively, you can apply formatting in Google Sheets, which is easier and provides more formatting options.

Static Values

When using the "S" flag, the =TEAMONE() formula will return a single value (the very first one), and the remaining values will be printed as static. This flag is recommended when using the TEAMONE() formula with hundreds of rows and/or columns to avoid the error message:

You have exceeded the property storage quota. Please remove some properties and try again.

Additionally, the static mode allows you to insert calculated rows and columns between the rows and columns generated by the TEAMONE() calculation. In this mode, TEAMONE will disregard any rows where elements are not provided for the last row or column dimension (the distribution channel on the rows dimension and the year dimension on the columns in the example below):

Update Mode

Use the "U" flag in the TEAMONE() function to enable data updates. The "U" flag also turns on static values as described above. You can submit the formula's data to IBM Planning Analytics using the upload data button available on the content tab.

Quiet Mode

If you skip some cube dimensions, TeamOne will automatically use default elements for those dimensions and will add them to the cell notes. Use the "Q" flag in the TEAMONE() function to disable notes for omitted dimensions.

Zero values

Use the "Z" flag in the TEAMONE() function to display zero values instead of blank cells.

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.

Multiple formulas performance

If using the same title elements, row/column dimensions, and formula flags, TeamOne will automatically detect and build a single query for each group of similar formulas, resulting in better performance.

Solve order

If you need to use the results of TEAMONE(), ATTR(), or SUBSET() functions as arguments for another TEAMONE(), ATTR(), or SUBSET() function, you can specify the solveOrder to control the order in which those results are recalculated. solveOrder must be 1 or greater. If omitted, the default value of 0 will be used.

TIP

It is recommended to use the same solveOrder value for multiple formulas that can be recalculated at the same time, as using multiple different values might increase the time taken to recalculate all formulas.

TIP

For both =TEAMONE() and =ATTR() functions, you can skip the options argument before specifying the solveOrder value. For example:

=ATTR('product', '21002', 'Product Color', 2)

Default elements

If you do not include elements from all cube dimensions, TeamOne will automatically select the default member for each skipped dimension and add a corresponding note to the cell. Use the quiet mode option to suppress notes.

Ambiguous element names

If you have elements with the same names in multiple dimensions, you can clarify the dimension name by using the following syntax: dimension^hierarchy^element

You can also specify the hierarchy if needed (see below).

Alternative hierarhies

Use the dimension^hierarchy^element syntax to specify the dimension and hierarchy for any title element or the first element of any row/column range.

Connection name

=TEAMONE() function always fetches data from the currently selected connection. Use ConnectionName:CubeName syntax to retrieve data from a different configured connection.

// Revenue cube in TeamOneTest environment
=TEAMONE("TeamOneTest:Revenue", A4:A5, B2:C3, A2:A3)

Create formulas automatically

You can let TeamOne build (autogenerate) the =TEAMONE() formula for you using an existing cube/view on the Model tab or from the exploration menu:

MDX() function

MDX() function allows you creating reports with dynamic rows and columns. It expects the following arguments:

• cubeName - the cube name to retrieve data from
• rowsExpressions - expressions to generate yours rows
• columnExpressions - expressions to generate yours columns
• rangeTitles - select the cells representing your context elements
• options - "F" formatted values, "S" static values, "R" include empty rows, "C" include empty columns, "H" hide the column headers. You can combine flags: "FSRC" - fetch formatted static values include empty rows and columns.

Dynamic rows and columns

You can specify dimension expressions for both rows and columns using the following format: dimName1^setExpression~aliasName * dimName2^setExpression~aliasName * ...

You can provide multiple dimension expressions separated by " * " (space asterisk space). Each expression should have the following structure: dimName^setExpression

You can specify an alternative hierarchy by using the following syntax: dimName^hierName^setExpression

You can specify an alias by using the following syntax: dimName^setExpression~aliasName

If no alias is specified, the functinon will return the element principal names.

For the setExpression you can use an existing subset name, MDX, MDX mods or element list, using the same set expressions as in the =SUBSET() function.

See =MDX() formula example with set expressions

The MDX() function will also return the column headers unless you specify the "H" option flag.

Dynamic rows and static columns

You can use the MDX() selecting a column range similar to the TEAMONE() function. TeamOne will automatically find the dimension names:

See =MDX() formula example with static column elements

MDX expression

You can provide an MDX expression in the MDX() function, for instance, you can copy-paste from IBM Planning Analytics Workspace:

See =MDX() formula example with MDX expression

Cube view

You can provide a cube view name in the MDX() function using the following syntax:

cubeName^viewName

See =MDX() formula example with a cube view

ATTR() function

ATTR() function retrieves the specified attribute value for the specified elements. It expects the following arguments:

• dimName - the dimension name, you can also use dimName:hierName syntax to specify an alternative hierarchy
• rangeElements - select the cells representing your elements
• attrName - attribute name, i.e. 'Caption'
• options - flag, "S" - static values.
• solveOrder - specifies the formula evaluation order. If omitted, the default value of 0 will be used.

See =ATTR() formula example

Dimension name

To specify a dimension name, simply enter the name. Alternatively, you can use the syntax dimName:hierName to specify an alternative hierarchy.

Element range

Select cells in a spreadsheet, you can choose a single cell, or a range of multiple horizontal or vertical cells. TeamOne will ignore any empty cells that are included in the range.

SUBSET() function

SUBSET() function retrieves dimension elements using the provided set expression. It expects the following arguments:

• dimName - the dimension name, you can also use dimName:hierName syntax to specify an alternative hierarchy
• setExpression - a valid MDX expression, $SubsetName or MDX mods
• attrName - attribute name, i.e. 'Caption'
• options - flag, "S" - static values.
• solveOrder - specifies the formula evaluation order. If omitted, the default value of 0 will be used.

See =SUBSET() formula example

Set expression

You can provide a valid MDX expression or use TeamOne subset syntax:

• a semicolon delimited element list, i.e.: Jan;Feb;Mar
• a pattern using "*" (any characters) and "?" (any one character), i.e.: /*substring*/ or /FY202? */
• a subset name after a dollar character, i.e. $My subset name
• a subset keyword, i.e.: $LEAVES or $ALL or $ROOTS

You can also combine elements, patterns, MDX expressions, subset keywords and MDX mods into a semicolon delimited list, i.e.: {[month].[Q1].children};/FY202*/>>sortasc

USERNAME() function

USERNAME() function returns the current user name from the IBM Planning Analytics connection. This function has no arguments.

See =USERNAME() formula example

UPDATED() function

UPDATED() function returns the date and time the current sheet data was refreshed from IBM Planning Analytics. This function has no arguments.

See =USERNAME() formula example