EPMcopyrange vs LocalMember for calculations

[former_member196865]

Hi Gurus,

I have an input form where I need to calculate data for certain months and save it to the input data source. I am wondering whether I should use EPM copyrange or a localmember to do the calculation.

Example:

                    Col1-       Col2       - Col3   

                    TotalDS-TotalDS- Input

                    Actual- Estimated- Estimated

                    Jan15- Calculation15-Jan16

Account1 100        =100*1.1            110

Account2  200       =200*1.1            220

Account3  300       =300*1.1            330

Should I use a local member for column2 or can I just use epmcopyrange? The reason I am asking is because if I have to calculate in for 24 months, there would be a minimum of 24 local members. I need local members for other calculations as well. So we are talking about 35 local members.

Regards,

David

[former_member186338 · Accepted solution]

Ups,

May be it's better to calculate in default.lgf?

Vadim

[former_member186338 · Accepted solution]

P.S. With local member you will have to use EPMSaveData... you can't put local member in the column with real member

[former_member196865 · Accepted solution]

Hi Vadim,

I don't have to write anything in column2 ... column 3 is where I Input. I have to show the calculations in column 2 though . It is for representation purposes, I have to put it there. Given the number of formulas that are being used and the number of local members I would have to create, I am wondering whether I should opt for epmcopyrange instead of a local member. I have asked the users and they said they would like to do the calculations on the excel sheet itself instead of SL.

Note: Total DS is a calculated member and cannot be written to.

[former_member186338 · Accepted solution]

Sorry, but "I don't have to write anything in column2 ... column 3 is where I Input." - not clear!

"I have an input form where I need to calculate data for certain months and save it to the input data source."

The result of the formula will be saved? How?

Can you provide some real screenshot?

"I have asked the users and they said they would like to do the calculations on the excel sheet itself instead of SL." - I think it's a bad idea. Better to show some formula result in the report, but to perform calculations in script logic.

Vadim

[former_member196865 · Accepted solution]

In Column h, i use the formula to calculate an intermediate calculation, column h takes value from column g and multiplies it with rate. I have to input data only from column h. Column g is a dummy column used to show intermediate calculations for the users- I don't save anything from here. This is where I am thinking whether to to use a local member or epmcopyrange I will have many columns like this one in the sheet.

Further, the user has to give the multiple rate values depending on the situation and there is no dimension we use to capture tha data. So SL will not be useful for us as we cannot keep changing SL to give multiple rate values.

[former_member186338 · Accepted solution]

Still the logic is not clear, no formulas shown on the screenshot...

Like what is the formula in H? How it will be created? How the result will be saved...

Vadim

P.S. In general for rate some dedicated account is used (to calculate in SL)

[former_member196865 · Accepted solution]

Hi Vadim,

Please find the below screenshot.

The data needs to be saved at the intersection of (Input,Estimated15,jan2016,account1 etc).

[former_member186338 · Accepted solution]

Last question - what about row axis - accounts - is it static or dynamic?

[former_member196865 · Accepted solution]

Hi Vadim,

The row is dynamic as I will be changing the accounts that show up with the help of page axis selections.

Thank you for replying and taking interest in this. My fault that I didn't explain it clearly before.

Regards,

David

[former_member186338 · Accepted solution]

OK, finally you have static column axis and dynamic row axis.

Columns will be:

1. Read Actual data from the cube (correct?)

2. Calculate preliminary result by multiplying actual by 1.1 (to show on the report only)

3. Calculate final result and store it to Estimate

Correct?

[former_member196865 · Accepted solution]

Hi Vadim,

Yes that is absolutely correct. The columns are static. The year which they are written to will change though using a member property, but the number of columns in the input form will be the same no matter what the year is.

[former_member186338 · Accepted solution]

Ty my mind, CopyRange will be a better solution in this case:

Test:

C1: =B1*1,1

D1: =(1+$G$1)*C1

Vadim

P.S.

B1 is empty - the values from the cube will be shown!