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:

Since Analysis for Office 2.0 you have the option Table Design. You can now create new empty rows and columns.

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.

I just found a very good tip at Chandoo's blog, how you can sum many ranges with multi-select.

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.

The previous post about Excel Dashboards has such an appeal found, so I want to show another example.

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.

1
2
3
4
5

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 sample as an Excel formula.

=IFERROR(IF(targetCell < 0,(sourceCell - targetCell) / - targetCell,(sourceCell - targetCell / targetCell), 0)

= (ACT) 215 - (PY) 204 / (PY) 204 = 0,055 = 5,5 %

As you can see the own formula is more readable and can be easily combined with another formula.

Unfortunately, SAP export negative numbers like "Number-":

In 7.0 and 3.5 you can open workbooks via VBA. This is useful if you want to automate reporting. The command for BEx Analyzer is: