- SAP Managed Tags:
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:
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.
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.
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.
Drag the formulas across the cells:
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.
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
16 Comments
