cancel
Showing results for 
Search instead for 
Did you mean: 

EPMDimensionOverride with dynamic range and EPMOlapMemberO Formula

Former Member
0 Kudos

Hi all,

I'm trying to replicate an Evdre Input Form in EPM.

The schedule is composed by 2 queries: the first query expand on the Account dimension and retrive actual data, the second query modify the first query rowkeyrange by adding the suffix "_D" and save planning data (this because the first query expand on subtotal account and the second query save data on the relative dummy nodes).

I tried following:

- to customize EPMDimensionOverride expanding on dynamic cell range (by EPMCellRanges) but it doesn't works; i found a thread in sap community network (n. 3531850) that confirm that EPMDimensionOverride just works with single or concatenated values. I created a list of values based on first query expansion but this work around have the excel cell characters limitation;

- i tried also to utilize formatting sheet, overriding the row header with a customized EPMOlapMemberO formula. If the report is static the expansion modify the row header and data are correct. Otherwise, if the report have a dynamic expansion, made by EPMDimensionOverride, the expansion modify the row header but data are not updated: the report get data based on the original definition of row header (referred to the EPMDimensionOverride).

I can't create an ad-hoc property in dimension, and i would not create n local members to send data with EPMSaveData formula (i have 12 months in colum, i should create 24 local members: 12 to input data and 12 to send data), how can i resolve this problem?

Thanks,

Valeria

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member186338
Active Contributor
0 Kudos

Hi Valeria,

The requirements are not clear! Please, provide screenshots.

Vadim

Former Member
0 Kudos

Hi all,

i try to better explain my question.

In my report i have two queries: Report000 reads actual data, and Report001 send Budget values.

Budget values must be input at level 3 of the Account hierarchy but these nodes are calculated. In Account hierarchy there are dummy nodes for corresponding level 3 set by concatenating the suffix “_D” with the ID, these dummies are not calculated so planners can input data (i have not a property that could link real and dummy node).

The schedule have fix col range and dynamic row range. I have utilized EPMDimensionOverride to customize row expansion both Report000 and Report001. Report001 expansion is based on Report000 expansion with the necessary modifications in row header. In Report000 i have created a LocalMember (LocalMember000) that reproduces the Budget Row Header: the formula read the HLEVEL property and, if the property value is =”3”, concatenate “_D” with the id (as see in Picture n.1). The formula is: =IF(EPMMemberProperty(; EPMMemberID(A8); "HLEVEL")="3"; A8&"_D"; A8).

Picture n.1

So I have tried to customize Report001 row header utilizing the FormattingSheet001: in the Row Header section i have inserted dynamic EPMOlapMemberO linked to LocalMember000 (Picture n.2). The formula is “=EPMOlapMemberO("[ACCOUNT].[PARENTH1].["&F8&"]","[ACCOUNT].[PARENTH1].["&F8&"]",F8,F8,"000")”.

Picture n.2

When I perform refresh the Report001 Row header is modified in the correct way (Picture n.3) but data are not correct: Budget data are relatives to the EPMDimensionOverride settings of row header (as Picture n.4). 

Picture n.3

Picture n.4

The correct Budget data are represented in Picture n.5.

Picture n.5

I can not create a new property to manage this expansion. The alternative could be to create n local members utilizing EPMSaveData or to generate a string with the new values concatenation (as mentioned in n. 3531850 discussion) but this have excel cells limitations. I have many input forms with this implementations and the row expansions could be very large.

Thanks!

Valeria

former_member186338
Active Contributor
0 Kudos

Hi Valeria,

I recommend to implement WRITE BACK badi and in the input form write to parents. Then on the write back badi level test attempt to write to parent and instead of parent write to parent_D.

Read here:

Vadim

P.S. your attempts to generate row axis of the second report will fail!

Former Member
0 Kudos

Hi Vadim,

thanks for your suggestion.

Is there any other way to reproduce this form by utilizing only EPM functions (without implementing BADI)?

Valeria

former_member186338
Active Contributor
0 Kudos

Hi Valeria,

Try the following local member for Report000:

=IF(EPMMemberProperty(,EPMDIM_CURRENT_MEMBER(ACCOUNT),"HLEVEL")="3",EPMDIM_CURRENT_MEMBER(ACCOUNT)&"_D",EPMDIM_CURRENT_MEMBER(ACCOUNT))&IF($B9<>"",","&$B9,"")

Assuming the local member is in column B and the first data row is 8 - $B9 in the formula.

Then in B8 you will have a comma separated list of ACCOUNT members for the second report.

Use this cell in =EPMDimensionOverride("001";"ACCOUNT";$B$8)

Vadim

former_member186338
Active Contributor
0 Kudos

Something like:

Vadim

Former Member
0 Kudos

I had just implemented a VBA macro that build a string concatenating the single elements of the second Report and i have linked it to EPMDimensionOverride but there is the characters cells limitation and, besides, i need to perform two refresh: the first one to expand the first report and the second to expand the other query based on the generated string.

I will try with your suggestion, i hope by this way i will not have necessity of double refresh.

Valeria

former_member186338
Active Contributor
0 Kudos

1. VBA is the last option to evaluate my concatenation in local member formula will do the same job.

2. Yes, limitation is the same

3. two refresh is ok

Vadim

P.S. Badi solution is better

former_member186338
Active Contributor
0 Kudos

P.S. How do you want to avoid double refresh? How do you select account members for the first report?

Former Member
0 Kudos

Hi Vadim,

the first report selection is setted by the user (based on a customized selection pane).

I implemented your suggestion: unfortunately double refresh is necessary because the first expansion cause loss of reference of the concatenation cell and EPMDimensionOverride goes in #REF.

Thanks for your time.

Valeria

former_member186338
Active Contributor
0 Kudos

"EPMDimensionOverride goes in #REF"??? - what do you mean? Can you provide a screenshot?

Double refresh is required anyway!

Vadim

Shrikant_Jadhav
Active Contributor
0 Kudos

Hi Valeria,

I am not clear about your requirement but for dimension override you can refer below doc, may be helpful.

Shrikant