Formulas ​

TEAMONE() function ​

TEAMONE() function is a simple formula approach to retrieve data from IBM Planning Analytics. It expects the following arguments:

• cubeName - the cube name to retrieve data from
• rangeRows - select the cells representing your rows
• rangeColumns - select the cells representing your columns
• rangeTitles - select the 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, "Z"ero values instead of blanks. You can combine flags: "AFSQZ" - fetch formatted static values, autofit columns and quite mode.
• solveOrder - specifies the formula evaluation order. If omitted, the default value of 0 will be used.

For rows and columns you can select a single or multiple cells, 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')

// 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')

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

// Multiply each value by 2
=ARRAYFORMULA(TEAMONE(B2,B10:C16,D8:G9,B5:B7)*2)

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

// Multiply each value by 2
=ARRAYFORMULA(TEAMONE(B2,B10:C16,D8:G9,B5:B7)*2)

See =TEAMONE() formula example

Autofit columns ​

Use the "A" flag in the TEAMONE() function to automatically resizes 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 skip this flag and apply formatting in Google Sheets.

Static values ​

When using the "S" flag, =TEAMONE() formula will return a single value (the very first one) and the rest of the values will be printed as static. This flag is recommended when you need to use 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 dimension (e.g., the product dimension in the example below):

Update mode ​

Use the "U" flag in the TEAMONE() function to enable data updates. The "U" flag also turns on the static values described above. You can submit the formulas 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 with 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, the same row\column dimensions and the same 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() or ATTR() functions as the arguments for another TEAMONE() or ATTR() function, you can specify the solveOrder to control the order in which those results will be recalculated. solveOrder must be 1 or greater. To use the results of the functions TEAMONE() or ATTR() as arguments for another TEAMONE() or ATTR() function, you can specify the solveOrder to control the order in which those results will be recalculated. The solveOrder value should be 1 or greater. If omitted, the default value of 0 will be used.

Default elements ​

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

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. This is especially helpful when an element name is ambiguous (exists in multiple dimensions).

Connection name ​

Use "ConnectionName:CubeName" syntax to retrieve data from the specified connection.

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

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

Create formulas automatically ​

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

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

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