Using Excel Formulas in Row key/Column key

[Former Member]

Hello,

Is it possible to use simple excel formula(if statement basically) in row key or column key. I have tried entering formula in there, also tried having formula somewhere else and rowkey columns referencing that formula. doesnt really work. This is a dynamically expanded report, not static.

Thanks.

[Former Member · Accepted solution]

Hi Zack,

Any area of the template, which is expanded dynamically, wont hold the formulas. After the refresh, the formula will be overwritten.

Where exactly did you write your formula, and what was the formula?

[Former Member · Accepted solution]

Here is what I am trying to do exactly. I have two dimensions in row key - Account & Profit center dimension. Account dimension needs to be expanded dynamically, another one - Profit center has to have a condition - If the account dimension member next to it (in the same row) is Non calculated member, then put No_profitcenter as the profitcenter member. if its calculated, then put all_profitcenter as hte profit center member.

Here is what I tried: EVPRO next to row key, pulling calc property. then in row key for profit center, i put a condition that If that evpro is N, then No_profitcenter, else all_profitcenter should be the value. Please advise. thanks.

[JohnL · Accepted solution]

Hi Zack,

Sorry that won't work with BPC 7.X EVDRE reports. You can do what you describe using the new EPM Add-In for BPC 10.

As a workaround, have you tried using a CellKeyRange? See the below screenshot for an example.

The cellkeyrange is defined in the range W9:Z9 and the formula in each of those cells is the IF statement =IF($J9="Y","A001","")

HTH,

John

[Former Member · Accepted solution]

John,

The help site specifically states that Cellkeyranges will only work in static reports, mine is dynamic. Is the help site wrong or do you have any other suggestions? thanks.

[JohnL · Accepted solution]

Hi Zack,

I think the Help site may be incorrect. Look at my prior screen shot up close by clicking on it.

You will see I have expansions turned on thus it is dynamic.

Give it a try and let us know how it goes.

Thanks,

John

[Former Member · Accepted solution]

I get 440-Automation Error when I try to implement it. Here is step by step what I did:

Step 1 - Open EVDRE Report with account in rows, time in column

Step 2-  I created cellkeyrange with =EVRNG(K2:M2)

step 3 - I inserted evpro by account expansion for calc

step 4 - in k2:m2 I entered a formula, =if(f15(the evpro cell)="n","ACTUAL","FORECAST")

Step 5: I hit expand. and then refresh.

I get the 440-Automation error at this point.

Then I removed time from expansion range, so in other words just rows are dynamically expanding. still got the same error.     

[JohnL · Accepted solution]

Hi Zack,

You can't use F15 as a formula reference in your cellkeyrange.

Look at my example, I have my cell keyrange as W9:Z9

and my formula in each of the cells within that range is =IF($J9="Y","A001","")

When the cell key range gets applied, look at the formula's that get populated in the cellkeyrange just to the right of the data grid.

Thanks,

John

[Former Member · Accepted solution]

I dont see any formula in J9 in your screenshot though. J9 in your example seems to be blank.

[JohnL · Accepted solution]

Hi Zack,

$J9 needs to be in the cellkeyrange formula of W9:Z9. After you expand the report, the defined cell key range (including the formula) in W9:Z9 gets applied to the range O17:R20.

Now if you look at the formula's in range O17:R20 (after expanding), you'll see the IF formulas are referencing the correct column ($J) and the corresponding row.

Keep trying and you'll see what I mean.

Thanks,

John

[Former Member · Accepted solution]

Wohlaa! Worked!!!!!  so basically it looks like(from my testing) that the cell key value will override current view for that specific cell. Great! One thing that I had a question: this is fine if the number of columns stay the same, what if the columns dynamically expand? one solution I was thinking is may be look at maximum possible columns(about 100), and have 110columns as cell key range. When I tested it with 4 columns in cell key range and only one column in the column key(2012.jan), it did expand with EVDRE OK! what do you think about this solution?

Hey SAP, GIVE THIS GUY JOHN A RAISE!    

[jrg_finster3]

Hi Zack,

what you can do is to combine two EVDRE's. On dynamic with only your Account dimension as rowkey. Then you put your formula in the column right of your keyrange to evaluate your Profitcenter. Your Formula is then treated as a normal "Header" Cell (like The EVDES area) and the formula will not be overwritten.

Then you use another EVDRE Formula to build a EVDRE without dynamic rows and you define your Account and Profitcenter Column as rowkeyrange.

Regards

Jörg

[Former Member]

Zack

Assuming you mean you are trying to use formulas to set the keys for retrieval , then the report cannot be dynamic as the expansion with overwrite keys.

You can have calculations to set column keys and have the rows expand or vice versa.

kind regards

[JohnL]

Hi Zack,

What are you trying to do exactly?

I don't think you can use formula's in those key ranges because they will be deleted after you re-expand the report as Roberto mentioned.

You can have formula's to right of the key ranges and those would not get removed.

Thanks,

John

[former_member186498]

Hi Zack,

you can use excel formula outside the data region, because inside after every expand the retrieved data will overwrite your formula. You can you inside just formulas for conditional formatting. Please note that the data region could be a combination of several intervals and between this intervals you're able to use formulas (they will be automatically expanded).

Kind regards

     Roberto