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.
Here is a short overview about the new features of Analysis Office 2.3
I think there is some cool new stuff in AO 2.3 available. Can't wait to see the next version.
I just download the version and hope to write more in the next days.
Since Analysis Office 2.1 you have a formula editor. You can find this function in the group Data Analysis >> Calculations >> Add Advanced Calculation.