Migrating Analysis Office workbooks from BW to BI Platform

This week I don't have much time to write an article about Analysis Office, but I found an interesting article from Xavier about Migrating Analysis for Office workbooks from BW to BI Platform last week.


The topic came up yesterday as a collegue asked me if I know why he can't save a workbook to the BI platform. The workbook was opened from a NetWeaver system and than the option save to BI Platform isn't available.


So read this article if you have the same problem.


Analysis Office SAPInsertLine

The API command SAPInsertLine is available since version 2.2 SP3 of Analysis Office. With this function you can insert a new line into a crosstab. There are five input-parameters available:

  • RuleID
  • Data Source Alias
  • Position
  • PositionBy
  • PositionBy parameters

You can define the RuleID, if you do not define an ID, the system generates one automatically. As Data Source Alias, you have to enter the data source alias, e.g. DS_1. As Position you can choose between Before, After, BelowHeader or BesideHeader.  For PositionBy you can enter one of the following elements:

  • Dimension
  • DimensionResult
  • DimensionGroup
  • DimensionMember
  • HierarchyNode
  • Tuple

Here is an example code, more detail information can be found in my book. This code insert a new line after the dimension 0Material.


Dim ret As String

ret = Application.Run("SAPInsertLine", "NewLine1", "DS_1", "After", "DIMENSION", "0MATERIAL")

read more 0 Comments

Analysis Office Excel formula video

Here is the post Analysis Office: How to work with Excel formulas as a short video.

read more 0 Comments

Analysis Office Cancel long-running Queries

Since Analysis Office 2.3 was released, you are able to cancel long-running queries. In the Ao_app.config you can define the parameter CancelPopupDelay. The default value is 5 seconds. You can change this setting to specify after how many seconds the cancel dialog should be displayed. The dialog could appear when you inserting a data source or navigating through the data and it took more than 5 seconds.


If the dialog is displayed, you could press "cancel" to cancel the server request. In the following message dialog, you can select restart to go back to the workbook and the data source is active. Or you select close to go back to the workbook and the data source is offline. If you don't cancel the request, the dialog will disappear automatically when the server request is completed.


Analysis Office fetching data from server
Analysis Office fetching data from server
read more 0 Comments

SAP BusinessObjects Analysis Office - The Comprehensive Guide

It is done. My Analysis Office book is finished. You can now download the table of content and the release of the book is next week. Have fun and if you have feedback send it to me. 


Have a nice weekend.

read more 0 Comments

Using SAPGetVariable with VBA

At the moment I am working very hard to get my book done, so this is only a short blog post how to use SAPGetVariable with VBA. The Analysis Office help provides no example so here it is:

Dim ret As Variant

ret = Application.Run("SAPGetVariable", "DS_1", "0S_CUS", "INPUT_STRING_AS_ARRAY") 

read more 0 Comments

Design Studio SDK installation

The information what you need to use Design Studio including the SDK can be found on many places. My problem was there is no guide where you find everything and here is a list:

  1. Download the newest version of Design Studio
  2. Download Eclipse IDE for Java EE Developers
  3. Download Design Studio SDK

The actual developer guide can be found here:

read more 0 Comments

Design Studio Settings

The Preferred Startup Mode of Design Studio decides where an application is developed and saved.

Design Studio Settings
Design Studio Settings
read more

Settings for the Analysis Office Plug-in

In Analysis Office 2.2 SP3 some new interesting parameter for Ao_app.config were delivered. The following parameter are interesting and in some case maybe very useful.

  • MaxNumberOfParallelThreads

Use this setting to define the maximum number of parallel threads that can be used to open the SAP HANA DataSources of a workbook. This only works with SAP HANA.

Use this setting to specify the mode of the prompting dialog when you open a workbook from the NetWeaver that contains exactly one DataSource.

In Analysis Office 2.3, which was released on 23th May also brought some new parameter.

  • ShowSsoLogonDialogBip

Use this setting to specify whether the logon dialog box should be displayed when using SSO with the BI platform. This maybe is interesting, if you want to change the logon language.

  • CancelPopupDelay

Since Analysis Office 2.3 it is possible to cancel a query execution. This parameter defines how long the cancel dialog should be displayed. I think this is a really nice option of Analysis Office.


There are a lot of more settings for Ao_app.config in the Admin Guide of Analysis Office. Like for example:

Some I am testing at the moment and will be review them very soon.

read more 0 Comments

Analysis Office Infofields, formulas & Dashboard functions

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:

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.

read more 0 Comments

Design Studio crashes during start

Sometimes Design Studio crashes during start after installation of a new version. I found two threads in the SCN. The simple solution is to uninstall Design Studio, rename the following folder:

  • C:\Users\<user>\Analysis-config
  • C:\Users\<user>\Analysis-workspace

and reinstall Design Studio again. Maybe sometimes it is enough to rename only the folders. More Design Studio troubleshooting can be find in the Design Studio Wiki.


