Calculating Column Totals Dynamically - SAP BPC

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