Appearance

Formulas ​

TEAMONE() function ​

TEAMONE() function is a simple formula approach to retrieve data from IBM Planning Analytics. It expects the next 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.

img

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.

img

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.

img

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.

img

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.

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.

img

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

img

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:

img