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.
I just found a very good tip at Chandoo's blog, how you can sum many ranges with multi-select.
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.
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.
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.
Public Function prozent(source As Double, target As Double)
prozent = Application.WorksheetFunction.IfError(IIf(target < 0,(source - target) / -target, (source - target) / target), 0)
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-":