For most organizations, profit and loss (P&L) reports are typically presented in a matrix format of rows and columns. In the rows of these reports, the definition can vary depending on the view that is desired. Financial analysts might use reports with rows of accounts. Corporate accounting staff might require P&L reports with organizational unit breakdowns. Sales and marketing analysts may work with reports that display rows arranged by customer or product hierarchy. The columns of these reports often include categories of key figures such as actual, budget, forecast, and time dimension (e.g., current or prior year) filters. Formula columns are frequently added to reports to show, for example, the percentage of total as a calculation for each row member. In the case of reports with the account dimension in the rows, this calculation could represent the percentage of sales or the percentage of net income. In the case of reports that contain rows of customers or products, this formula could represent a weighting or mix percentage.

A common practice used by report writers is to make report designs as flexible and reusable as possible, which means giving users the power to manipulate their displayed views without structurally changing the report definition. In SAP BusinessObjects Planning and Consolidation, this flexibility can be accomplished through use of member selection in the Current View and reusability can be aided by the control panel that is automatically created within each EvDRE report.

I will demonstrate the use of EvDRE reporting using SAP BusinessObjects Planning and Consolidation 7.0, version for SAP NetWeaver, and its delivered business content known as APSHELL, which has been copied into an application set called BEST_PRACTICES.

I will explain how to create a simple percentage of total report to take advantage of the flexibility and reusability of SAP BusinessObjects Planning and Consolidation whereby users can navigate to different levels of a dimension hierarchy or even change the row definition itself. Meanwhile, the report should be expected to still maintain a percentage of total formula that dynamically changes based on the user navigation or member selection and avoids the #REF! error that commonly occurs under certain combinations of report settings.

Example Scenario

Figure 1 shows the dimension member selections I have set in the SAP BusinessObjects Planning and Consolidation Current View. Figure 2 shows the SAP BusinessObjects Planning and Consolidation toolbar that I refer to throughout this article.

Figure 1

Example selections

Figure 2

Functions in the SAP BusinessObjects Planning and Consolidation toolbar

Begin with a blank SAP BusinessObjects Planning and Consolidation for Excel worksheet and input =EVDRE() into cell A1 and click the refresh workbook icon (Figure 2). In the BPC - EvDRE Builder pop-up window that appears, accept the default settings (Figure 3).

Figure 3

Accept the default settings

The resulting report shown in Figure 4 contains two expansions: one column expansion by the time dimension and one row expansion by the P_ACCT dimension. SAP BusinessObjects Planning and Consolidation only stores base member data in the database and any values shown in a report against parent members, such as the Total Costs line in this example, are calculated on the fly on data retrieval.

To demonstrate the common practice of adding a calculated % of total column, first adjust the EvDRE formula in cell A1 to remove the second expansion (Figure 5).

Then replace column expansion with row expansion by cutting cells C15:C21 and pasting them into B15:B21 so that the result is a report with row expansion only, while the columns remain static (Figure 6). Afterward, clear cells C14:C21 because they are no longer used in this example report.

Figure 6

Cut cells C15:C21 and paste them into B15:B21

Next, add a column between the existing columns J and K (Figure 7). Then add the formula =J14/$J$20 to the first line of the report (row 14), which calculates the percentage of total shown in row 20 using the $ symbol to set an absolute reference to the Total Costs line.

Figure 7

Add a column between columns J and K

By clicking the expand all icon shown in Figure 2, the new % of Total formula in cell K14 is automatically expanded down to the other rows. This capability is indeed one of the best features of reporting with SAP BusinessObjects Planning and Consolidation. Last, change the cell format to % and add a column title (% of Total) (Figure 8).

Figure 8

Add a % of Total column

Next I demonstrate the problem that can occur from defining formulas that reference dynamic total lines as shown in the previous example.

First, change the current view setting for P_ACCT to dimension member CE0004000 Personal Costs. Right-click the report, then expand and refresh the active worksheet by clicking EVDRE: Expand & Refresh > Active Worksheet (Figure 9).

Figure 9

Expand and refresh the active worksheet

The formulas in column K now show #REF! because the reference to cell $J$20 was lost during the expansion and refresh process in SAP BusinessObjects Planning and Consolidation and the new total line is on row 17 (Figure 10).

Figure 10

The new column shows #REF!

Fix the formulas in column K to now refer to cell $J$17 and then change the current view back to its original state with P_ACCT equal to CE0001000 (Figure 11). Then when you expand and refresh again, the original report is shown just the same as before. The expand-and-refresh process with this current view selection does not result in the #REF! error message.

Figure 11

This time, the column does not show #REF!

