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:
When I was recently creating a new query at a customer, I have found a strange phenomenon. The query should show the average ticket number of a department. First you see the explanation and then I provide the solution of the problem. The query should show the individual, cumulative and average monthly values, as you can see in the screenshot.
In Analysis 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 Office 2.0. You can add your formula.
You want to use the data which is provided by the Business Warehouse in your monthly report or any other report. The SAP Reporting tools like BEx Analyzer or Analysis for Office provides this data in "data tables". These are not easy to read and maybe it isn't possible to see at first glance what you want to express.
When you get a date from BEx Analyzer or Analysis Office the Excel internal format is Text and not Date. This means that the sorting doesn't work correctly. Use the following VBA source code to convert easily text to date.
In Excel you can quickly create very complex formulas. If you want to simplify your formula, you can also write your own function in VBA. Here is an example for calculating the percentage variance from the previous year. Option Explicit Public Function prozent(source As Double, target As Double) prozent = Application.WorksheetFunction.IfError(IIf(target < 0,(source - target) / -target, (source - target) / target), 0) End Function As you can see, a very simple formula. For this purpose the...