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 section we’ll learn formatting cells in TM1.

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

You should have created a cube with subsequent dimension items, apply the correct rules for Sales and Sale Price and input the data shown in figure below:

Page 1 Image 1 46 — Sales and Sale Price

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

You can apply formats at 2 levels in TM1

Cube level
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 parentheses.

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 cannot be in decimal values. There cannot 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…’

Page 3 Image 4 25 — 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 employed 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:

Dimension format takes precedence over cube format
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.
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.
The formatting is applied only for numeric values. You cannot format string values.

Assignment

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.

So, this brings us to the end of blog. This Tecklearn ‘How to Format Cells in Cognos TM1’ blog helps you with commonly asked questions if you are looking out for a job in Cognos TM1. If you wish to learn Cognos TM1 and build a career in Business Intelligence domain, then check out our interactive, Cognos TM1 Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/cognos-tm1/

Cognos TM1 Training

About the Course

Tecklearn’s Cognos TM1 training will make you proficient in building and configuring models in Cognos TM1 using performance modeler tool. Some of the topics included in this Cognos TM1 training are creation and configuration of TM1 servers, building Cubes and Views, creating dimensions, deploying Turbo Integrator for loading and maintaining data and transferring data using scripts. etc., in working with real-time projects, and this will enable you to become a certified Cognos TM1 developer.

Why Should you take Cognos TM1 Training?

The Average salary for a Cognos TM1 Developer is $117,816 per annum – ZipRecruiter.com.
Cognos TM1 has a market share of about 11.87%.
Solution that is owned by Financial and Business analysts, with limited IT involvement, but consistent with IT’s governance.

Curriculum

Introduction to Cognos TM1

Concepts of Multi-dimensional OLAP database
Cognos TM1 Installation
TM1 Servers and Services
TM1 Workflow
TM1 User Interfaces
Turbo Integrator

Cognos TM1 Configuration

Managing TM1 Components and Tasks
Operations on TM1 Servers: Start, Stop and adding TM1 Servers
TM1 Web, Applications and Operations Console
Using Command prompt create or delete Desktop/Windows services
Hands On

Creating Dimensions

Manual creation of Dimensions
Create Product Dimensions and Measure Dimensions
Dynamic Subset Creation
Sparse and Dense Dimensions
Using Turbo Integrator ETL create cubes, review Cubes and Dimensions
Create Subset, assign Subset, Dimensional Manipulation
Performance Monitoring
Control Cubes
TM1 Web and Rules in TM1
Cube related attributes, dimension related attributes
Hands On

Building Cubes and Views

Building Cubes
Building Views
Bulk Load Mode
Creating Data Source through TM1 Turbo Integrator
Hands On

TM1 Security

Various security levels including Server level, Cube level, Dimension level
Concepts of Security in TM1
Creation of Client & Group and access privileges
Cube Level Security
Hands On

TM1 Data Spreading

TM1 Data Spreading and Recreating dimension
Attributes element types
Cube control elements
Dimension elements
Drill Assignment rules
Trace a feeder and feed strings
Hands On

Cognos TM1 Rules

Understanding TM1 rules
TM1 Rules Editor and Rules compilation using functions
Rule performance optimization
Element information rules functions
Cube manipulation Turbo Integrator functions
D-type in TM1
ELCOMP & ELISCOMP Functions
ELISPAR & ELWEIGHT Functions
Hands On

Advanced Scripting with Turbo Integrator

Advanced Scripting with Turbo Integrator
Deploying Dimension subsets and attributes
Editing procedures using scripts
Hands On

Advanced Modelling Techniques

Significance of advanced modelling
Manually adding objects to application design
Understanding application views, dimensions and hierarchy
Virtual cubes, Lookup cubes and spread profile cube
Hands On

TM1 Application Web

TM1 Web Applications
Creating TM1 Application
Working with TM1 Application Web
Snapshots in TM1 application
Adding MDX filters and Action button properties
Active forms, TM1 user and data formatting range
Hands On

636