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 represeting your rows
• rangeColumns - select the cells represeting your columns
• rangeTitles - select the cells represeting 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. You can combine flags: "AFSQ" - fetch formatted static values, autofit columns and quite mode.
• solveOrder - specifies the formula evaluation order. If omitted, the default value of 1 will be used.

For rows and columns you can select multiple cells, a single cell or even no cells (use an empty string or leave the argument empty):

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

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. However, in most cases, you will not need to use this flag.

Update mode ​

Use the "U" flag in TEAMONE() 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.

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 1 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 represeting 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 1 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.