Analysis for Office Infofields, formulas & Dashboard functions

Analysis for Office provides own Microsoft Excel formulas to show information of your data source. I want to explain some 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 SAP Business Warehouse 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.

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

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 for 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")

Cell A8: =SAPGetInfoLabel("WorkbookName")

Cell B8: =SAPGetWorkbookInfo("WorkbookName")

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

As you can see the first entry is in English, 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.

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.

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

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 on 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")

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 decide which data he wants to see.

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. If you like, you can share some of your ideas either via the comments or just send me a mail.

As Jim mentioned in the comments I had a mistake that in some formulas the &-symbol was missing to concatenate the string correctly

These posts might also be interesting:

• Analysis for Office SAPInsertLine
• Analysis for Office: How to work with Excel formulas
• Create simple Excel Dashboards
• SAP Analysis for Office DataSource Info Field

• In my newsletter you get informed about new topics
• You learn how to use Analysis Office
• You get tips and tricks about SAP BI topics
• You get the first 3 chapters of my ebook Analysis Office - The Comprehensive Guide for free