Static report in BPC NW 10

[former_member186338]

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