New TM1Val hierarchy spreadsheet formula

TM1Val is a new hierarchy-aware spreadsheet formula available in IBM® Planning Analytics TM1® Web.

This formula allows for cell by cell control of TM1 or IBM TM1 Database 12 data using tuple intersections that take hierarchies into consideration.

This feature requires TM1 Web 2.0.92 to work.

Note: TM1Val currently only supports a low-workload count.

Syntax

=TM1Val("datasource uri”, “server”, “cube", 1, 945730358, "[dim1].[hier1].[elem1]","[dim2].[hier2].[elem2]","[dim3].[hier3].[elem3]","[dim4].[hier4].[elem4]","[dim5].[hier5].[elem5]")
Table 1.
Argument Purpose Optional or Required
Host

Enter the data source URI.

The host argument allows for simultaneous use of multiple systems, even when database names would otherwise collide.

Required

Server

Database name

Required

Cube

TM1 cube name

Required

Mode

Operating mode for the function

“Read” or 0 - read value from the cube cell

“Write” or 1 - attempts to write the given writeValue, and returns the current value of the cell post-write attempt

“Clear” or 2 - clears the value of the cube cell, and returns the current value of the cell post-clear attempt

Required

WriteValue

Only consumed in mode 1 operation

Optional

M1,...Mn

Dimension member names that define the intersection of the cube containing the value to be retrieved.

[dim].[hier].[elem]

Optional

if there are no dimensions, use the default hierarchy and default member for each dimension in that cube

Example

=TM1VAL("http://mydatasource.ibm.com",
              "Planning Sample",
              "plan_BudgetPlan",
		1,
		945730358,
		"[plan_version].[plan_version].[FY 2004 Budget]",
		"[plan_business_unit].[plan_business_unit].[Total Business Unit]",
		"[plan_department].[plan_department].[Total Organization]",
		"[plan_chart_of_accounts].[plan_chart_of_accounts].[Revenue]",
		"[plan_exchange_rates].[plan_exchange_rates].[actual]",
		"[plan_source].[plan_source].[Goal]",
		"[plan_time].[plan_time].[2004]")

TM1Val Errors

The TM1Val formula output generates #NUM if the formula cannot be evaluated due to the any of these conditions:
  • There is an incomplete or non-existent dimension member name
  • A user does not have access to the dimension or hierarchy
  • The ODATA query returns a server error

The TM1Val formula output generates #VALUE if there are missing or invalid arguments.

The TM1Val formula output generates #N/A if the server disconnects, or if the user is not logged in.

The TM1Val formula will not generate an output if the server name in the TM1Val arguments is spelled incorrectly or references an invalid server.

For more information about TM1Val, see: TM1Val