3402133 - Formula SAP.GETDATA returns value 0 for an aggregate node when using a data source for formulas

You inserted a data source for formulas in SAP Analytics Cloud, add-in for Microsoft Office. You then applied formula SAP.GETDATA to a hierarchy aggregate node, but the formula returns value 0. The correct result is returned when inserting the data source as a Table and applying the formula to the aggregate node in this table.

"Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental"

SAP Analytics Cloud

1. You connect your SAP Analytics Cloud, add-in for Microsoft Office to your tenant.
2. You insert a data source via "Add Function" > "Add a data source for formulas".
3. In an empty cell, you add your SAP.GETDATA formula, trying to retrieve the value for an aggregate node:
=SAP.GETDATA(DS_1,"DIMENSION","MEMBER")
4. Get value 0 as the formula return.

SAP.GETDATA formula only returns values for the members/nodes in the current selection. An aggregate node available in a hierarchy is not available in a Flat Presentation.

All members used in the SAP.GETDATA formula must be part of the current selection, regardless of this selection to be displayed in a Table (Add Table) or only in an optimized formula panel (Add a data source for formulas).

For example, if you added a data source for formula, a hierarchy exists for one of the dimension and you want to return the value of the aggregate node using SAP.GETDATA. It will return the correct value only if you apply the hierarchy to the given dimension, so that the node is available in the current selection.

Example:

The same data source is inserted twice in the worksheet. First using a table (Add Table) and then as a data source only (Add a data source for formulas). In the table, we apply hierarchy to dimension "Product", where we can see aggregate node "PC1" that sums (aggregates) the values of all the members under it. This value "123,800" will be correctly returned by the below formula:
=SAP.GETDATA("Table1","Product","PC1")

For the similar formula below
=SAP.GETDATA(DS_1,"Product","PC1")
the result will be value 0 if no hierarchy is applied to "Product":

The correct value will only be displayed if you apply the hierarchy on Product for DS_1 in the optimized formula panel:



If you check the Flat Presentation of dimension "Product", you'll see that aggregate node "PC1" is not available, so no value can be retrieved for it.

SAC Excel Add-in, SAC Excel, Excel Add-in, Addin , KBA , LOD-ANA-OF , SAP Analytics Cloud, add-in for Microsoft Office , How To