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
Reports against TM1 - Question
-
- 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
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:
Here is what I would do:
- All calculations is done in TM1 cube.
- 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.
- 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.
MK
-
- Posts: 8
- Joined: Thu Mar 22, 2018 1:04 pm
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2013
Re: Reports against TM1 - Question
Thank you very much. I generally try to do everything from the reporting side.
I will try this out and see.
Thanks again...
I will try this out and see.
Thanks again...
- 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
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
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
- 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
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
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