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.
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")
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 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.
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")
Cell A29: =SAPListOfDimensions("DS_1";"AXIS";"COLUMNS")
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 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 decided which data he want 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:
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.
These posts might also be interesting: