Reports against TM1 - Question

Post Reply
Reuben
Posts: 8
Joined: Thu Mar 22, 2018 1:04 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2013

Reports against TM1 - Question

Post by Reuben »

Gurus,
I have a question on Cognos BI reporting against TM1.

The report I am planning to write has 4 columns - Account No, Actual, Budget and Variance.
The Column Variance can be either (Actual - Budget) OR (Budget - Actual) depending on the Element Attribue of the Account No.
There is a element attribute in the Account Dimension which specifies the account no as either Revenue or Expense.

If Account Attribute is Revenue then the report should calculate Actual - Budget
If Account Attribute is Expense then the report should calculate Budget - Actual

I would like to make use to this element attribute to do this calculation

So basically I am looking for an "if then else clause" which I have heard is not possible in dimensional reporting.

Thanks in advance
babytiger
Posts: 78
Joined: Wed Jul 31, 2013 4:32 am
OLAP Product: Cognos TM1, EP, Analyst
Version: 10.2.2
Excel Version: 2013
Location: Sydney AU

Re: Reports against TM1 - Question

Post by babytiger »

This is a fairly common scenario for a TM1 reporting cube, whether is reported from BI or other reporting tools such as Perspectives.

Here is what I would do:
  1. All calculations is done in TM1 cube.
  2. Introduce another Account Attribute [1, -1] in TM1 that refers to the Revenue/Expenses attribute. This is to make it easier for rules, and it can be useful if you have BS accounts in the account dim. This new attribute can be ruled or static values updated via a TI. TI is probably better, for better performance.
  3. For Variance element, it can be a consolidated element or a level 0. And add tm1 cube rule to calculate variance and multiple by the new numeric attribute above. I personally like consolidation, because it would eliminate the need of feeders, but the downside is you can not use TI to update this variance. In the case of a level 0 variance element, you can have another TI to update the variance (overnight or on demand), which may give you better performance. It depends on the size of your dataset/cube, etc. You can still use a rule for level 0 variance element, but will require to specify the feeder.
Hope this helps.
MK
Reuben
Posts: 8
Joined: Thu Mar 22, 2018 1:04 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2013

Re: Reports against TM1 - Question

Post by Reuben »

Thank you very much. I generally try to do everything from the reporting side.
I will try this out and see.
Thanks again...
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Reports against TM1 - Question

Post by paulsimon »

Hi Reuben

I would check your data first. It it is a typical TB then Income will be negative and Expenses positive. Therefore Budget - Actual will always give you what you want eg

Act Bud Var B-A
Inc X -4 -5 -1 Income below Budget so negative
Inc Y -3 -2 1 Income above Budget so positive
Exp X 2 1 -1 Expenses over Budget so negative
Exp Y 1 3 2 Expenses below Budget so positive

It often depends on who you are presenting to, but it will typically be accountants and they are used to negative income and negative being a profit. I have often had debates as to whether we should reverse the sign on the I&E side and leave it on the BS side, but after much discussion people normally decide to keep the standard double entry book keeping standards.

Regards

Paul Simon
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Reports against TM1 - Question

Post by paulsimon »

Hi Reuben

By the way if you do still need the rule approach then I would suggest that you still use consolidation ie Variance as a parent of Budget and Actual, but then you override the Variance with a C: consolidated level rule. The advantage of this is that you don't need to feed the variance. I use a more generic method that lets me quickly add just about any variance. For each variance I just have attributes to define which is the positive and which is the negative child of the variance

Regards

Paul Simon
Post Reply