Smart Ad Hoc Reporting in EPM BPC 10
This document highlights the key benefits available in BPC 10 reporting and details more on the smartness it brings in BPC reporting space.
SAP in its latest version for Business Planning and Consolidation has brought in significant changes in two spaces compared to its previous versions. Like in,
- System Architecture and
- System Reporting
BPC in its earlier versions was matured enough in its reporting and was able to cater most of the business requirement with its EV functions and native Excel functions. It’s not a simple decision to replace a matured reporting tool with a new tool.
Having said that, it is necessary for all BPC consultants to understand the key benefits BPC 10 Reporting is bringing in, compared to what was available in the former versions.
In BPC 10 a user can build Excel reports via three options,
- EPM – Report Editor
- Drag and Drop Reporting in EPM Pane
- Ad Hoc Reporting
EPM – Report Editor and Drag and Drop Reporting options are something similar to what we had in previous version of BPC.
Ad Hoc Reporting is the one which draws more attent
ion in BPC 10. This is completely a new feature which is made available for reporting and it benefits all BPC users (Developers and End Users) in performing quick reporting based on their needs.
This approach has two prerequisites,
- Enable Member Recognition option in SheetOptions of EPM Tab
- Knowledge on the Dimensions and its member ID’s or Descriptions
Member Recognition is specific to a Worksheet and has to be enabled in any worksheet the ad hoc report needs to be built.
Building basic Report with Ad Hoc Approach,
To start with this option of building reports,
1. Enter the ID of any dimension member(Here as an example – Account member) for which the report needs to be built and click enter – This will create a EPMOLAPMEMBER formula for the ID entered
Ad-Hoc reporting – Possible Cases:
1. When a Dimension ID is entered in a new Row Axis (Column), it checks if the dimension is duplicated in in the column(Row) axis.
- If it is not duplicated the report updates it with the new row axis for the dimension entered
- If it is duplicated in column axis, the duplication of the dimension ID will be highlighted as in the error below.
2. When a Dimension ID is entered in the existing axis of a report, it checks for the value with the members of the dimension in the axis,
- If the ID entered matches exactly with any member in the dimension, it will be added as a member in the existing report
- If the ID entered doesn’t match exactly with the existing members of the dimension, a search will be done automatically based on the value entered, on the dimension available in the report axis
3. When a dimension ID of a dimension which exists in the report is entered in the same axis(Row/Column), it doesn’t recognize the entry and defaults to the old report structure leaving the ID entered as just a value in the cell
Other Special Reporting Features:
A report layout built using Ad Hoc approach or any other approach can be tweaked to view the data in different format and also few changes can be made ad hoc using few standard functions available like,
- Switch Axes
- Insert Members
- Refresh Selected Data
This option switches the dimensions in the Row to Column and Dimensions in Column to Row. Below screen shows how this converted a report with Account in Row and Time and Currency in Column to a report with Account in Column and Time and Currency in Row
Insert Member option is used in adding members in the report as and when required in the required position. This can be used when the user is not comfortable remembering the ID’s in the system.
Refresh Selected Data,
This option enables the user to refresh data only in the selected cells. If the report retrieves 1000 records and the user wanted to refresh data in certain selected cells, this approach can be used. This enables quick data refresh based on our actual need, rather bringing all data based on the report layout.
Ad hoc approach of creating reports is very simple and helps users in build quick reports. Still it has certain limitations like,
- Building ad hoc Butterfly type reports is not possible
- Building ad hoc multiple reports sharing a common Axis is not possible
The basic Ad hoc report built can be enhanced in building butterfly or shared axis reports using EPM Report Editor Option.
Ad Hoc reports and other new features discussed above on the whole are very useful for,
1. BPC developers, working on Logic Script, BADI’s, and other Data Loading functions to do quick check on data in the system after the calculation.
2. EndUser perspective it will be more useful to check their relevant data more quickly rather spending time in opening the report which was built before which has many irrelevant data.