Dear Experts,
I am trying to replace the MDX formula from my account dimension due to performance issues, but I am facing a problem in understanding how should I make the SQL scrip logic work to get the same results.
Just an hypothitical example:
I need to find the difference between account "Revenue" and "Expense" to arrive at the account "Profit". In this case "Revenue" and "Expense" are parents at level 4 which is aggregration of 200 accounts at base level. I am using the below scrip which is not working.
*WHEN Account
*IS Revenue, Expense
*REC(Account=Profit)
*ENDWHEN
This logic does not work as when I send data, we will be sending it to base level account and the above scrip recognize only the Revenue and Expense which is a roll-up. I can make this work be writing all 200 accounts in the scrip which is not practical.
Can you advice how should I use the scrip in a simpler way and make the logic work.
Thanks
Sharath