Technical Articles
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:
Table as Data Source
2) Design the report structure:
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
Drag the formulas across the cells:
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 “butterfly” report
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
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
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.
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
Great addition, will really help!
Hope to see SAP.SetData() formula soon as well to support planning use cases.
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
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
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
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:
Regards,
Daniyar
Hi Daniyar,
currently sharing of templates works only with Excel native functionality.
We are working on a better integration with SAC.
Regards,
Steffen
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
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
Hi Krishna,
please check the syntax of the formula again. If it is still not working, please open a customer incident.
Regards,
Steffen
SAP.SETDATA was long awaited addition. Thanks for sharing the blog.
Hi Steffen,
Does this SETDATA support reading the dimension combination from un-booked cells?
Thanks in advance.
Thanks
Vaishali Shetti
Yes, it works on unbooked cells.
Great for asymmetrical planning, but not so performant on very large nr. of cells & functions.