YTD calculation using SQL script logic

[Former Member]

Hi,

Has anyone used the SQL logic to generate YTD numbers. I have a requirement where I need to have a account which should have a Net_Income_YTD value stored in it. I can have this easily through MDX, but trying to see if we can achieve this in any way through SQL logic.

MDX formula for this calculation is:-

(Account).(Net_Income_YTD) = (Account).(Net_Income),(Measures).(YTD)

Thanks

Sharath

[Former Member]

Hi,

The only way that i can see that you would be able to calculate the YTD value is to calculate it before you need to use it. You could either calculate the value in the same script by detecting the YEAR and calculating the YTD value for every month for the year and then using the appropriate value in your next step. Alternatively you could also write this into your default logic and calculate the value every time the value change.

The formula would be something like (please forgive the syntax, i'm just winging it a bit here )

*calc_each_Period
*when time.monthnum
*is <> 1
*REC(Expression=get(account="income",time=prior)+get(account="income"),NOADD,account="YTDincome")
*else
*REC(Expression=get(account="income"),NOADD,account="YTDincome")
*endwhen

Regards,

Andries

[Former Member]

Andries,

This is a good idea and I had thought of the same logic but found it difficult to make this work. Not sure whether I am making any mistakes in the script in replicate this idea or is it a limitation of the script. I found different issues in making this work correctly.

Now when I used the formula which you have given I see a new problem.

My "income" account itself is a calculated account which is derived using SQL logic. But when I have your formula added to my Default.lgx file neither "income" nor "YTDincome" is getting generated in my WB table. Looks strange as "income" formula comes before "YTDincome" formula and it is not dependent on "YTDincome".

Have you actually used this in any of your application and able to use it without any errors? Please advice.

Thanks

Sharath

[Former Member]

Hi,

You are going to struggle using the calculated account with SQL logic, but there may be a way. I take it that the "income" account is the income statement roll up account that is the sum of all the income statement accounts. If this is the case, then you can put a member property on the income statement accounts that will identify them. You can then first do the summing:

*when Account.FINSTMNT
*is "IS"
*REC(factor=1,Account="SUMINCOME")
*ENDWHEN
*GO

*when time.monthnum
*is NOT 1
*REC(Expression=get(account="SUMINCOME",time=prior)+get(account="SUMINCOME"),NOADD,account="YTDincome")
*else
*REC(Expression=get(account="SUMINCOME"),NOADD,account="YTDincome")
*endwhen

I'm sure this will effectively do a sum of all the income statement account (giving you the same value) and then get a YTD calue thereafter.

Regards,

Andries

[Former Member]

Hi Andries,

I used the below syntax as per your explaination but no records are getting generated in WB table for the YTD account. Can you please review the below syntax and advice whether I need to change anything.

PS: for testing I am sending data to NI account and expecting YTD values in NI_YTD

*XDIM_MEMBERSET ACCOUNT=<ALL>

*CALC_EACH_PERIOD

*WHEN TIME.MONTHNUM

*IS NOT 1

*REC(Expression=get(account=NI,Time=Prior)+get(account=NI),NOADD,Account=NI_YTD)

*Else

*REC(Expression=get(account=NI),NOADD,Account=NI_YTD)

*ENDWHEN

*COMMIT

Thanks

Sharath

[Former Member]

Andries,

I used the below syntax available on the SAP 7.0 admin guide and I think only this works correctly without any errors.

//----

---

Net Income to BS

*CALC_EACH_PERIOD

*XDIM_MEMBERSET TIME=PRIOR,%TIME_SET%,%YEAR%.DEC

*CALC_DUMMY_ORG ACCOUNT=PARENTH1

*WHEN TIME

*IS PRIOR

*WHEN ACCOUNT

*IS CYNI

*REC(ACCOUNT=#OPE_CYNI,TIME=NEXT)

*ENDWHEN

*ELSE

*WHEN ACCOUNT

*IS #NETINCOME

*REC(ACCOUNT=#OPE_CYNI,TIME=NEXT)

*REC(ACCOUNT=CYNI)

*IS #OPE_CYNI

*WHEN TIME.PERIOD

*IS<>JAN

*REC(ACCOUNT=#OPE_CYNI,TIME=NEXT)

*REC(ACCOUNT=CYNI)

*ENDWHEN

*ENDWHEN

*ENDWHEN

//----

---

[Former Member]

Using the Account transformation rules business rule you should be able to generate a YTD value from the rule and post it to another account. We use it for sending CYNI to the Balance Sheet. Check the APShell for the example of the rule in the Legal consolidation app if its there. But yes, you can use the business rule to solve this; assuming that you application can be modified for business rules.

Hope this helps

[Former Member]

Hi Petar,

Thanks for your advice.

Legalapp in my Apshell do not have any account transformation rules. But looking at the account transformation table, I can see it requires dimension type datasrc and subtable for datasource and flow fields. But currently my application is similar to the Finance application from Apshell and do not have these dimension types.

Still I created a rule in account transformation table by having these fields blank and the validation was successful. Then I used SPRUNCALCACCOUNT in default.LGL file to trigger this rule but I don't see this working.

Can you help me on below questions:

1. Is it necessary to have datasrc and subtable dimensions to create account transformation rule?

2. Will SPRUNCALCACCOUNT write the values to WB table or should I use COMMIT or any other command along with his to have the values written?

3. Is there any way/place where I can get the examples of Account Transformation rules ?

Thanks

Sharath

[Former Member]

Sharath,

I'm guessing as YTD,QTD are Calcualted members, probably they are not accessible via SQL logic.

again my guess here...

vishal.