Schlagwort: excel

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 Analysis for Office 2.2 a new feature was published. You can now use your own Excel formulas in a Crosstab. First you have to add a row or column in your Crosstab. The table design is a feature since Analysis for Office 2.0.

You can add your formula.

Then you can drag your formula down to the end of the Crosstab.

When you now add a new characteristic to the rows, you will see the Excel formula will be automatically adjusts.

These posts might also be intersting:

• Analysis for Office SAPInsertLine
• Analysis for Office Data Analysis Advanced Calculations
• Analysis for Office Variable Operator Contains Pattern
• Analysis for Office Infofields, formulas & Dashboard functions

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.