on 03-30-2015 2:16 PM
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
Hi Valeria,
The requirements are not clear! Please, provide screenshots.
Vadim
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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 | |
5 | |
2 | |
1 | |
1 | |
1 | |
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.