Why is this happening and which combination of settings in the report does or does not result in a #REF! error? More importantly, what can be done about the problem?

The answer is that this error can occur when a formula uses a reference to a dynamic total line, but it is lost upon member expansion when the navigation results in a smaller number of rows than previously displayed. Remember, in the first example, the total line moved from row 20 to 17. In contrast, the expansion formula continues to work and is not lost if the subsequent number of rows stays the same or is greater.

In the second example, the formula reference automatically adjusted from row 17 to 20. I will explain two possible workaround solutions that you can use to avoid the #REF! error. I will first briefly explain the MemberSet concept within the EvDRE function.

MemberSet in EvDRE

If SAP BusinessObjects Planning and Consolidation users want to change the level of a hierarchy displayed in the report, they have two main options. First, they can change the dimension member selected in their Current View. Alternatively, they can change the MemberSet selection(s) in the expansion parameters of the EvDRE control panel. In the report example above, the MemberSet is DEP,SELF. This setting tells the system to expand the rows first using the descendents of the dimension member selected in the report, then to display the selected dimension member itself. Therefore in this report all the P_ACCT descendents are shown for dimension member CE0001000 (Total Costs), which is also shown as a dynamic total line in the last row.

Workaround Solution No. 1

The #REF! issue described previously results when the total line moves up in a report because dimension member selection causes a reduced number of rows to be returned. It is possible to avoid this error by using a formula reference that is fixed, where the total line is on top line of the report, rather than dynamically moving when the total is at the bottom. You can do this simply by reversing the sequence of the MemberSet — changing DEP,SELF to SELF,DEP.

In this workaround, begin by changing the % of Total formula to reference row 14 instead of row 20. Then expand and refresh the report (Figure 12). Notice the CE0001000 Total Costs line now precedes its children due to the modification made to the MemberSet.

Figure 12

The Total Costs line precedes its children

With this workaround, it does not matter which P_ACCT dimension member is selected from the current view. The total line is held constant on the first line of the report because the MemberSet now reads SELF,DEP. It is possible to swap the P_ACCT dimension with any other dimension and the % of Total formula still calculates correctly after expanding and refreshing the new dimension. For example, Figure 13 shows the report with the entity dimension (an SAP BusinessObjects Planning and Consideration term similar to company code or profit center) in the rows instead of P_ACCT. Notice that both rows 14 and 15 are total lines and the % of Total formula worked as expected.

Figure 13

The Entity dimension is in the place of P_ACCT

The downside to this workaround solution is that it is purely dependent on the opinion of the report user. If the user can accept having report totals at the top of the report, then this workaround is sufficient. However, most users, especially of financial reports, expect total lines to be displayed at the bottom of the report. To achieve bottom-row report totals and at the same time avoid the #REF! error, you need to use the second workaround solution.

Workaround Solution No. 2

The second workaround is just as simple to set up as the first workaround. Begin with the original control panel settings where the row expansion is on P_ACCT and the MemberSet is DEP,SELF.

You need to separate the RowKeyRange into two separate ranges: One range is for the children dimension members and a second range is for the parent member of the given dimension. Do this by making the change shown in Figure 14.

Next, change the MemberSet to reflect two separate ranges in the RowKeyRange by separating the DEP,SELF values with a pipe instead of a comma (Figure 15). Then change the percentage of total formula to use row 20 once again by using the formula =J14/$J$20.

After making these small changes to the report definition, expand and refresh the report again (Figure 16).

Figure 16

Expand and refresh the report

I will not detail all the steps here, but I tested this second workaround by repeating the iterations I made previously by changing the current view and by changing the row expansion from dimension P_ACCT to ENTITY. Both of these scenarios result in fewer rows than previously displayed and this solution works correctly. The percentage of total formula worked as expected while avoiding the #REF! error.

Jeffrey Holdeman

Jeffrey Holdeman joined the SAP BusinessObjects Regional Implementation Group (RIG) for Enterprise Performance Management (EPM) in 2007. He works as a product specialist in SAP BusinessObjects Profitability and Cost Management and SAP BusinessObjects Planning and Consolidation. In March 2009, he celebrated with his customer the first ever go-live on SAP BusinessObjects Planning and Consolidation, version for SAP NetWeaver. Previously, Jeff worked for 10 years at SAP America, Inc. as a principal applications consultant where he was a key contributor and impact player in the success of nine customer SAP ERP/BW go-lives, including all aspects of project lifecycles. He is a nationally known expert in CO-PA and was the past national practice leader for SEM. Jeff has an MBA degree from The Ohio State University and a BS cum laude degree with high honors in finance from Butler University.

You may contact the author at jeffrey.holdeman@sap.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.