SAP Analysis for Office - Data source for formulas SAPGetData

[former_member628386]

Here is the summary of what is covered in this article:

• Introduction and Inserting formula data source.


• How to define SAPGetData formulas in Analysis for office.


• Usage of SAPSelectMember formula in SAPGetData.

Introduction and Inserting formula data source:

Steps:

• Open Analysis for Office and login to the server (client system)


• Goto Insert Data Source and choose "Select Data Source for Defining Formulas" (Refer to the below screenshot)

• Provide the technical name and click on insert, once you insert the query you can able to see the available dimensions.


• Here I used some standard Query (refer to the below screenshot)

Note: By default, the above data source technical name is DS_1, we can insert multiple data sources and use them, for example, if we insert another data source it indicates DS_2( You can see technical names in the Components tab in the above screenshot)

How to define SAPGetData formulas in Analysis for office.

Steps:

• Got to excel and provide the below formula in any cell.


• =SAPGetData(DS_1,AmountInGroupCurrency,"2CICNSLDTNRITEM",Revenue,"2CIMCRPTENHANC-FISCALYEAR",$H$11)


• Here Formula indicates:
  
  
  
  • DS_1 is the Data source
  
  
  • AmountInGroupCurrency is the measure
  
  
  • 2CICNSLDTNRITEM is the Dimension and the value is "Revenue"
  
  
  • 2CIMCRPTENHANC-FISCALYEAR is the dimension and the value is referring to the excel H11 cell using the SAPSelectMember formula.
  
  
  
  

Note: click on the formula button "fx" in excel to see the popup box in the above screenshot.

Usage of SAPSelectMember formula in SAPGetData.

Steps:

• Got to excel and provide the below formula in any cell (I'm using H11 here because I referred to H11 in the SAPGetData formula).


• 
  
  =@SAPSelectMember(DS_1,"2CIMCRPTENHANC-FISCALYEAR{pres=Internal_Key}=K42021","2CIMCRPTENHANC-FISCALYEAR","KEY","SIMPLE")
  
  
  


• DS_1 is the Data source


• Member can be blank, the above value is automatically set when you click on the filter symbol and select a value.


• 2CIMCRPTENHANC-FISCALYEAR is a dimension


• We can display it as KEY or Text


• Selector mode can be SIMPLE by default.

Pros & Cons:

Pros:

• We can create customized reports using the SAPGetData formula.


• Can use restrictions, drop-down selections & advanced calculations using various formulas

Cons:

• Performance issues if you are using more formulas in multiple tabs using a single data source


• Hierarchy values are inconsistent but we can get the values.

Now that you know about SAPGetData in SAP Analysis for Office. How about discovering more about SAP Analysis for Office? Explore help.sap.com to learn more.

Thanks for reading this article, hope you will understand the formulas, please write or comment to me back with any queries.