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.
Syntax
=TM1Val("datasource uri”, “server”, “cube", 1, 945730358, "[dim1].[hier1].[elem1]","[dim2].[hier2].[elem2]","[dim3].[hier3].[elem3]","[dim4].[hier4].[elem4]","[dim5].[hier5].[elem5]")
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
- 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