excel.

Analysis for Office Infofields, formulas & Dashboard functions

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:

Read More 2 Comments

Analysis for Office Table Design, API and Design Rules

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

Read More 0 Comments

BEx Query Average vs. Excel Average

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.

Read More 0 Comments

Sum many ranges with ALT=

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

Read More 0 Comments

Analysis for Office: How to work with Excel formulas

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.

Read More 8 Comments

One further Excel Dashboard Example

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

Read More 0 Comments

Create simple Excel Dashboards

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.

Read More 0 Comments

How to convert a text field into a date field with VBA

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.

 

Read More 0 Comments

Create own Excel formula

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.

Read More 0 Comments

Display SAP Minus keyfigures correct in Excel

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

Read More 0 Comments

Open a BEx workbook via VBA

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:

Read More 0 Comments