VIEW TM1 Excel Syntax and Use
The VIEW function enhances the efficiency of a Planning Analytics for Excel Custom Reports or Dynamic Reports by establishing an optimised representation of a cube. This feature speeds up report retrieval, especially during slicing operations in Perspectives or when exporting a Custom Report in PAX. It achieves faster retrieval by establishing an optimised representation of the cube stored in memory.
The following syntax is used:
- TM1ServerName:cubename is The name of the cube.
- e1, e2[,…en] are the arguments provided to specify titles for slices can be either specific elements within the slice, referred to as dimension elements, or the string “!”.
- e1, e2 etc can refer to subsets from a dimension.
- When you use the string “!” as an argument, it indicates that the corresponding dimension is in a row or column in the view. Furthermore, instead of specifying specific dimension elements, you can use aliases as arguments to represent them.
Assume we have a cube named “Sales” with the following dimensions:
- Dimension 1: Product (elements: Product A, Product B, Product C)
- Dimension 2: Region (elements: Region X, Region Y, Region Z)
- Dimension 3: Time (elements: January, February, March)
Let’s say we want to create a view of the “Sales” cube, where the rows contain the Product dimension, and the columns contain the Month dimension. We’ll include specific elements as titles for the slice.
VIEW ("NebulaFit:Sales", "!", "Sydney", "!")
In this example, we are using the string “!” as arguments for the elements in the slice for the Product and Month dimension and “Sydney” for the Region dimension.
The resulting view will display all the available data in the “Sales” cube, with the Product dimension representing the rows and the Time dimension representing the columns. The view will be filtered on the Sydney region.
You can also modify the example as below to specify specific elements or subsets in the slice to be used as titles:. For example:
VIEW ("Sales", "Product A", "All Regions", "January")
In this updated example, we are using specific elements or subsets from the Product, Region and Month dimensions as arguments. The resulting view will contain data for the selected elements and subset, with Product A, All Regions and January included.
Example of the use of VIEW in TM1 and Planning Analytics.
Note re View in Dynamic Reports
When you create a Dynamic Report, the View function is inserted in the report. Each dimension is referred to in the view and will usually point to a SUBNM with a defined subset. If you subsequently modify the filter, say by adding a ” YTD” on the back of a SUBNM on a Month dimension, and the YTD elements are not in the subset, your Dynamic Report might fail to return values when presented via TM1Web or the Websheets viewer in PAW.