IBM Planning Analytics Functions for Excel

Excel (IBM reference)
Excel Macro (IBM reference)
Rule (IBM reference)
TurboIntegrator (TI) (IBM reference)
MDX (IBM reference)

ABS

Converts a number into an absolute value.

Rules

TI

ACOS

Returns the angle, in radians, whose cosine is x. 

Rules

TI

DBR

Retrieves and writes back a value or text to a cube’s cell. DBR is less efficient over a WAN than DBRW. DBR will be processed before a DBRW.

Excel

DBRA

Used to retrieve and write back a value or text to a dimension’s attribute.

Excel

DBRW

Provides read and write back capability to Video from a Cube.

Excel

DBS

Used to send a value to a cube’s cell. This function cannot send a string to a cube. To send strings, use the DBSS function.

Excel

DBSA

Used to send a value to a dimension attribute. The value sent can be either a string or numeric value, based on the attribute type.

Excel

DBSS

Used to send text to a cube’s cell.

Excel

DBSW

Used to send text to a cube’s cell.

Excel

DFRST

Returns the first element in a dimension.

Excel

DIMIX

Returns an element’s index number in a dimension.

Excel

DIMNM

Returns an element’s primary name based on an index in a dimension.

Excel

DIMSIZ

Returns the number of elements in a dimension.

Excel

DNEXT

Returns the next element based on the index in a dimension.

Excel

DNLEV

Returns the number of levels in a dimension.

Excel

DTYPE

Returns the element type in a dimension.
N = numeric element; C = consolidated element; S = string element

Excel

ELCOMP

Returns the name of a child element from a consolidated element based on the position.

Excel

ELCOMPN

Returns the number of components under an element.

Excel

ELISCOMP

Determines whether an element is a component of a parent element in a dimension.

Excel

ELISPAR

Determine whether an element is a parent of a child in a dimension.

Excel

ELLEV

Returns element’s level in the dimension.

Excel

ELPAR

Returns the parent of an element in a dimension.

Excel

ELPARN

Return the number of parents for an element.

Excel

ELSLEN

Return the number of characters in the element in a dimension. Member ID and alias can be used.

Excel

ELWEIGHT

Returns the element’s weight to a consolidated element in a dimension.

Excel

SUBNM

Return an element from a dimension set. The formula transforms the cell into a drop down and can open to the set by double-clicking on the cell.

Excel

TABDIM

Returns the dimension name in a cube based on an index.

Excel

TM1ELLIST

Returns elements based on a list of elements, MDX or set.

Excel

TM1GLOBALSANDBOX

Returns the active sandbox for the user. Only available in IBM Planning Analytics for Video and Worksapce.

Excel

TM1INFO

Return information on the TM1 client. Only supported in Planning Analytics for Video.

Excel

TM1PRIMARYDBNAME

Returns the primary TM1 server name the user is authenticated in.

Excel

TM1RPTELISCONSOLIDATED

Returns a boolean value if an element is consolidated. Used for Dynamic Reports (Active Form).

Excel

TM1RPTELISEXPANDED

Returns a boolean value if an element is expanded on the row set.
Used for Dynamic Reports (Active Form).

Excel

TM1RPTELLEV

Returns an element’s level relative to the root in the subset.
Used for Dynamic Reports (Active Form).

Excel

TM1RPTFILTER

Apply a filter to the Dynamic report to sort the row based on a column
Used for Dynamic Reports (Active Form).

Excel

TM1RPTROW

Returns the row set in a Dynamic Report. Use MDX, a set/subset or hardcode a list of elements in Video to return members to the row.

Excel

TM1RPTTITLE

Used to define the title dimension. TM1RPTTITLE is used in conjunction with TM1RptView.
Used for Dynamic Reports (Active Form).

Excel

TM1RPTVIEW

Controls the definition of the report like zero suppression and the format area.
Used for Dynamic Reports (Active Form).

Excel

TM1USER

Returns the user logged in.

Excel

VIEW

Sets up an optimised view for a cube that is cached in memory, so it can be referenced quicker.

Excel

Stay in the loop

Keep yourself informed on the latest news in the analytics space by following us on LinkedIn.

For any enquires, email us at edu@cubewise.com or contact an office near you (link).