TM1Val

TM1Val is a hierarchy spreadsheet formula that is available in IBM® Planning Analytics TM1® Web and IBM Planning Analytics for Microsoft Excel.

TM1Val is a hierarchy-aware writeback formula. This formula allows for cell-by-cell control of TM1 or IBM TM1 Database 12 data by using tuple intersections that take hierarchies into consideration. TM1Val currently only supports a low-workload count.

Note: TM1Val requires Planning Analytics for Microsoft Excel 2.0.92 and TM1 Web 2.0.92 to work. Planning Analytics on Cloud customers must request that their TM1 Server be upgraded to 2.0.9.19 IF1 or higher for TM1 Web 2.0.92 to be deployed on cloud.

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. TM1Val arguments
Argument Purpose Optional or Required
Host

In the Host argument, enter the data source URI.

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

Required
Server

In the Server argument, input the Database name

Required
Cube

In the Cube argument, input the TM1 cube name

Required
Mode

The Mode argument is the operating mode for the function. The following values can be used to set the operating mode:

  • “Read” or 0 reads 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

The WriteValue is only consumed in mode 1 operation.

Optional
M1,...Mn

These are the dimension member names that define the intersection of the cube containing the value to be retrieved.

[dim].[hier].[elem]

There is a limit of 64 dimension member names that can be added in TM1Val for Planning Analytics for Microsoft Excel.

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

Embedding a TM1Val function

Note: This feature is only available for Planning Analytics for Microsoft Excel.
The TM1Val function can be added to your report from Planning Analytics for Microsoft Excel:
  1. Connect to a data source in Planning Analytics for Microsoft Excel.
  2. Select a cell and enter the TM1Val formula.
    Note: For guidance on arguments, type =TM1VAL() in a cell and press Enter. Click Insert function to see all the arguments that you need to enter for TM1VAL. The following image shows the function arguments that can be inputted:
    TM1Val function arguments

TM1Val Errors

The TM1Val formula output generates #NUM if the formula cannot be evaluated due to the following:
  • 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 does not generate an output if the server name in the TM1Val arguments is spelled incorrectly or references an invalid server.