TM1Val

TM1Val is a hierarchy spreadsheet formula 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 Planning Analytics Engine data using tuple intersections that take hierarchies into consideration.

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.

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] Note: 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:

Connect to a data source in Planning Analytics for Microsoft Excel.
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.

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 will not generate an output if the server name in the TM1Val arguments is spelled incorrectly or references an invalid server.