Analysis Office Infofields, formulas & Dashboard functions

Analysis Office provides own Microsoft Excel formulas to show information of your data source. I want to explain some of the functions and how to use them in a dashboard or a report.

  • SAPGetVariable
  • SAPGetMember
  • SAPGetInfoLabel
  • SAPGetSourceInfo
  • SAPGetWorkbookInfo
  • SAPGetDimensionInfo
  • SAPListOF
  • SAPListOfDimensions
  • SAPListOfEffectiveFilters
  • SAPListOfDynamicFilters
  • SAPSetFilterComponent

The function SAPGetVariable returns the value for a specific BW variable. For example:

Cell A2: =SAPGetVariable("DS_1";"0S_CUS";"VALUEASKEY")

 

This formula returns the key for the variable 0S_CUS. You can use this key to fill another formula to get other information. For example:

 

Cell A3: =SAPGetMember("DS_1";"0SOLD_TO=A2;"0NAME")

 

If you don't want to have two formulas you can merge them like this.

 

Cell A4: =SAPGetMember("DS_1";"0SOLD_TO="&SAPGetVariable("DS_1";"0S_CUS";"VALUEASKEY");"0NAME")

 

The result is still the same.

Analysis Office SAPGetMember & SAPGetVariable
Analysis Office SAPGetMember & SAPGetVariable

The formula =SAPGetInfoLabel returns language-dependent label for an info field. This is really nice, if you have colleagues or customers who are not familiar with one language. For example:

 

Cell B5/B6: =SAPGetSourceInfo("DS_1"; "InfoProviderName")

Analysis Office SAPGetSourceInfo
Analysis Office SAPGetSourceInfo

The information is either workbook or data source related. Which are exactly workbook or data source related can be checked in the user guide of Analysis Office. For a dashboard these are relevant:

 

  • LastRefreshedAt
  • WorkbookName
  • LastDataUpdate
  • LastDataUpdateMaximum
  • QueryTechName
  • QueryLastRefreshedAt
  • InfoProviderTechName
  • System

 

To get values for the above mentioned info fields, you have to use SAPGetWorkbookInfo and SAPGetSourceInfo.

 

Cell A7: =SAPGetInfoLabel("QueryTechName")

Cell B7: =SAPGetSourceInfo("DS_1"; "QueryTechName")

Analysis Office SAPGetInfoLabel & SAPGetSourceInfo
Analysis Office SAPGetInfoLabel & SAPGetSourceInfo

Cell A8: =SAPGetInfoLabel("WorkbookName")

Cell B8: =SAPGetWorkbookInfo("WorkbookName")

Analysis Office SAPGetWorkbookInfo
Analysis Office SAPGetWorkbookInfo

Cell A9/A10: =SAPGetDimensionInfo("DS_1";"0SOLD_TO";"ATTRIBUTE";"0CITY")

Analysis Office SAPGetDimensionInfo
Analysis Office SAPGetDimensionInfo

As you can see the first entry is in Englisch, the second in German. This is also nice for an international Excel dashboard.

 

To get an overview of the existing DataSources you can use the formula =SAPListOf.

Analysis Office SAPListOf
Analysis Office SAPListOf

As you can see my workbook contains two data sources. When you build a dashboard it is good to see which data source you need for the data.

 

If you want to display which dimensions are available in this data source you can use =SAPListOFDimensions.

Analysis Office SAPListOfDimensions
Analysis Office SAPListOfDimensions

If you only want to see which Dimensions are in rows, you have to use this

 

Cell A27: =SAPListOfDimensions("DS_1";"AXIS";"ROWS")

 

For Columns

Cell A29: =SAPListOfDimensions("DS_1";"AXIS";"COLUMNS")

 

For Filter

Cell A32: =SAPListOfDimensions("DS_1";"AXIS";"FILTER")

Analysis Office SAPListOfDimension Detail
Analysis Office SAPListOfDimension Detail

If you want to display the filters of your dashboard, you have different formulas. For example:

 

=SAPListOfEffectiveFilters returns a list of all filters of a data source including dynamic filters defined by a user, static filters in the query and measure filter. If you want either dynamic filters or measure filters you also can use =SAPGetListOfDynamicFilters or =SAPGetListOfMeasureFilters.

 

It depends what you want to show in a report. If you want to make your dashboard interactive you can use =SAPSetFilterComponent so the user can decide what to filter on a specific data source

 

For example this formula shows a filter for 0SOLD_TO and applies to all data sources in this workbook.

 

Cell A36: =SAPSetFilterComponent("DS_1";"0SOLD_TO";"ALL";"MULTIPLE")

Analysis Office SAPSetFilterComponent
Analysis Office SAPSetFilterComponent

You also get a little filter icon to select via a dialog box your values.

So after the technical stuff is done, we can create our dashboard. I want to build a simple dashboard with a chart and a table, where the user can decided which data he want to see.

Analysis Office Dashboard Example
Analysis Office Dashboard Example

For the customer number in cell A1 we use this formula =SAPGetVariable("DS_1";"0S_CUS";"VALUEASKEY") because the user has to select a customer in the prompt dialog. For the name and address I used the following commands:

 

Name: =SAPGetMember("DS_1";"0SOLD_TO="A1;"0NAME")

Postal Code and City: =SAPGetMember("DS_1";"0SOLD_TO="A1;"0POSTAL_CD") & " " & SAPGetMember("DS_1";"0SOLD_TO="A1;"0CITY")

 

For the date I use the last refreshed of the query: =SAPGetWorkbookInfo("LastRefreshedAt").  To display the last data update I use the formula =SAPGetSourceInfo("DS_1"; "LastDataUpdate"). For the InfoProvider I use =SAPGetSourceInfo("DS_1"; "InfoProviderTechName").

 

For the data of the table I use formulas like =SAPGetData("DS_1";"Net price";"0MATL_GROUP=060")

 

As you can see, it is very easy to build a beautiful report without any VBA coding. I hope this post help you to build your own dashboard.

Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
29,99 €
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
39,99 €

Write a comment

Comments: 0