Analysis for Office -Workbook or Pre-Canned Report Development Formulae.

[narasingha]

Hello Friends,

Welcome back to my another blogpost on Analysis for Office -Workbook or Pre-Canned Report Development Formulae.

Little Background:-

As we all know being an BW developer we need to create Bex queries or AFO Queries (Majority of the Functional guys or Business people calls the same). Majority of the times many of us must have development requirement to create AFO workbooks on defined template or Pre-Canned reports which are ready with certain formatting standards.

These AFO workbooks are cannot be edited and they were designed as per the business requirement or agreed template so that business users do not need to slice and dice or sometimes we can link the AFO workbook in BOBJ as a document link(WB need to be saved in BOBJ a. The workbook is ready for the as per the dimensions, measures.

Of Course the business users have flexibility to chose the desired filter, run the workbook and save the workbook to their desktop.

We BW developers need to write few formulas which are available in AFO to make the report displayed as per the business need. In this blogpost we will mainly focus on those formulas. These formulas are known to us but sometimes after a gap we tend to forget and keep on searching on the internet for the right formula which may cause the development effort more. This blogpost will help a AFO WB report developer as a one stop information to develop the pre canned report or workbook in Minutes.

If You want to explore on Bex Query/AFO Reports Development tips/tricks & Performance Improvement Techniques you can always refer to my earlier blogpost as in below link :-

https://blogs.sap.com/2023/10/25/bex-query-afo-reports-development-tips-tricks-performance-improveme...

I had covered as many as formulas I had used during my experience and I expect these piece of information will suffice for a developer to create the WB.

I will be keep on adding new formulas as when I implement them. So keep watching this blogpost for regular updates.

Now let is come straight to the topic and the formulae :-

 

• 
  

  To get the dimension Name

  
  

 

=@SAPGetDimensionInfo("DS_1", "0FISCYEAR", "Name")  ----> This will display the dimension Name for example here in this case it is "Year"

 

• 
  

  Suppose we have a bex mandatory variable set then below is the formulae to get the variable value

  
  

=@SAPGetVariable("DS_1", "ZV_0FISCYEAR_Naren", "VALUEASKEY") - Say if the user had selected 2022 then it shall display as "2022"

This is an important formulae as can be modified as per below.

VALUE

If you enter VALUE, the current value of the variable is displayed.

VALUEASKEY

If you enter VALUEASKEY, the current value of the variable is displayed as a key. (Value and ValueasKey No difference )

VALUEASTEXT

If you enter VALUEASTEXT, the current value of the variable is displayed as text.

INPUT_STRING

If you enter INPUT_STRING, the current value of the variable is displayed as an input string.

INPUT_STRING_AS_ARRAY

This property name can only be used if the formula is called from VBA. It returns the input string as a string array.

DESCRIPTION

If you enter DESCRIPTION, the variable name is displayed.

ISINPUTENABLED

If you enter ISINPUTENABLED, the function checks if the variable is input-enabled.

ISMANDATORY

If you enter ISMANDATORY, the function checks if the variable is mandatory.

TECHNICALNAME

If you enter TECHNICALNAME, the technical name of the variable is displayed.

BASEINFOOBJECT

If you enter BASEINFOOBJECT, the technical name of the dimension the variable is based on is displayed.

-----------------------------------------------------------------------------------------------------------------------------

• 
  

  To set the filter component

  
  

=@SAPSetFilterComponent("DS_1", "0LOCATION", "") : This will display all the values associated as a filter in the prompt. If any Hierarchy is set then the Childs associated with the parent hierarchy is displayed as Nodes in the filer. This is awesome.

 

 * To Display a filter range say Date or Month

=@SAPGetDimensionEffectiveFilter("DS_1", "0FISCPER", "ALL") : This will display the filter range is 0fiscper dimension. Say for an example if the user had given a range of say 001.2022 to 012.2022 then that range will be displayed as  001.2022 - 012.2022.

This is applicable for 0DATE,0FISCPER3,0YEAR as well or if any custom time dimension is set.

 

• 
  

  “Last data update” in Analysis Office (AO)

  
  

1st function returns the oldest load and 2^nd one the newest load. First parameter is always the name of report (AO calls it DataSource) inserted into Excel’s worksheet.

=SAPGetSourceInfo("DS_1", "LastDataUpdate")     : Last data update Old date 

=SAPGetSourceInfo("DS_1", "LastDataUpdateMaximum")  : Last data update new date.

 

• 
  

  SAPListOfEffectiveFilters

  
  This function returns a list of all effective filters of a data source: Dynamic filters defined by the user, static filters defined in the underlying source, and filters by measure defined for the data source dimensions.

for example : Cell F20: =SAPListOfEffectiveFilters("DS_1";"TEXT")

ou have added dimension Region from data source DS_1 to your analysis. You filter this dimension and the following members are part of the analysis: California, Arizona, Florida, Nevada. If you enter the formula in cell F20, the name of the dimension is displayed in cell F20, and the four filtered members are listed as text in cell G20.

• 
  

  SAPGetWorkbookInfo

  
  This function returns an info field value for the current workbook. The info field label can be inserted with the SAPGetInfoLabel function The property names correspond to the info field values that are available for workbooks on the Information tab in the design panel.

 

Example

Cell D20: =SAPGetInfoLabel("WorkbookName")

Cell E20: =SAPGetWorkbookInfo("WorkbookDescription")

In cell D20, the Workbook Name label is displayed. In cell E20, the description name used for saving the workbook is displayed, for example sales in Europe

 

These above are the widely used formulae while creating a workbook. Rest all formulas are hardly required and even if they are needed the business cases are less.

 

Still if you want to explore other formulas kindly visit SAP help documentation and apply then in your work book in the AFO.

Please find the below link.

https://help.sap.com/docs/SAP_BUSINESSOBJECTS_ANALYSIS_OFFICE/ca9c58444d64420d99d6c136a3207632/40a62...

Kindly apply these filters in your workbook to develop it faster.

If anyone is new to AFO and want to explore more kindly visit the below URL from SAP press.

https://blog.sap-press.com/18-helpful-sap-analysis-for-microsoft-office-tricks-to-make-your-life-eas...

 

The above two URLs are SAP's documentation and I do not own those information. Above links are for information purpose.

Thank You,

Narasingha Prasad Patro