Technical Articles
Static report in BPC NW 10
In BPC NW 7.5 finance users were able to create static report having some very basic knowledge about EVDRE. The only thing to remember was: “remove reference for expand range from EVDRE formula”. Then we have a static report where user can insert rows and columns use any excel formulas etc.
After some experiments with BPC 10 (in native, not EVDRE mode) I have found out that the usage of the automatic report generation will be too complex for the ordinary finance user who wanted to create a custom layout of the report.
Then I tried to create a “static” report and it takes more time and efforts then in 7.5.
The sample requirements:
1. All report parameters (dimension members and report year and month) have to be text values in some cells. If the user changes the values in cells (by Excel link) and refresh the report the data have to be updated. For sure EPMContextMember or EPMSelectMember can be used to provide text values of required dimension member ID’s.
2. Multiple reports on one sheet (with column axis sharing). 2 reports in this sample (2 models with the same column keys).
3. 3 dimensions in column headers (MEASURES, CATEGORY and TIME)
4. 1 Dimension in row headers (ACCOUNT for first Model/report, ADV for second Model/report)
5. Different page axis members for reports. 2 reports in this sample will have slightly different page axis due to different number of dimensions in the corresponding models. First report dimensions: ENTITY, TITLES, CORPDIR; second report dimensions: ENTITY, TITLES.
6. Number of empty lines and a lot of Excel formulas.
Steps to create a report:
1. Switch off autofit column width.
2. Switch on repeat column headers.
3. Switch off automatic member and local member recognition.
4. Select connection for the first report (first model).
5. Create number of cells with report parameters at the top of sheet (B1-B10 cells):
B1: PERIODIC (MEASURES member)
B2: YTD (MEASURES member)
B3: 2012 (Year – to be a part of period ID)
B4: .MAR (Month – to be a part of period ID)
B5: ACTUAL (CATEGORY member)
B6: BUDGET (CATEGORY member)
B7: LE02 (CATEGORY member)
B8: BE1000 (ENTITY member)
B9: ALL (CORPDIR user def dimension member)
B10: 1110 (TITLES user def dimension member)
6. Switch on automatic member recognition.
7. Creating column keys:
In B13 (Left first column key) I put a formula: =$B$1 and it was recognized as PERIODIC with the variable member formula:
= EPMOlapMemberO($B$1,”[MEASURES].[].[PERIODIC]”,”Periodic”,””,”000″)
In B14 (Left second column key) I put a formula: =$B$5 and it was recognized as ACTUAL with the the variable member formula:
= EPMOlapMemberO($B$5,”[CATEGORY].[].[ACTUAL]”,”ACTUAL”,””,”000″)
In B15 (Left third column key) I put a formula: =$B$3&$B$4 and it was recognized as 2012.MAR with the the variable member formula:
= EPMOlapMemberO($B$3&$B$4,”[TIME].[PARENTH1].[2012.MAR]”,”2012.MAR”,””,”000″)
8. Creating row key:
In A19 (Top row key) I put 1 member of ACCOUNT dimension: 0 and it was recognized as “0 – NET INCOME / (LOSS)” with the fixed member formula:
= EPMOlapMemberO(“[ACCOUNT].[PARENTH1].[0]”,””,”0 – NET INCOME / (LOSS)”,””,”000″)
The report was created automatically and I got some value from the model in B19.
9. Creating page axis keys:
In A1 I put a formula =$B$8 (link to ENTITY member BE1000) and it was recognized as BE1000 with the variable member formula:
= EPMOlapMemberO($B$8,”[ENTITY].[PARENTH1].[BE1000]”,”BE1000 – OOO “”United Press”””;””;”000″)
It was automatically recognized as a first page axis member for this report.
In A2 I put a formula =$B$9 (link to CORPDIR user def dimension member ALL) and it was recognized as ALL with the variable member formula:
= EPMOlapMemberO($B$9,”[CORPDIR].[PARENTH1].[ALL]”,”ALL – All Data”,””,”000″)
It was automatically recognized as a second page axis member for this report.
In A3 I put a formula =$B$10 (link to TITLES user def dimension member 1110) and it was recognized as 1110 with the variable member formula:
= EPMOlapMemberO($B$10,”[TITLES].[PARENTH1].[1110]”,”1110 – Men’s Health Russia Norm”,””,”000″)
It was automatically recognized as a third page axis member for this report.
10. I opened Edit Report and renamed it as REP1 (not required).
11. In A20 (Next row key) I put another member of ACCOUNT dimension: 2 and it was recognized as “2 – NET INCOME / (LOSS) BEFORE TAX” with the fixed member formula:
= EPMOlapMemberO(“[ACCOUNT].[PARENTH1].[2]”,””,”2 – NET INCOME / (LOSS) BEFORE TAX”,””,”000″)
This step can be repeated number of times to add all necessary ACCOUNT members.
The axis members can be also selected using row axis member selector.
The row axis can be set dynamic if you set member in row axis member selector as Context and some option like Member and Descendents. Then, if you change context member and refresh the report the row axis will be expanded in line with context.
You can also use row axis dimension override with the function like =EPMDimensionOverride(“000″,”ACCOUNT”,”BAS(2)”) in any cell outside report.
12. Then I started adding second report. Selected connection for the second report (second Model).
13. I have to use New Report button. Named the report as REP2. In “Column Axis Shared with” I selected REP1. I put ACCOUNT dimension ADV in the “Row Axis Dimensions” list and selected one member (PAGESTOT) of this dimension. The page axis remained empty for the moment. After OK the report was created.
14. Switch off automatic member recognition.
15. In cells A4, A5 I prepared formulas for page axis of the second report (the page axis of the second report has less dimensions then first report):
A4: =$B$8 (link to ENTITY member)
A5: =$B$10 (link to TITLES user def dimension member)
I can’t use cells A1 and A3 used for the page axis of the first report. A4-A5 will be page axis for the second report.
16. I selected cell A21 to be inside REP2
17. Open “Edit Report” and put ENTITY to the “Page Axis Dimensions” list, adjusting the reference to the cell A4, then TITLES to the “Page Axis Dimensions” list, adjusting the reference to the cell A5. After OK the formulas in A4 and A5 were replaced by the variable member formulas:
A4: = EPMOlapMemberO($B$8,”[ENTITY].[PARENTH1].[BE1000]”,”OOO “”United Press”””,””,”001″)
A5: = EPMOlapMemberO($B$10,”[TITLES].[PARENTH1].[1110]”,”Men’s Health Russia Norm”,””,”001″)
18. Switch on automatic member recognition.
19. In A22 (Next row key) I put another member of ADV dimension: PAGESEDT and it was recognized as “PAGESEDT – Editorial PAGES” with the fixed member formula:
= EPMOlapMemberO(“[ADV].[PARENTH1].[PAGESEDT]”,””,”PAGESEDT – Editorial PAGES”,””,”001″)
This step can be repeated number of times to add all necessary ADV members.
The row axis for the second report can also remain dynamic and the row axis members can be also selected using context and row axis member selector (see above).
20. Adding more columns to the report (I need only specific combinations of MEASURES, CATEGORY and TIME). Copy range B13:B15 to C13:C15. Replace links in formulas (to point to another required members):
C13: $B$1 with $B$2 Result after refresh: = EPMOlapMemberO($B$2,”[MEASURES].[].[YTD]”,”Year To Date”,””,”000;001″)
C14: $B$5 with $B$6 Result after refresh: = EPMOlapMemberO($B$6,”[CATEGORY].[].[BUDGET]”,”BUDGET”,””,”000;001″)
C15: $B$3&$B$4 with $B$3&”.TOTAL” Result after refresh: = EPMOlapMemberO($B$3&”.TOTAL”,”[PERIODS].[PARENTH1].[2012.TOTAL]”,”2012.TOTAL”,””,”000;001″)
More columns can be added the same way.
21. Switch off automatic member recognition.
22. Then I can insert rows and columns, use any Excel formulas and texts in the inserted rows and columns, apply Excel formatting etc. With EVDRE in BPC 7.5 it was possible to put one one ERVDRE data range inside the data range of another EVDRE (For example to have at first rows from one EVDRE, then rows from another EVDRE, then back rows from first EVDRE). In BPC NW 10 in this case you have to use 3 reports.
The result is shown on the picture:
Yellow – report parameters, blue – shared column axis, orange – first report, green – second report. You can change values in the yellow cells and after refresh you will see correct results in the report. You can use =EPMSelectMember(…) in yellow cells or any other formula.
23. You can insert column local members with formulas to support dynamic row axis:
You have to use Attached to Column Axis and Insert at Position (here at position 2). The formula have to be simple Excel reference to the top column cell.
The result is:
Local member in red.
The overall procedure looks much difficult then the usage of EVDRE and requires strict order of steps.
If the user will change column axis members in Report Layout -> Column Axis, then the whole report will be corrupted – column axis expansion will happen!
B.R. Vadim
**** Updated on 18.12.2014 member selection
**** Updated on 11.04.2014 EPM SP17 (Dynamic row axis, EPMDimensionOverride, Local Members)
**** Updated on 18.03.2014 EPM SP16 patch 1
Hello Vadim,
thanks a lot for this blog about EPM vs EVDRE - reporting.
Unfortunately I have no idea to simplify your report.
I'm a "heavy" EVDRE() developer for years and tried to convert / redevelop complex
dynamic and statis reports last year after a migration to BPC 10. It's wasn't possible
last year, and I gave up. So I'm still using EVDRE() in the EPM Client with very good performance.
At the next DSAG - meeting for BPC in april I will fight again for the EVDRE() - functions in EPM-Client. Perhaps you can help me in this topic.
Kind regards
Jochen
Hi Jochen,
That was the idea of my post - to show that for the ordinary finance user it was possible to create a nice looking report with BPC 7.5 with minimum efforts and with the understanding of the syntax of the few EV functions. With BPC 10 native instruments it's close to impossible (especially for multireport sheet). Even for me it takes time to correctly follow the steps described in my post.
For sure we can continue usage of EVDRE in BPC 10, but I have some concerns about EVDRE performance on HANA with a new HANA MDX engine.
B.R. Vadim
Vadim, Very good effort you made and thank you very much for sharing the same with us.
Hi All,
my best guess to preserving report definitions in a similar case as mentioned here is to use the EPMAXISOVERRIDE. Whenever the number of combination is limited, it might help to preserve report definitions..
Kind regards, christophe
Hi Christophe,
The EPMAXISOVERRIDE is useless for asymmetric static report (single report)! In your sample I see 2 reports, but in the real life it will be more reports to create - sample:
But starting from some SP the asymmetric static report is more or less stable, you can make static only column axis with dynamic rows, you can add new reports without issues with existing.
B.R. Vadim
Hi Vadim,
that's true. If too much separate reports need to be build, you have to stick to your method, which I also use frequently due to it's flexibility.
given your report, I just want to add, that I once (on a BPC 10 MS) created a new measure "Variance" to compare 'selected category from context' against actuals of prior year (periodically here), which may be a slightly different approach allowing you to eliminate the time from the columns axis..
IIF([%TIMEDIM%].CURRENTMEMBER.PROPERTIES("LEVEL")="MONTH"
,IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC"
,SUM((PARALLELPERIOD([%TIMEDIM%].[MONTH],12,[%TIMEDIM%].CURRENTMEMBER),[CATEGORY].[H1].[ACTUAL]),MEASURES.[SIGNEDDATA])-MEASURES.[SIGNEDDATA]
,SUM((PARALLELPERIOD([%TIMEDIM%].[MONTH],12,[%TIMEDIM%].CURRENTMEMBER),[CATEGORY].[H1].[ACTUAL]),-MEASURES.[SIGNEDDATA])+MEASURES.[SIGNEDDATA]
)
,IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC"
,SUM(([%TIMEDIM%].CURRENTMEMBER.PREVMEMBER,[CATEGORY].[H1].[ACTUAL]),MEASURES.[SIGNEDDATA])-MEASURES.[SIGNEDDATA]
,SUM(([%TIMEDIM%].CURRENTMEMBER.PREVMEMBER,[CATEGORY].[H1].[ACTUAL]),-MEASURES.[SIGNEDDATA])+MEASURES.[SIGNEDDATA]
)
)
Hi Christophe,
Unfortunately custom measures involving different categories in BPC NW have some issues and are very slow. Also, if you have to compare a lot of categories (in our case we have ACTUAL, BUDGET, STRATPLAN, EST1,...,EST11 and drafts) then you will have to maintain a lot of custom measures. It's much easier to have a static report with compare by Excel formula.
Vadim
Hi Vadim,
In the NW there are indeed issues with this approach, that's why I explicitely mentioned the MS version 🙂 .
As long as you can compare them against 1 category, this single measure applies. It compares the selected category against actuals prior year. Off course, if they should be compared against one another (not explicitely the actuals), then the best option is to stick to your excel.
I wanted to mention it to broaden the discussion by showing that a different approach in some cases might be worth considering.
Thanks for your insights.
Thanks for sharing the information....!
Vadim,
Thanks for the blog.I am trying to use epmolapmember formula and refer to other cell for the value. like = EPMOlapMemberO($B$8,"[ENTITY].[PARENTH1].[BE1000]","OOO ""United Press""","","001") your example above. but once I use back(undo) button on epm ribbon, this cell reference $B$8 is wiped out. how can i stop this happening.Have you encountered this issue before.please advise.
Thanks for you contribution.
Hi Sailu,
why are you using undo button after changing the formula?
Regards,
Harshit
Harish,
The report is saved with the epmolapmember formula with excel refrences for page axis members. After opening the report, user navigate to do some expand, collapse, keep , exclude. when he tries to go back to the original state, he is using undo button. Then all the excel references in the page axix epmolapmember formulas are wiped out .
Best regards,
Sailaja
very helpful, thanks for the sharing knowledge
Hi,
If I try to change my column axis in edit report, then column axis expansion takes place as mentioned by you at the bottom of your post.
Is there any way in which I can edit columns in my report while my Category is linked to excel cells?
If you have some dimension in the axis as static (using reference in epmolapmembero formula) then all other dimensions for this axis has to be also static.
Hi
I built a dynamic custom report that allows me to change the cost center dimension, all other elements are static.
The report works on my laptop but when i send it out to colleagues after they select the cost center there is no data showing in the table.
Please HELP! Is it an access issue?
Please open a new discussion, provide a report screenshot and detailed explanation.
Hi Vadim,
Thanks for the great article ..
I have a query regarding a static report which is used as an input template.
The number of rows in my static input template are fixed say for ex., 10 rows . but the user can enter data for any number of rows for ex., say one user can enter 5 rows in the text field and other user may enter only 3 rows .
In such cases the first 5 rows gets saved successfully.
Then the second user deletes those 5 rows and enters data for only 3 rows in this case what happens is that the the last 2 rows dimension selections are retained by the input template . Is there a way to clear those dimension values to be blank ?
Simply don't allow user to delete full rows (use protection on the template). Let user to delete data values, but not rows! Cleared cells will send zero's to the model.
Thanks Vadim for the prompt reply … i guess I was not clear enough, the users DO NOT delete the rows.. they just clear the cells only ..but the report still takes the dimension value which was selected before the cells were cleared.
Sorry, but this is not possible. Test yourself on the simple report and you will see result.