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:
Any other possible solution is also welcome!
Server Patch Details are attached.
Thanks,
Swathi