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]

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