on 08-23-2010 10:56 AM
I am using BPC for legal consolidations and have come across a question on the ACCTYPE property. Here on the forum I have found an explanation saying that
ACCTYPE INC is saved with the opposite sign to the database and aggregated to the OLAP cube
ACCTYPE LEQ is saved with the opposite sign to the database and not aggregated to the OLAP cube
ACCTYPE EXP is saved as entered to the database and aggregated to the OLAP cube
ACCTYPE AST is saved as entered to the database and aggregated to the OLAP cube
The questions I have are:
1) Aggregation
Our application is set as a YTD application, so I assume that INC and EXP are NOT aggregated, is that correct?
2) system usage of ACCTYPE
Where is the ACCTYPE actually used (apart from when saving data to the database and displaying data in Excel) by the system or, in other words, would I be able to build an application with only accounts of ACCTYPE EXP and AST? As this is for a legal consolidation we do make us of currency conversion and other business rules.
Thanks
Hi Arnold,
1. Your understanding is correct.
2. Technically speaking, there is no limitation. You can definitely have only EXP and AST. However, if we look at it functionally, Asset should be equal to liability. This means that if you have AST, then you should have LEQ as well.
Hope this helps.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
it does help. The reason why I want to use only EXP and AST accounts is the following.
When entering data we want to enter income as negative and expenses as positive. If we does this in an application with the correct usage of INC and EXP the roll-up into the profit for the year doesn't work.
Equally, when entering data we want to enter assets as positive and liabilities/equity as negative. We also want an account that shows the sum of the balance sheet as zero when it balances. If we use AST and LEQ, this summation account shows the wrong value.
Hi Arnold,
1. I didnt understand the reson behind entering the INC as negative. Usually, lets take the below scenario
Income = 300
Expense = 100
In the DB,
INC = -300
EXP = 100
Profit = -200
Profit should be maintained as INC. So, while reporting Profit will be displayed as 200, which is absolutely right (considering the original example).
However, if you want to enter the income as negative values, then you need to change the measure formula maintained in your database.
Hope this helps.
Hi Arnold,
If you are entering the values as shown by you, then in the database,
Income = 300
Expense = 100
Profit = 400
While displaying, it will display the profit as -400 (if profit's acctype is INC).
This is the reason, i requested you to change the measure formula. By default, whenever an account is of type INC or AST, it is multiplied by -1. So, if you remove this and multiply the amount only by 1 (instead of -1), then your databse will have
Income = -300
Expense = 100
Profit = -200
The formula needs to be changed in the measureformula table in your appset in SQL.
Hope this helps.
Hi Arnold,
The measure formulae are specific to a particular appset. formulae of Appset A has no relation with formulae of Appset B.
Having said this, in the later versions, if you create a new appset by copying Appshell, then you will have the default formulae (since appshell is designed like that). However, if you take a backup of your custom appset and then restore it, then the measure formulae will remain as you have designed.
Hope this helps.
Hi,
in the measureformula table I have four entries that seem to deal with factors, two with the name FINSIGN and two with the name LOADSIGN.
The FINSIGN ones list account types INC and AST, the LOADSIGN ones list account types INC and LEQ.
I have changed all references to -1 so that they now read 1 but when I enter 100 on a LEQ account the factwblegal table still shows -100 in SQL.
are there any other steps I have to do before this works?
Edited by: ArnoldWarhonowicz on Sep 9, 2010 12:01 PM
So as I am only using YTD, I can change only YTD measure formulas. But there are two of them.
IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",SUM(YTD(),-MEASURES.[SIGNEDDATA]),IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",SUM(YTD(),MEASURES.[SIGNEDDATA]),IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%])),IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%])),-MEASURES.[SIGNEDDATA]))))
and
IIF(INSTR("INCLEQ",[%ACCOUNTDIM%].PROPERTIES("ACCTYPE")), -1 ,1)*([MEASURES].[SIGNEDDATA],CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))
do I need to change both? I don't quite understand the code itself.
Hi Arnold,
Yes, both the formulas are for YTD measure. One formula is applicable, when your application is a periodic type application. Another formula is applicable when your application is a YTD type application. You can see in both the formulas that when ACCTYPE is INC or LEQ, the signeddata is multiplied by -1 and when ACCTYPE is EXP or AST, signeddata is multiplied by 1. You need to make changes accordingly.
Hope this helps.
I have changed both YTD entries, as my application is YTD I would have thought that this is the one that applies though:
SEQ 200
NAME YTD
STATEMENT
IIF(INSTR("INCLEQ",[%ACCOUNTDIM%].PROPERTIES("ACCTYPE")), 1 ,1)*([MEASURES].[SIGNEDDATA],CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))
SOLVEORDER 3
DESCRIPTION YTD
IS VISIBLE 1
IS YTD APP 1
IS SYSTEM 1
yet, when I enter data onto a LEQ account as a positive value, the database will contain a negative value.
Hi Arnold,
You need to check few things?
1. Is your application YTD type?
2. What is the measure you are using while reporting?
Now, go to the table measureformula. The answer to the first question is the column "IsYTDApp". The answer to the second question is the column "NAME". Now, you can change the formula for this combination, accordingly.
Hope this helps.
My app ist YTD and I am using the YTD measure for data entry which is where I am testing.
So, the change described above, as far as I can see should be the correct one.
ISYTDAPP is 1 which should be true
and NAME is YTD which should be the correct measure.
Yet, when I enter data onto a LEQ account, it is still saved in the DB with the opposite sign.
Hi,
I think I am missing something.
The BPC standard is to enter all values as positive values and the system will save values on INC and LEQ accounts as negative values in the database.
So, if I enter (I work in a YTD application and use the YTD measure, so I enter the cumulative YTD value and then ask for it back in a report)
INC 1000
EXP 500
AST 1000
LEQ 500
the system will save
INC -1000
EXP 500
AST 1000
LEQ -500
As the system will also transfer the -500 from the P&L to the LEQ side of the BS, my balance sheet does balance.
This behaviour is driven by the dbo.tblmeasureformula table. The entry responsible for this is
SEQ
200
NAME
YTD
STATEMENT
IIF(INSTR("INCLEQ",[%ACCOUNTDIM%].PROPERTIES("ACCTYPE")), -1 ,1)*([MEASURES].[SIGNEDDATA],CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))
DESCRIPTION
YTD
IS VISIBLE
1
IS YTD APP
1
IS SYSTEM
1
What I would like to see is:
I enter
INC -1000
EXP 500
AST 1000
LEQ -500
and the system saves
INC -1000
EXP 500
AST 1000
LEQ -500
The profit for the P&L is again brought into the BS through an account transformation rule, which means I can change the sign and therefore my balance sheet does balance.
I expted this to happen when I change the entry in dbo.tblmeasureformula to
SEQ
200
NAME
YTD
STATEMENT
IIF(INSTR("INCLEQ",[%ACCOUNTDIM%].PROPERTIES("ACCTYPE")), 1 ,1)*([MEASURES].[SIGNEDDATA],CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%]))
DESCRIPTION
YTD
IS VISIBLE
1
IS YTD APP
1
IS SYSTEM
1
Is this assumption correct?
Hi
Here are example how you can treat an acc type in financial method.
TYPE INPUT TEMPLATE DATABASE Position
INC + + - Dr
INC - - + Cr
EXP + + + Dr
EXP - - - Cr
AST + + + Dr
AST - - - Cr
LEQ + + - Dr
LEQ - - + Cr
"" + - - Cr
"" - + + Dr
how to read table :
If type INC inputted in positive value, value will show in template positive either but in database recorded as minus and it given position debit in journal method.
basically if you do the following table it will not required any script that transform positive and minus value..
as example i give..
SALES as INC with value 300
COGS as EXP with value 150
NETPROFIT as INC with value 150
when you transfer an income type to lequity type is shown a same manner.
hope helps
User | Count |
---|---|
15 | |
3 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.