Excel is still the tool of choice for TM1 users to create reports and forms. They can be opened directly in Perspectives or Planning Analytics for Excel or via the web in TM1 Web, or Planning Analytics Workspace. Excel remains a really easy to use, highly business friendly place for the creation of easy to use and understand reports. Here, in the first of our series on best practices for using Excel as the report writer for TM1, we are going to discuss the use of a Control Tab.
What is a Control Tab in a TM1 Excel Report or Form?
When you create a new Custom Report or Dynamic Report in PAX, or a Slice or Active Form in Perspectives, TM1 inserts some code at the top of your report. In this code, TM1 will place a reference to the TM1 system and cube that the report is created from. Then, if you use any TM1 Excel functions that refer to a cube or dimension directly, you need to enter the syntax of system:dimension or system:cube as an argument in the Excel function.
A Control Tab is a tab inserted by the report creator that is then hidden from the report user that contains references used throughout the rest fo the report in a central location. We use it to store variables for the TM1 system and cubes and dimensions used in TM1 formulae.
We also use the System Control tab for storing any lists that will be used in data validations on the Excel form or report and also sometimes for calculations or extractions of attributes for data like prior month or prior year.
Creating a Control Tab
So, instead of hard coding that information into each formula as required, we create a Control Tab inside the relevant Excel workbook. On this we create a set of parameters as follows:
- pSystem
- pCube1
- pCube2
- pCube3
- pDim1
- pDim2
- pDim3
We then use the create named range function in Excel to name the cells immediately to the right of the labels the same as the label.
Next is to create a set of additional labels and defined names for the concatenation of the pSystem parameter with each of the other parameters. We name these:
- pSysCube1
- pSysCube2
- pSysCube3
- pSysDim1
- pSysDim2
- pSysDim3
Finally, in the cells to the right of the new parameters, we concatenate pSystem with “:” and each of the others parameters to end up with the correct syntax for referring to it in any TM1 based formula. So, for example, for pSysCube1, the formula would be =pSystem&”:”&pCube1.
Using the Control Tab
We then have a set of ready to go named ranges for use in our TM1 formulae. Obviously the first cab off the rank is the TM1 formula entered at the top of your report or form. Just go and change it from “servername:cubename” to pSysCube1 and away you go.
This method is especially useful if you are migrating from one TM1 model to another, or from one server to another where the instance is different. This will allow you to migrate, go to the control tab and change one parameter and your report is then working.
