on 2012 Apr 12 1:51 PM
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.
Request clarification before answering.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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.
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
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
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.