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 widthF
– Retrieve formatted valuesU
– Enable updates to Planning AnalyticsS
– Use static valuesR
– Reverse the sign of numeric valuesQ
– Enable quiet mode (hides errors and warnings)Z
– Display zero values instead of blanks
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 valuesS
– Use static values instead of live formulasG
– 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 resultC
– Include empty columns in the resultH
– Hide the column headersZ
– 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 syntaxdimName: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
– Retrieve static values instead of live formulas.
solveOrder
– Specifies the formula evaluation priority. If omitted, the default value 0 is used.
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 syntaxdimName: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
– Retrieve static values instead of live formulas.
solveOrder
– Specifies the formula evaluation priority. If omitted, the default value 0 is used.
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
- Example:
- 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
- Example:
- 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:text
$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.