7. Formatting Cells in TM1

Introduction to Formatting Cells in TM1

Sometimes you may need to format your data according to your client requirements. For example:

Show your numeric value up to 3 decimal points
Show the date values in MM-DD-YYYY format
Show the % values followed by a %
Show negative values in ()
Etc.

So in this tutorial we’ll learn formatting cells in TM1.

Prerequisites

If you just want to learn the concept (instead of following the tutorial step by step, as shown in the screenshots), Just create some cube and input some decimal values in it. However

If you want to follow this tutorial step by step you must do one of the two following things:
1. You should have completed last tutorial (5. Loading Data in TM1 Cubes)
or
2. You should have created a cube with following dimension items, apply the correct rules for Sales and Sale Price and input the data shown in figure below:

 

So we’ll learn how you can apply formatting using TM1 formats.

You can apply formats at 2 levels in TM1

1. Cube level
2. Dimension level

 

Cube Level Formatting

Open Products2 cube (or the one in which you have decimal values) and go to Options > Format …

 

You’ll see various formats such as Comma, Currency, Date, Time, Percentage etc. Most of these are self explanatory. For now we’ll use decimal values up to 2 decimal points. Go to the third format, Comma, and select Precision as 2, also select the checkbox Show negative values in parantheses.

 

Click OK. You’ll notice that all the values of Quantity, Sale price and Sales have become decimal values including the ones which had more than 2 decimal points. What if I don’t want my quantities to be decimal. For example bottles of Pepsi can not be in decimal values. There can not be 1.4 bottles of pepsi, either 1 bottle will be sold or 2 or more or none. No decimal values.

So I’ll have to change decimal quantity values to whole numbers. I’ll do that by learning next type of formatting: dimension formatting.

 

Dimension Level Formatting

Go to the dimensions of the cube in Architect. Right click the last dimension of the cube (‘Measure’ dimension in this case) and click ‘Edit element attributes…’

 

The attribute editor opens up. Select the Format column of Quantity here and click on ‘Format…’.

 

You’ll see that the same format box launches which we used in last formatting. Apply the comma format and set the precision to 0 because we don’t require the decimal values. Click OK. The formatting text will appear in the format column.

From the next time, if I want to apply this format to any other items, I can just copy this column’s text and paste it elsewhere. You can try it for sale price. Copy the Quantity formatting text and copy it in sale price column. Press OK.

Press F9 in the cube view and you’ll see that the formatting has been applied.

 

Now about applying formats in TM1, a few…

Things to Remember :

1. Dimension format takes precedence over cube format

2. You can specify formats in all dimensions but while applying the format, only the last dimension’s (last dimension in the cube order) formatting is applied to the cells. If you have not specified the formatting in the last dimension, the second to last dimension’s formatting is taken, and so on until a formatting is found. If no formatting is found in any of the dimensions, cube level formatting is applied.

3. However, if you’ve applied a formatting in a certain dimension and that dimension exists in columns, its formatting will take precedence over any other formatting.

4. The formatting is applied only for numeric values. You can not format string values.

 

 

Homework

Revert the formatting of Sale Price dimension item. Apply a formatting to Sales values so that if a sales value is 999, it shows $ 999.00.