Analysis Office Filter Data by Member

A feature of Bex Analyzer was the upload of selections, so you haven't to type the selections all the time. This feature wasn't available for Analysis Office until version 2.2 SP3. So I looked deeper in this function and want to share my knowledge.

BEx Analyzer Upload Selection
BEx Analyzer Upload Selection
read more 4 Comments

Analysis Office Workbook vs. DataSource Variants

Analysis Office has different two kinds of variants. One is a workbook variant and the other is a DataSource variant. So what is exactly the difference?


If you have a workbook you can save your variants in the prompt dialog.

Analysis Office Save Variant
Analysis Office Save Variant
read more 0 Comments

Analysis Office Variable Operator Contains Pattern

Analysis Office Prompt without Pattern
Analysis Office Prompt without Pattern

Since Analysis Office 2.0 the administrative settings are moved from the registry to the file system. These settings are now maintained in a XML file. As in my article “Analysis Office 2.2 SP2” mentioned the Ao_app.config is repeatedly expanded and sometimes there are new commands available.


In this context the admin guide is always very useful. On this Topic there is also a SAP Note 1920589. To use “Contains Pattern” as it is described in the Analysis Office Help under “Syntax for Entering Values”, it must be activated in the Ao_user_roaming.config file.

read more 0 Comments

Error while executing function module BICS_PROV_OPEN

I just got access to a NetWeaver 7.5 SP2 and I want to test it with Analysis Office 2.3. So I open Excel and insert a query. And here we go first error: "unable to open data source", so I thought maybe the query is broken and I developed a new query and insert it. Here we go, same error. Maybe queries don't work, so I insert a InfoCube directly. Same error…..  


Now I refresh the insert query and got an Analysis Office message: Error while executing function module: BICS_PROV_OPEN

In the explanation was one line with the hint "wrong parameter type in an rfc call", so I looked into st22 and saw a dump which the message: CALL_FUNCTION_ILLEGAL_P_TYPE

The explanation of this dump is "This error appears if parameters submitted incorrectly in a Remote Function Call."  


I searched but found nothing which should help me. After a longer research I found in the SCN this thread and the simple solution is:  


Delete all contents in C:\Users\<your_user>\AppData\Roaming\SAP AG\SAP BusinessObjects Advanced Analysis\cache  


After deleting the content, close Analysis Office and it works. Either the Analysis Office message or the BW dump explain the solution. But maybe someone has the same issue, this is the solution.

read more 0 Comments

Analysis Office Table Design, API and Design Rules

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

Analysis Office Table Design
Analysis Office Table Design
read more 0 Comments

BEx Query Designer: No Authorization for Query

The other day I had a very strange phenomenon. I was at a client, where I had created several queries a month ago. The query information shows me as owner of these queries. In the meantime the Department has made some small changes, so that I was no longer the last user who changed the query. 

BEx Query Properties Last Changed
BEx Query Properties Last Changed
read more 0 Comments

Analysis Office SAPOpenWorkbook

A new feature of Analysis Office 2.3 is a macro called SAPOpenWorkbook. Some of you maybe know the old sapbex.xla!SAPBExReadWorkbook or the BExAnalyzer.xla!runWorkbook command.


The user guide explanation for this macro is:


You can use this API method to open an Analysis workbook. The workbook that should be opened can be stored on a SAP NetWeaver server or on a BI platform.

The workbook is opened in the same Microsoft Excel instance. After the command execution, the opened workbook is active.


As you know the sapbex.xla!SAPBExReadWorkbook respectively BExAnalyzer.xla!runWorkbook command needed either an established connection from the addin or you created your own connection with the following code:

Dim R32 As Object 'Connection Object

Set R32 = CreateObject("SAP.Functions")


With R32.Connection


    .system = "ABC" 'system

    .client = "001" 'client

    .user = "reyemsaibot" 'user

    .Password = "4711" 'password

    .Language = "EN" 'language

    .systemnumber = "00" 'systemnumber

    .hostname = "" 'hostname


End With


'Test Connection

If R32.Connection.logon(0, True) <> True Then

 Exit Function

End If

read more 0 Comments

Design Studio 1.6 SP2

The new Design Studio version will be released on 8th June. New features will be only available on BI platform, not on SAP NetWeaver. Here is a short Overview of the Roadmap.


  • End user
    • Waterfall Info Charts
    • Display scaling factors in Info Chart
    • Resizable, moveable dialogs
  • Analyst
    • Ad-hoc currency conversion
    • Build virtual hierarchy of stacked dimensions
    • Cascading Filter for Universe and CSV as Data Source
  • Designer, developer, admin
    • New standard components, e.g. Dialog, Filter Bar, Link, Multi-Line Text Input, Switch, Progress Bar (only in Fiori Library)
    • Info Chart Enhancements
    • Initial View Editor: Simple Calculations

More information will be available on 8th June.


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.

BEx Analyzer Average false values
BEx Analyzer Average false values
read more