Need to calculate a grand total dynamically? Shifting of Local Members giving you a headache? I previously used
Indirect() functions, but I discovered with larger reports this put a drag on reporting performance. To sum from the first report column to the last report column I was using something like this =SUM(EPMPOSITION(1):INDIRECT(ADDRESS(ROW(),COLUMN()-1))), which is not considered best practice but I got results!
The SAP definition: “EPMALLMEMBERS is used to perform a calculation (such as sum or average) on all the members displayed in a row or column axis, whether the axis contains one or several dimensions”
HOW? Here are some examples:
- You can create a local member with SUM(EPMALLMEMBERS), which also works when there are multiple dimensions in the rows.
- You can also use it in calculations such as = SUM(EPMALLMEMBERS)+$A$1.
- Another example is to commit the sum to a specific column =IF(COLUMN()>=15,SUM(EPMALLMEMBERS)+$A$1),"").
- An example from SAPs documentation for when we are using various periods. To calculate the sum for time periods use SUM(EPMALLMEMBERS(TIME)).
Related post: Formatting in SAP BPC
For tips like these and much more, download our free eBook entitled "Top 10 SAP BPC Reporting Tips"!
Related Articles:
Blog Post: How to Supress Send Dialog Box in SAP BPC
Blog Post: SAP BPC Reporting Tips & Tricks
Blog Post: Using the Power of Visual Basic in EPM 10 Reporting
Blog Post: Tax Reporting Just Got a Whole Lot Tougher! (Thanks to the OECD)
Customer Roundtable Webcast - BPC Performance Issues
Author Bio:
Deborah Silverman has been with Column5 Consulting for almost 5 years as a Senior Consultant. She is one of our most popular blog writers with her decades of software experience. She has assisted in hundreds of implementations and specializes in SAP BPC.
