Excel Functions

TM1 Function – VIEW

Available In

Excel

Definition

VIEW sets up an optimised view for a cube that is cached in memory, so it can be referenced quicker. When Slicing a view in Perspectives or exporting to a Custom Report in PAX, the VIEW function is used to optimise the report.

In the VIEW function, title dimension is referenced to an element, while the dimensions in the row and column axis are defined as “!” to include all elements.

Syntax

VIEW(TM1ServerName:Cube, Element 1, Element 2,… , , “!”,”!”, Element n)

TM1ServerName = Name of the TM1 Server
Cube = Name of the Cube
Element 1 and Element 2 = Name of the title dimension elements
“!” = refers to row or column dimensions to include all elements
Note: the number of elements must be the same as the number of elements in the cube and the arrangement must be in order.

Example

Export the default view from the Product Scorecard cube as a Custom Report and show how the VIEW function is put together.

Opening the default view as a Custom Report.

Unhide the top rows to unveil the VIEW formula.

Location of the VIEW formula.

VIEW(“AdventureWorks:Product Scorecard”,”!”,$B$2,$B$3,”!”,$B$4,”!”)

TM1 server name = AdventureWorks
Cube = Product Scorecard
1st Dimension (“!”) = all elements from the Version dimension (Column set)
2nd Dimension ($B$2) = refers to the element ‘2020’ from the Year dimension
3rd Dimension ($B$3) = refers to the element ‘Q1’ from the Quarter dimension
4th Dimension (“!”) = all elements from the Product Type dimension (Row set)
5th Dimension ($B$4) = refers to the element ‘Sales Amount’ from the Product Scorecard dimension
6th Dimension (“!”) = all elements from the Product Scorecard Measure dimension (Row set)

Video Demo