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.
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:
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")
Here is the post Analysis Office: How to work with Excel formulas as a short video.
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.
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.
Dim ret As Variant
ret = Application.Run("SAPGetVariable", "DS_1", "0S_CUS", "INPUT_STRING_AS_ARRAY")
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:
The Preferred Startup Mode of Design Studio decides where an application is developed and saved.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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")
.system = "ABC" 'system
.client = "001" 'client
.user = "reyemsaibot" 'user
.Password = "4711" 'password
.Language = "EN" 'language
.systemnumber = "00" 'systemnumber
.hostname = "172.23.13.70" 'hostname
If R32.Connection.logon(0, True) <> True Then
More information will be available on 8th June.
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.