Formula Variables Offer a Low-Cost Alternative to Cell Definition in Financial Reports

  • by Soumya Mishra, Managing Consultant, IBM Corporation
  • April 16, 2009
Find out how you can use formula variables with hierarchies to define individual cells in a report. This six-step process offers a low-maintenance alternative to developing custom query structures in BEx Query Designer, a pre-requisite for the cell definition feature.
Key Concept

Variables are query parameters that you define in BEx Query Designer. They are filled with values when you execute the query or Web application. Formula variables represent numeric values that you can use in formulas. In addition, you can also use formula variables to select conditions and exceptions.

Often companies need to produce balance sheets and income statements as part of statutory, regulatory, and management reporting requirements. In many SAP implementations, companies typically use SAP NetWeaver BW (formerly SAP NetWeaver BI) to produce these statements. Two widely used approaches to produce these statements are:

  1. Using structures in BEx Query Designer: This approach has high development and maintenance cost

  2. Using a general ledger (GL) account hierarchy in BEx Query Designer: This approach has low development and maintenance cost

Companies typically need to define individual cell in these reports, based on the individual hierarchy node. In approach (a), you create a query structure to build the GL account structure and key figures are part of a second query structure. Using custom GL account structures can result in high maintenance costs in global implementations. For example, if you need to add a new structure element or GL account to the query structure, you need to make the changes in all the affected SAP NetWeaver BW queries.

Subsequent rollout to other countries causes the maintenance cost to increase even further. As you add more structure elements to the query structure, it becomes challenging to keep the SAP ERP Central Component (SAP ECC) Financial Statement Versions (FSVs) and SAP NetWeaver BW query structures in sync to avoid reconciliation issues.

However, in approach (b), you use a GL account hierarchy instead of a query structure to create the GL account structure. This allows you to avoid the fixed structures from approach (a). Changes to the reporting structure are handled in the SAP ECC FSV, which is extracted as a GL account hierarchy. No changes to the individual query structure are required. Because the changes originate from the FSV in SAP ECC, reconciliation between SAP ECC and SAP NetWeaver BW is not a major challenge. There is no standard way to define an individual cell based on hierarchy nodes, so I developed a way to handle this requirement using formula variables while still following the cost-effective approach (b). I’ll show you the step-by-step approach I used with SAP NetWeaver BW 7.0.

Soumya Mishra

Soumya Mishra is a senior BI practitioner with a total of eight years of experience in the SAP BI space. He has been continuously focused on developing business enabling analytic strategies and solutions. He has extensive enterprise analytics assessment knowledge and has successfully played a key role in delivering large-scale finance transformation solutions that deliver actionable information and insight to users, giving them the ability to make informed decisions. He has deep knowledge of BI, and SAP ECC FI/CO configuration, as well as experience with implementations in several Fortune 500 companies in the consumer products and financial services industries. He is a certified SAP BI solution consultant with deep experience in development and integration of BI strategy; process redesign with information systems; and project planning and implementations with strong communication, organizational, and project management skills. You can view his LinkedIn profile at http://www.linkedin.com/profile/view?id=50199012&locale=en_US&trk=tab_pro.

See more by this author


Comments

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.