Two Workarounds to Avoid the #REF! Error in Percentage of Total Formulas

  • by Jeffrey Holdeman, Product Specialist, SAP
  • July 14, 2008
If you are involved with reporting from SAP BusinessObjects Planning and Consolidation, you may have seen formulas that work until the expansion settings are changed and the result is a #REF! error message in Excel. Typically, this type of error can result when reports are defined with Excel formulas that show the percentage of total in a column based on dynamically changing row members. Follow a problem scenario with two workarounds for avoiding this error.
Key Concept

Percentage of total is a common reporting requirement achieved using Excel formulas that reference dynamic total lines in many SAP BusinessObjects Planning and Consolidation for Excel reports. If the reports are designed and used to flexibly expand the rows by different dimensions or by varying hierarchy levels within a given dimension member, then the % of Total formula can break and result in a #REF! error message. This error message can be avoided either by changing the MemberSet to display the total line on the top row of the report or by adding a second RowKeyRange and separating the MemberSet with a pipe instead of a comma.

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.

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.

See more by this author


No comments have been submitted on this article. 

Please log in to post a comment.

To learn more about subscription access to premium content, click here.