Skip to Content
0
Former Member
Jul 28, 2016 at 07:58 AM

EPM Adhoc reporting options

398 Views

Hello,

We are looking at giving our users the freedom of creating their own adhoc reports where each row could be at a different aggregation level or combination of dimension members.

For example, one row can show Cost center node 1 and GL account node 1 and the next row can show Cost center node 2 and GL account node 2 but with BEx/AO, users will get a view with additional rows/columns which they do not wish to see.

Required view:

2016

2016.Q1

Budget

Forecast

Cost center node 1

GL account node 1

100,000

2,000

Cost center node 2

GL account node 2

200,000

3,000

View with standard AO/BEX reporting:

2016

2016.Q1

Budget

Forecast

Budget

Forecast

Cost center node 1

GL account node 1

100,000

110,000

2,500

2,000

GL account node 2

150,000

145,000

1,500

1,000

Cost center node 2

GL account node 1

500,000

505,000

1,750

1,500

GL account node 2

200,000

201,000

2,800

3,000

In addition to above, the requirement is to have ability to report using formula functions so that they can link their worksheets with reports to have consistent views for formatted reports. Our source includes a host of real time as well as standard infoproviders. We have looked at SAPGetData and EPMRetrieveData as options.

1. Use of SAPGetData formula:

If such report is built using SAPGetData formula, there must be a query in the workbook (not just connection without generating output) and its output must contain exact intersection of members, which means that query will contain huge set of data for real adhoc query building. This significantly impacts the performance.

Impact: Business users will not be able to do ad-hoc reporting using formula, for reports based on different aggregations within each dimensions. It will be challenge from performance and query expansion point of view for business users.

From SAP AO user guide:


2. Use of EPMRetrieveData formula:

Such reports can also be built using EPMRetrieveData formula. To do this, we need to use the BPC Web interface and add the real-time cube as a model in BPC Embedded environment. We also need to create BEx queries and connections to those queries in the excel add-in.


We have following queries for using EPM add-in:

  • The advantage of using EPM add-in is that we do not need to generate a report output to connect with the query connection but if the EPM workbooks are saved on the server, users will always need to connect to a query first while providing the selections, to reach to that workbook.
  • The challenge we face while using EPM formula for this is the uniqueness of master data (member IDs). Since the embedded planning system is using the master data loaded from ECC system and there is a roadmap to migrate to S4/HANA in future, we do not want to change the master data IDs with any prefixes to make them unique. A possible solution can be using Dimension Name along with the members (Dimension|Member ID).
  • We are looking for assurance that utilizing EPM add-in for such reporting requirements on top of an embedded planning environment is supported and recommended solution from SAP.

Any other possible solution is also welcome!

Server Patch Details are attached.

Thanks,

Swathi

Attachments