Formulas

TEAMONE() Function

The TEAMONE() function allows you to efficiently retrieve data from IBM Planning Analytics directly into Google Sheets based on the specified elements.

Syntax

=TEAMONE(cubeName, rowElements, columnElements, contextFilters, options, solveOrder)

Arguments

• cubeName – The name of the IBM Planning Analytics cube from which to retrieve data.
• rowElements – The selected cells defining the rows of your report.
• columnElements – The selected cells defining the columns of your report.
• contextFilters – The selected cells defining context filters to refine your data selection.
• options – A set of flags that modify how data is retrieved and displayed. Multiple flags can be combined (e.g., "AFSQZ"). Available flags:
  • A – Autofit column width
  • F – Retrieve formatted values
  • U – Enable updates to Planning Analytics
  • S – Print static values instead of returning all values dynamically
  • R – Reverse the sign of numeric values
  • Q – Enable quiet mode (hides errors and warnings)
  • Z – Display zero values instead of blanks
  • L – Live evaluation of rows. Recommended for large formulas involving thousands of rows, to prevent the Property Storage Quota limit error
• solveOrder – Specifies the formula evaluation priority. If omitted, the default value 0 is 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 context filter element 'Gross Revenue'

=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

The MDX() function enables the creation of reports with dynamic rows and columns by providing MDX (Multidimensional Expressions). It allows for dynamic data retrieval from IBM Planning Analytics, generating rows and columns based on expressions rather than fixed cell references.

Syntax

=MDX(cubeName, rowElements, columnElements, contextFilters, options)

Arguments

• cubeName – The name of the IBM Planning Analytics cube from which to retrieve data.
• rowElements – MDX expressions that define the rows dynamically.
• columnElements – MDX expressions that define the columns dynamically or specify an element range.
• contextFilters – The selected cells that define context filters to refine the data selection.
• options – A set of flags that modify how data is retrieved and displayed. Multiple flags can be combined (e.g., "FSRCGI"). Available flags:
  • F – Retrieve formatted values
  • S – Print static values instead of returning all values dynamically
  • G – Group consecutive rows with the same element name, displaying it only once.
  • I – Indent element names based on hierarchy levels using leading spaces.
  • R – Include empty rows in the result
  • C – Include empty columns in the result
  • H – Hide the column headers
  • Z – Display zero values instead of blanks

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.

Syntax

=ATTR(dimensionName, elements, attrName, attributeName, solveOrder)

Arguments

• dimensionName – The name of the dimension. You can also specify an alternative hierarchy using the syntax dimName:hierName.
• elements – The selected cells representing the elements for which the attribute value should be retrieved.
• attributeName – The attribute name, such as "Caption", that defines which attribute value to return.
• options – A set of flags that modify how data is retrieved and displayed. Available flags:
  • S – Print static values instead of returning all values dynamically
• solveOrder – Specifies the formula evaluation priority. If omitted, the default value 0 is 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

The SUBSET() function retrieves dimension elements using the provided set expression.

Syntax

=SUBSET(dimName, setExpression, attrName, options, solveOrder)

Arguments

• dimName – The name of the dimension. You can also specify an alternative hierarchy using the syntax dimName:hierName.
• setExpression – A valid MDX expression, $SubsetName, or MDX mods.
• attrName – The attribute name, such as "Caption", that defines which attribute value to return.
• options – A set of flags that modify how data is retrieved and displayed. Available flags:
  • S – Print static values instead of returning all values dynamically
  • I – Indent element names based on hierarchy levels using leading spaces.
  • A – Expand above, display children elements above the consolidation
• solveOrder – Specifies the formula evaluation priority. If omitted, the default value 0 is used.

See =SUBSET() formula example

Set Expression

You can provide a valid MDX expression or use TeamOne subset syntax to define a set of elements. The following formats are supported:

• Semicolon-delimited element list – A list of elements separated by semicolons.
  • Example: Jan;Feb;Mar
• Pattern matching – Use wildcards to match elements dynamically:
  • * – Matches any sequence of characters.
  • ? – Matches any single character.
  • Example: /*substring*/ or /*FY202? */
• Subset name reference – Use a dollar sign ($) before a subset name.
  • Example: $My subset name
• Subset keywords – Predefined keywords for dynamic element selection.
  • $ALL - All dimension elements
  • $ROOTS - Top-level dimension elements
  • $LEAVES - Leaf-level elements

You can also combine elements, patterns, MDX expressions, subset keywords, and MDX mods in a semicolon-delimited list for advanced selections.

• Example:

  $roots>>expand;{[month].[Q1].children};/FY20??/>>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