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.
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:
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")
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 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:
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:
I am Tobias, I write this blog since 2014, you can find me on twitter, facebook and youtube. I work as a Senior Business Warehouse Consultant. In 2016 I wrote the first edition of Analysis Office - The Comprehensive Guide. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
- 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
You want to know SAP Analysis Office in a perfect detail?
You want to know how to build an Excel Dashboard with your Query in Analysis Office?
You want to know how functions in SAP Analysis Office works?
Then you have to take a look into Analysis Office - The Comprehensive Guide. Either as a video course or as a ebook.
Write a comment
Jim (Thursday, 20 May 2021 11:29)
Thank you very much for sharing this information.
It helped me today to a great extent to get the attribute value of a single dimension member that is selected by the user in the workbook query prompt.
The SAP documentation about the possibilities of the Analysis functions is very brief.
I had to make 1 adjustment compared to one of your examples, which I (on my turn) want to share.
Getting the attribute value of a dimension dynamically you write:
In my Excel version (or Analysis version) the formula check gives an error.
However by adding an ampersand in between (&) it did work for me:
For the above example it is not applicable, but in order to get the description (text) of the attribute value instead of it's key it worked for me to add _Text behind the technical name of the dimension attribute. E.g.:
Tobias (Friday, 21 May 2021 12:53)
you are right, there was a mistake. You have to add the & to concatenate the fields. Thanks for the information. I changed it in the example above.