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 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