Skip to Content
0
Former Member
Sep 07, 2012 at 11:23 AM

EPM: getting wrong values from dynamic slices (member selection by Excel formulas)

234 Views

Hallo Community,

I encountered a really weird problem within my reports and hope you can help me.

------ Common Information

We are using SAP EPM 10.0 SP12 Patch 2 .NET 3.5 Build 8083 which was shortly updated from SP10.

I am not sure if it is really relevant but: We are using a BADI implementation to be able to spread values entered on node elements within the account and profit center structure as well as within the time dimension.

------ Problem

My report contains a lot of Excel calculated members. The data cells though show wrong values on some member combinations. Here is an example:

  • All title members (page key range) except the selection of profit center are set to a leaf element. The profit center is set to a parent of a leaf element.
  • The rows contain a static selection of the accounts (leaf & nodes).
  • The number of columns are fixed but the member combination within a column consists of four dimensions / members which are dynamically calculated.

If the cell within this report initially showed a value of 50.000 and I enter 100.000 and click Save & Refresh, the values are sent correctly to the database (proofed while debugging the BADI interface). After the refresh though, the data switches back to 50.000. If I build a second report right beneath the old (dynamic) report within the same sheet and make all members static (e.g. I only use the member selector and no formula), the values are shown correctly within this part of the report.

There are several other observations I made:

  • The problem (missing aggregation of values / showing old values) only occurs when I enter data on a node element within the profit center structure. If I switch to one of the leaf elements within the profit center structure, the values are shown correctly for both parts of the reports (e.g. dynamic vs. static).
  • If I select the data cell within my dynamic part of the report containing the "wrong" value and open the Excel context menu and click on EPM >> Refresh Selected Data the report also shows the correct value. If I though do a standard Refresh over die EPM ribbon, the values again switches back to the old value.
  • If I do (after entering data as described above) a "Full Optimize" for that specific cube over the BPC web admin interface, a refresh within my report shows correct values for both parts of the report (e.g. dynamic & static). New changes though come up with the same behaviour as before.

------ Detailed Description Of Report

I have set up a report with 4 dimensions within the columns. The screenshot shows a dynamic part (column M & N / red frame) and their static pendants capsuled in a second report within the same sheet (column W & X / green frame).

All members of the dynamic part are calculated dynamically via Excel and are linked into the EPM formula (see screenshot). The dark grey area (row 40-44) are filled with Excel formulas to calculate the correct members for the data slice. The rows 45-49 contained the standard EPM formulas which then were adapted by myself to look like that:

Example for cell M45:

= EPMOlapMemberO(M$40;"[TIME].[PARENTH1].[2013.06]";"2013.06";"";"000")

As you can see, the values of the cells M61 and W61 are different. The cell W61 shows the new / correct value, the cell M61 still shows the old value I have entered before.

Entering data within the dynamic part of the report and a Save & Refresh lets the static part update its values correctly. Also entering data within the static part of the report is followed by a correct update of the values.

Now, I am not really sure what the next step would be to get to the root of this problem. Any help / ideas would be really appreciated.

Cheers,

Gernot

Attachments

Report.png (203.6 kB)