Formatting elements
- Apr. 04, 2014
Introduction
Data in a cube can be formatted (obviously, I would say). Although there are no changes in recent TM1 versions, I would like to explore a bit the possibilities that we have to format data in the cube.
There is the possibility to format all values in a cube in one and the same way. It can be done in the Cubeviewer and is quick. Yet, here, you make the assumption that all data in the view should be formatted in the same way: all dollar/euro values, all percentages, all Dates and times in the cubeview, and so on. While this may be the case sometimes, I honestly do not use this formatting a lot.
Formatting usually occurs by dimension element. Here are the methods, although basically they all boil down to getting the correct formatting string in the relevant }ElementAttributes cube. This is because formatting is considered an atribute in TM1 (next to aliases, text and numeric attributes).
1. Manual approach
Right-click on the dimension name, bring up the Edit Element Attributes… dialog and set the correct string in the Format attribute. Don’t panic, as in Excel, you can pick a format from a list and you can adjust it if needed. Note the little square in (most of) the formatting strings: it is a so called Form Feed character with Ascii code 12.
2. Turbo Integrator
A statement in TI to update the Format attribute would be as follows:
# Wim Gielis # https://www.wimgielis.com##### # Setting element attributes # 04/04/14 #####cFormattingNumeric = 'b:#,##0' | Char(12) | 'CO|0|'; AttrPutS( cFormattingNumeric, 'Dim2', 'C element', 'Format');
For instance, during a TI process/chore that updates dimension elements, you can set its formatting right after adding the element to the dimension. The advantage is that the specific formatting string can be stored centrally in a parameters cube, or stored only once in the TI process (the Prolog section). Different levels in the dimension could easily get different formatting. In the TI code shown here, you will not be surprised to see Char(12) in there.
3. Business rules
Another option (as usual) is business rules, rules set on the }EementAttributes cube for the Format element:
# Wim Gielis # https://www.wimgielis.com##### # Setting element attributes # 04/04/14 #####['Format'] = S: If( Ellev( 'Dim2', !Dim2) = 0, 'b:#,##0.00;(#,##0.00)' | Char(12) | 'CO|2|Y', 'b:#,##0;(#,##0)' | Char(12) | 'CO|0|Y');
Hide 0 with custom formatting
Custom formatting of elements can be done as follows, where 0 values are hidden and we are not distracted by decimal values: b:#,##0;(#,##0);If you prefer the decimals, I can offer you: b:#,##0.00;(#,##0.00);
Custom date and time formatting
This could be done, for example in TI, as: AttrPutS( 'b:dd-mm-yyyy hh:nn:ss' | Char(12) | 'D|3|', 'dimension', 'element', 'Format' );. You might need to add 21916 as the number of days between the start of the years 1900 and 1960.Adding dynamic aspects
Again, that Form Feed character has been taken care of in the rules. The nice advantage about rules (and TI), is that they are dynamic (I’m not telling any news here!) But, this means that the formatting can be set in a central cube, and rules (just as TI processes) can refer to that formatting string while updating the formatted values. At the customer side, I never made the formatting attributes dynamic using rules. I have used method 2 above a number of times in the past, with good success.
Alternatives with the }ElementAttributes cube
Method 1 can be done straight in the }ElementAttributes cube. As TM1 stores the attributes in a control cube called }ElementAttributes_Name of the dimension, that is only logical. But it also means that a CellPutS statement in that cube works too: it is an alternative to the AttrPutN function. Method 3 was already illustrated on the attributes cube.
See you later for more TM1 bits!
