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

Build asymmetric reports with SAP Analytics Cloud, add-in for Microsoft Office (GETDATA and SETDATA function)

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.

Update: With the release 2023.13 (May 31, 2023) it is also possible to write-back data into SAP Analytic Cloud planning-enabled models via the function SAP.SETDATA.

 

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

The syntax of SAP.GETDATA is the following:

  • 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”)

 

SAP.SETDATA(Data Source, Value, {<Dimension>, <Member>}*). [Added in June 2023]

The syntax of SAP.SETDATA is similar to GETDATA, but additionally the value which should be send back to the model needs to be specified on the second position. The number value can be directly entered into the formula or refer to a different cell in the Excel workbook as a reference. The latter enables building flexible input sheets in an own look and feel which could also be used offline before submitting the values.

Example: SAP.SETDATA(“Table1”,A33,”G/L Account”,”Revenue”,”Time”,”2023″,”Version”,”public.Forecast”). In this case the cell A33 contains the input value for the forecasted revenue in 2023. When the number in A33 changes, the button Process Data in the toolbar becomes active to submit the data.

When using hierarchies, currently SETDATA is only possible on leaf level for the dimensions used in the function.

 

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

      16 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

       

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

      Hi Amrita,

      Rows 8, 9 I pasted as text into the cells. YTD for the Forecast is unfortunately only possible as the aggregation of individual months which I then hided. Actuals might be easier when the future values of the year are 0/Null.

      Regards,
      Steffen

       

      Author's profile photo Daniyar Iskakov
      Daniyar Iskakov

      Hi Steffen,

      Thank you for such a great blog!

      I have a few questions regarding Input/Reports templates created using the SAC add-in for Excel:

      1. How we can give access to this template to other SAC Users (Something similar to BPC public folder)?
      2. Can give access to created Template using the Calendar Functionality (currently we can only add Stories and files)?

       

      Regards,

      Daniyar

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

      Hi Daniyar,

      currently sharing of templates works only with Excel native functionality.

      We are working on a better integration with SAC.

      Regards,
      Steffen

      Author's profile photo Shashi Paleti
      Shashi Paleti

      Hi Steffen,

      If the SAC add-in excel templates can be saved to the SAC folders, that would be great. If we are able to read the data source (model) into template from the folders, why can't we able to store the template to them?

      Thanks,

      SP

      Author's profile photo Krishna P
      Krishna P

      Hi Steffen,

      Informative Blog.

      We are not able to see SAP.GETDATA function in "SAP Analytics Cloud Microsoft Add in" . Is there any additional step needs to be performed to view "SAP Analytics Cloud" category  in Insert function option in excel.

      After I click on  " Add Data Source for formulas"  , SAP.GETDATA() function gets added , and displays #NAME in the cell.

      Regards,

      Krishna

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

      Hi Krishna,

      please check the syntax of the formula again. If it is still not working, please open a customer incident.

      Regards,

      Steffen

      Author's profile photo Sachin Prabhudesai
      Sachin Prabhudesai

      SAP.SETDATA  was long awaited addition.  Thanks for sharing the blog.

      Author's profile photo Vaishali Shetti
      Vaishali Shetti

      Hi Steffen,

      Does this SETDATA support reading the dimension combination from un-booked cells?
      Thanks in advance.

       

      Thanks
      Vaishali Shetti

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Yes, it works on unbooked cells.
      Great for asymmetrical planning, but not so performant on very large nr. of cells & functions.