Skip to Content
Technical Articles
Author's profile photo Steffen Heine

Build asymmetric reports with SAP Analytics Cloud, add-in for Microsoft Office

With the latest version of the SAP Analytics Cloud, add-in for Microsoft Office which was released on October 6th, 2022 Microsoft Excel users are now able to use a new function SAP.GETDATA. This function returns the data value for a specified set of dimension and member combinations. This enables users to build asymmetric reports or blend data from several data sources e.g. SAP Analytics Cloud, SAP Data Warehouse Cloud or SAP S/4HANA Cloud into one Excel sheet. Additionally all Microsoft Excel formulas and formatting options can be used.

The syntax of SAP.GETDATA is the following:

SAP.GETDATA(Data Source, {<Dimension>, <Member>}*).

  • The data source refers to a table which needs to be inserted on any sheet
  • For dimension and members their IDs need to be used. It is possible to write them directly into the formula e.g. dimension “Time” with member “2022” or refer to other cells which contain the ID value.

Example: SAP.GETDATA(“Table1”,”G/L Account”,”Revenue”,”Time”,”2022″,”Version”,”public.Actual”)

 

Build an asymmetric report:

The following steps show how function SAP.GETDATA can be used to create a report which shows actual and forecasted values for month September 2022 on the left side of the G/L Accounts dimension and the Year-to-Date aggregation on the right side. It is based on one SAP Analytics Cloud model with dimensions G/L Account, Time and Version among others.

1) Insert the table:

First the SAP Analytics Cloud model needs to be added as a table. It helps to take the target dimensions G/L Account, Time and Version into the drill and copy their IDs for the later usage within SAP.GETDATA.

Table%20as%20Data%20Source

Table as Data Source

2) Design the report structure:

Report%20structure

Report structure

  • Cell B1 contains the table name (Table1) of step 1. It can be written into the cell or derived with the function SAP.GETTABLENAME.
  • Row 8 and 9 contain the relevant ID values for the Time (202209 for September and 20221-20223 for the quarters) and Version members (public.Actual and public.Forecast).
  • The three columns (C:E) represent the actual and forecast values including a difference for the current month (here September 2022).
  • In the middle (G:H) are the G/L accounts with ID and description.
  • Then there is the Year-to-Date actuals column (J), followed by four columns for the forecast. One (K:M) for each quarter of 2022 as the Q4 forecast values are already within the data model and should not be considered in the aggregation. Column N sums up the Year-to-Date forecast value and the last column (O) is again the difference between actual and forecast.

 

3) Usage of SAP.GETDATA:

Let’s finally use the SAP.GETDATA formula. It is possible to write all IDs into the formula, e.g.: =SAP.GETDATA(“Table1″,”G/L Account”,”FPA1/016″,”Time”,”202209″,”Version”,”public.Actual”), but for better dynamic refer to the IDs in the grey colored cells.

SAP.GETDATA

SAP.GETDATA

 

Drag the formulas across the cells:

SAP.GETDATA

SAP.GETDATA

4) Use Excel formulas and formatting options:

To finish the report hide the grey colored columns/rows with the member IDs. Calculate the differences between actuals and forecast and format the cells.

Final%20report

Final “butterfly” report

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jan Vereecken
      Jan Vereecken

      This enables users to build asymmetric reports or blend data from several data sources e.g. SAP Analytics Cloud, SAP Data Warehouse Cloud or SAP S/4HANA Cloud into one Excel sheet.

       

      Is this also working for BW/4 ? I would expect yes ... Strange that this isn't mentioned . Seems to be a SAP directive Do Not Mention BW/4 ....

       

      Jan Vereecken

      Author's profile photo Steffen Heine
      Steffen Heine
      Blog Post Author

      Hi Jan,

      there is currently no support of BW/4. It might be added to the roadmap https://roadmaps.sap.com/board?range=CURRENT-LAST&PRODUCT=73555000100800001621.

      For BW/4 the recommended add-in remains SAP Analysis for Office which offers this formula as well.

      Regards,

      Steffen

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Hi Steffen,

      What is the recommended Excel add-in for Hana Cloud?
      Because SAC add-in doesn't seem to support Hana Cloud and AFO only with local IP connections is not OK.

      Author's profile photo Steffen Heine
      Steffen Heine
      Blog Post Author

      Hi Jef,

      currently only via AFO and the Analytics Adapter, but strategically the SAC add-in. We haven't received much asks about SAP HANA Cloud for the SAC add-in and therefore didn't prioritize it yet. I created an influence item, so please vote for it: https://influence.sap.com/sap/ino/#/idea/290532

      Regards,
      Steffen

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Great addition, will really help!
      Hope to see SAP.SetData() formula soon as well to support planning use cases.

      Author's profile photo Steffen Heine
      Steffen Heine
      Blog Post Author

      Hi Jef,

      thats already in the works and on the roadmap: https://roadmaps.sap.com/board?PRODUCT=73555000100800001621&range=FIRST-LAST#;INNO=000D3AAADBCE1EECB5BE26D0F0B3C6FF

      Regards,

      Steffen

      Author's profile photo Amrita Goswami
      Amrita Goswami

      Thanks for the informative blog Steffen!

      I had a few questions on the Report. Have you used formulas in Rows 8,9 (the grey colored cells)?

      What is the formula you have applied to get the YTD Numbers? Assuming I have data till December in the model and I want to show YTD for October in the report, will the formula have to be hardcoded based on Quarter ?

       

      Thanks,

      Amrita