Skip to Content
0

Inconsistent formula in a Column (Unable to clear EPMOLAPMember function)

Mar 13, 2017 at 12:47 PM

144

avatar image
Former Member

Hi Experts,

We have an EPM data retrieval report here that the column T is supposed to show a SUM value of the row, only cell T67 always reverts to an EPMOlapMemberO(SUM()) which cannot be changed or cleared.

in cell T67:

= EPMOlapMemberO(SUM(H67:S67),"[ACCOUNT].[H1].[120000]","120000 - Surplus","","000")

In cell T66:

=SUM(H66:S66)

In Cell T68:

=SUM(H68:S68)

Plz let me know if there is any wacapture.pngy I can replace EPMOlapMemberO formula or a way to delete the EPM expansion worksheet , I really appreciate your help!

Best Regards,

Yajing

capture.png (24.7 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Vadim Kalinin Mar 13, 2017 at 01:04 PM
0

Please provide the report screenshot!

Show 6 Share
10 |10000 characters needed characters left characters exceeded

And provide full info about report:

1. Have you enabled "Calculate Parents in Hierarchy"?

2. Do you use local members (if yes - setup of each local member)

0
Former Member

Hello Vadim,

We don't use local members and our "calculated parents in Hierarchy" is greyed out.

In the report, all the numbers in Column T are calculated in SUM functions except T67 like the screenshot I included. All the other cells are retrieved by EPMdataretrieval function and column T will update the sum of each row after we refresh the report. On the report editor, the cell with expansion report is BP67 however, the cell BP67 on our report is set to be zero with out any calculation embedded. We just need to change the formula in T67 to SUM function, but so far we haven't figured out why this is happening...

bp67.png

capture.png

capture.png (12.7 kB)
bp67.png (24.6 kB)
0

Ups, you are using "EPMdataretrieval function"??? In EPM report???

Sorry, please full details on your report! Looks like you are doing something wrong!

0

P.S. Please! Use standard button "Insert Image" to attach images inline.

Please show Excel formulas in the cells!

0
Former Member

Yes, other columns except column T use EPMretrieve functions to get the data from the database, Col T is supposed to calculate a sum number for each row.

epm-functions.png (16.2 kB)
capture.png (12.7 kB)
0

First - don't use EPMRetrieveData functions on the data area of EPM report - absolutely bad practice!

Recreate your report as normal EPM report.

Your strange function is due to Member recognition enabled! :)

0
avatar image
Former Member Mar 13, 2017 at 04:34 PM
0

My member recognition has been turned off all the time. but it doesn't show any difference.

Is there a way to just correct this specific cell instead of recreating the whole report?

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Sorry, your report is incorrect! You need to get some basic training to understand report creation in BPC...

0