SAP BPC: calculation by using script logic

[Former Member]

Hi experts,

I am creating an input schedule using three dimensions i.e account in the row, time in the column and another trading dimension in the column as well. Now I have to input data and have to calculate income statement for the year lets say 2011. in year 2012, i do the same thing and create income statement, but this time i have to include slight difference. this time, i have calculate gross profit margin using previous year gross profit. I explain it bit more below

Income Statement	Year 2010	Year 2011	Year 2012
Turnover	1000	1100	1200
Cost of Sales	500	550	600
Gross profit	500	550	600
Margin	0	Gross profit (2011)/Grossprofit (2010)-1	Gross profit (2012)/Grossprofit (2011) -1

Is there any ways i can write a sript logic to calculate this gross profit margin (calculation explained above). Currently, I am using excel formulla. but i know this is just a quick fix not the right solution. Could anyone please help me out.

Thanks

Warren

[Former Member · Accepted solution]

Hi Warren,

Are you going to display the margin only in this template?

My suggestion would be to use excel formula to calculate this and send this data back to the database. You can definitely do this using script logic, but then you will have to execute the script using DM package. This calculation is not too complex, and I think its easier using the excel formula.

Hope this helps.

[Former Member · Accepted solution]

Hi Nilanjan

Thanks for quick response,

I am going to display in the template. I have few confusions

1. If I use the excel formula and send it to database. Can I tranfer this calculated data to other application.
2. What if, I add new dimension members, wouldnt it disrupt the excel formula because of changes in the cells.
3. If i use only script logic, how can i write it. I tried but was unsuccessfull..

Thanks

Warren

[Former Member · Accepted solution]

Hi Warren,

To answer your first 2 questions, writing an excel formula will not have any impact to your model.

Can you share the script you wrote. I will try to enhance it as required.

[Former Member · Accepted solution]

Hi Warren,

As elucidated by Nilanjan depends on your requirement it's much better to use Excel formulas.

Normally I use script logic when the data used in calculation comes from differents process.

In your case, if you want to send the Margin to another model (question 1), you can write a script only to send the these data.

About second question, yes, if you refresh the report and new members are add, you'll need to maintain the formula.

But, if after all, you decide to use a script, it will be something like:

*XDIM_MEMBERSET ENTITY = 1000
*XDIM_MEMBERSET ACCOUNT=Gross profit
*XDIM_MEMBERSET INPUTCURRENCY=USD
*WHEN ACCOUNT
  *IS Gross profit
    *REC(ACCOUNT="Margin",EXPRESSION=( %VALUE%  / [TIME].[TMVL(-12,%TIME_SET%)] ) - 1)
*ENDWHEN

Regards,

Lucas

[Former Member · Accepted solution]

Actually I just wanted to know that how can i write in sql logic. You solved it...

Thanks Lucas for great help.

[Former Member]

Thanks Nilanjan....