Dynamically updated reports
- Jan. 22, 2012
Introduction
Many companies use TM1 for the monthly financial closing. As we strive to use dynamic and self contained (Excel) reports, we will see how we can use lookup cubes and aliases to help us.
Lookup cubes
Excel reports can be set up to show the current month and year using a DBRW formula. It suffices to use a lookup cube with very short and simple dimensions:
Looking at the illustration above, we have a simple two-dimensional cube for parameters and their values. We foresee the possibility for input of both numeric values and text values. A DBRW function in Excel worksheets or TM1 Web could show the current values for the parameters, or receive input (a DBRW can be used to change a value in a cube too):
The user (with sufficient rights) could enter the new value every month. Important is to have existing elements for years and months: other Excel reports or TM1 Web reports can retrieve data from any cube based on the month/year parameters. The advantage is that we only need to change the month indicator in the lookup cube instead of tens or hundreds of custom reports. To add to the importance of this last point, I note that using picklists is a good way to make sure the user picks a valid element for months and years. If the user would enter '2012' instead of 'Y2012' (as the year element is called), DBRW formulas will return *KEY_ERR errors in the cells.
Aliases
As only 1 month is the current month and only 1 year is the current year, why would we use a lookup cube? An alias will suffice (I agree, aliases are stored in a lookup / control cube, but there is a subtle difference in my opinion). Or maybe an even better example would be Version or Scenario: we have several budget rounds, but only 1 round is the current version and needs to be reported on. How do we do this in a simple and straightforward way?
The Attributes Editor above shows that I added an alias to the Years dimension. Specifically, I set the alias CURRENT_YEAR on the correct year. As with lookup cubes, Excel sheets (and TM1 rules and Turbo Integrator code) can employ the alias in the calculations for input, reporting, data checks, …. Insert "CURRENT_YEAR" as the "element" for the Years dimension. Use a DBRA formula to work with aliases, or the DIMNM(…,DIMIX(…,…)) idiom.
Updating the values
Cells in a lookup cube can be changed in a number of ways, one of which is through a DBRW formula. See above. Alias are stored in cubes too, thus the way to update them is similar. Furthermore, the Attributes Editor in the image we saw is an option too.
With TI code, updating the aliases and lookup cube is possible in a non-manual way. To write to a cube cell, use the CELLPUTN and CELLPUTS formulae in TI for numbers and text, respectively. As this is rather trivial, let us move on to the alias update in TI:
# Wim Gielis # https://www.wimgielis.com##### # Shifting an alias value in TI from one element to another # 01/22/12 #####IF(DIMIX('Months','CURRENT_MONTH')>0); sPrevMonth=DIMNM('Months',DIMIX('Months','CURRENT_MONTH')); AttrPutS(sPrevMonth,'Months',sPrevMonth,'Current'); ENDIF; ATTRPUTS('CURRENT_MONTH','Months',pMonth,'Current');
Here is what happens when this TI code will shift the alias CURRENT_MONTH from P12 (December) to P01 (January) in the Months dimension:
- See if the alias value was set (line 1 in the code): the DIMIX should be strictly positive
- Determine on what month the alias was set (line 2 in the code): P12 will be the result in the variable sPrevMonth
- Set the value of P12 as the alias to itself (line 3 in the code): this effectively removes the alias
- Set the alias value 'CURRENT_MONTH' on the new current month P01 (line 5 in the code): see below.
Very often, data (actuals) are loaded on a monthly basis using TI. This TI process will ask the the user to input the year and month to load values to with parameters. That parameter (call it pMonth with P01) will be the new current month and receive the alias. Excel and other reports now instantly show the data for January!
Repeat this method for years and versions if you want to cut down maintenance time on reports.
