How to Sort, Group, and Subtotal Data with the EPM 10.0 Add-In
- by Charles "Tim" Soper, Senior Education Consultant, SAP
- November 21, 2013
See all the options to sort, group, and subtotal your data with the SAP Enterprise Performance Management (EPM) add-in in one central document.
After reading this article, you will:
- Know how to use all the sorting options in the SAP Enterprise Performance Management (EPM) 10.0 add-in interface
- See how the system sorts your data by default.
- Know that the report editor option allows you to sort by transaction data values
- Understand how to use the member sorting and grouping feature to create better reports
The SAP Enterprise Performance Management 10.0 Add-In interface includes the report editor sorting feature for sorting data based on transaction data values and the axis member sorting and grouping feature, which is more powerful than the report editor. With member sorting and grouping, you can sort data by member IDs or property values and you can also subtotal data. In addition to sorting based on member IDs, you can use member sorting and grouping to sort and subtotal by properties using local members, also known as a report-specific formula (e.g., the variance between budgeted and actual).
Imagine you are analyzing a report that has gross margin by profit center, and you want to see which profit centers had the highest to the lowest margins. Consider another scenario in which you need to analyze a report that has net earnings by company, and you want to sort the companies based on their local currency and then subtotal by currency. Both of these tasks could be performed in prior releases; however, they are much easier in the SAP Enterprise Performance Management (EPM) add-in interface 10.0. This is because there is a user interface to input the settings, whereas before all the settings and calculations were done in Microsoft Excel in earlier releases.
First, however, I explain how the system sorts members by default. I refer to this process as SAP Business Planning and Consolidation natural sorting. When you create a report with an entity in the rows and time in the columns, the members in the rows are displayed in the same order as they are stored in their dimension in the first hierarchy. The first hierarchy is the first one that is added to the dimension.
In this scenario, say that my first hierarchy is PARENTH1. To demonstrate the process described above, log on to the Web administration. From the Web client, choose Planning and Consolidation Administration and then select a dimension such as entity. Display the members in the Hierarchy view as shown in Figure 1.
Would you like to see this full item?