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)).
For tips like these and much more, download our free eBook entitled "Top 10 SAP BPC Reporting Tips"!
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.Deborah Silverman, Senior Consultant, US Team