EPM SP 22 EVDRE Report and drill down

[Former Member]

Hi,

I have a Evdre Report with row expansion and fixed columns. In the colkeyrange I have some empty columns used for delta excel formulas.

When I use the double click to perform a drill down on the row dimensions with the Expand by inserting option, the expansion works fine but on some of the columns the formula are resolved and the numeric value is shown as formula (Es: X23+W23 ---> =201222 (result value))

If I expand again the report the first row formula/value is expanded on all the rows (=201222 on all)

I have tried multiple combinations for the formula, I don't see any reasonable explanation. The only behaviour pattern I was able to isolate is the following:

It seems the drill-down resolves any kind of formula that starts with a letter between T and Z

I'm using BPC 10 NW with SP22.1. I have Office 2013 and I have the same described behaviour also on Office 2010.

Did someone have the same issue?

thanks

Iolanda

[Former Member]

It also happens with SP from 20 to 22

With SP 19 the formulas get not resolved but after the double click the Excel calculation option switches from Auto to Manual

Iolanda

[former_member186338]

Hi Iolanda,

It's not possible to understand the issue without some clear report screenshots!

Vadim

[Former Member]

Attached you can find my screenshot.

I used for all the expansion the same report and made the drill-down on row PARENTH1_A.

(the original expansion for row is SELF,ALL AND TECH<>"Y",PARENTAFTER

Case A:

Original Formula Z23=X23-Y23

Original Formula AD23=AB23-AC23

File

screen3.PNG  ----> report before drill-down Formula bar on Z23

screen4.PNG ----> report after drill-down Formula on Z23

screen5.PNG ----> report after drill-down Formula on AD23

Case B:

Changed formula Z23=SUM(X23)-SUM(Y23)

Changed formula AD23=SUM(AB23)-SUM(AB23)

File

screen.PNG  ----> report before drill-down Formula bar on Z23

screen1.PNG ----> report after drill-down Formula on Z23

screen2.PNG ----> report after drill-down Formula on AD23

I also tried to use the working original formula form cell AD23 on Z23 and after the drill down the formula doesn't change ( file: screen10.PNG)

[Former Member]

I cannot post more than three at a time and zip is not allowed...sorry new on the forum

[Former Member]

last one

[former_member186338]

Ups, I am lost in all this screenshots... I think you have to narrow down the issue using some very simple report...

Vadim

[Former Member]

This report has only Account and transaction currency on rows, columns are fixed

Account is in the row expansin with the SELF,ALL AND TECH<>"Y",PARENTAFTER  MemberSet

Transaction currency is set to SELF

in the pagekeyrange I have

Account --> PARENTH1_A

Transaction currency --> T_ALL

Columns are fixed and the colkeyrange goes from S to AF

I used columns Z and AD for my tests.

If I double click on PARENTH1_A the underlings Dep1, Dep2, Dep3 and Dep4 are added to the expansion (red font elements).

The the "scree1" file is the result of the drill down starting form the "screen" file situation

The the "scree4" file is the result of the drill down starting form the "screen3" file situation

the other are just to show the different behaviour of the cell AD where the original formula was not modified by the drill down.

I made tons of test on different columns and different formulas.

Whenever I use a formula starting with a letter between T and Z the formula get solved and the result i shown as ="formula result". Doesn't matters into which column I put the formula.

Even if I use formulas like =VLOOKUP()

Iolanda

[former_member186338]

I still think you have to narrow down the issue:

Minimalistic report with only few rows and columns to reproduce the issue!

Vadim

[Former Member]

I already narrowed down the issue:

"It seems the drill-down resolves any kind of formula that starts with a letter between T and Z"

Anyway I did a simpler report:

Account D2000.100 on row espansion with SELF,BAS

Two Columns with time on colkeyrange

On the Data range I put two formulas; a direct reference to rowkeyrange and a vlookup on account description.

After the drill down only the Vlookup formula gets resolved.

[former_member186338]

Can you provide full screenshots to see EVDRE ranges and parameters? Unable to reproduce your issue!

Vadim

P.S. I see strange row axis with duplicated accounts etc...

[Former Member]

The accounts on row axis are duplicate because to show the drill down behaviour I used as expansion a parent account (D2000.100) and set the MemberSet to SELF,DEP

("before" screenshot)

I made the drill down on D2000.100 and the effects are shown on the "after" screenshot.

As I stated before, I used the "Expand by inserting option" so the DEP elements are added to the report and this results in the duplication of the accounts.

I have tried this on 3 different BPC 10 NW and one BPC 10 connection on different projects.

I have tried on 4 different PC with Office 2013 and Office 2010

All have the same outcome when I replicate a simple report with drill down on report with formula.

Which SP did you used for the test?

The moderator has not approved jet a post of mine from thursday that specifies also:

"It also happens with SP from 20 to 22"

[former_member186338]

I managed to reproduce the issue finally!

Workaround - add something at the beginning of the formula:

=IF(TRUE;VLOOKUP(G13;$E$13:$F$45;2;0))

"IF(TRUE;" - will always calculate after ";"

Vadim

[Former Member]

I changed already all my formulas but I think this is not an acceptable behaviour and it takes a lot of extra effort to migrate EVDRE reports.

Iolanda

[former_member186338]

Yes, this issue is only BPC 10 specific, I tested BPC 7.5 - no issues!

And for sure if you try to drill down EVDRE report working in BPC 7.5 - in 10 you will have this issue... But not only this issue - there is no warranty that any EVDRE report from 7.5 will perform correctly in BPC 10. You have to talk directly to SAP and may be this issue will be solved sometimes...

Vadim

[Former Member]

yes, the worst part is that this issue is introduced starting from SP 20.

It is quite a problem if you already migrated and tested some reports that use the drill down functionality and your client decide to upgrade the SP. 😞

I will try to open an incident on SAP support system

thanks for your time

Iolanda

[former_member186338]

But with BPC 10 you have to migrate from EVDRE to EPM reports anyway... EVDRE is a temporary solution!

Vadim