2016.

After I started my BW/4HANA instance on cal.sap.com and everything was working, I logged on and typed RSA1. But there was no modeling tab anymore. Then I rememberd, I need the BW Modeling Tools. So I installed the latest Version of Eclipse and the BW Modeling Tools. After that I need a Project. The project is the binder for all activities in our BW system. Make a right-click in the Eclipse Project Explorer and choose BW Project.

A new feature of Analysis for Office 2.4 is workbook or sheet protection. It is similar to the Microsoft Excel Protect Sheet / Workbook function, but it is also different. When you need to protect your workbook or sheet and you use the Excel functionality, you can not refresh or drill down your Crosstab. But when you want to refresh your data or give the users the opportunity to drill down or filter, you have to use the workbook / sheet protection from Analysis for Office.

Analysis for Office 2.4 also provides new file system settings. Especially for the new Customize User Interface function. The file Cof_app.config provides all these parameters:

• AppBuilderDefaultProfilePath
• AppBuilderUserProfileDirectory
• AppBuilderCompanyProfileDirectory
• AppBuilderReadOnlyProfileDirectories

And also these parameters:

• CurrentCodePage
• SapWebGuiPath
• NwbcTicketIssuerPath

Only then AppBuilderCompanyProfileDirectory is a PerMachine parameter, the others are UserRoaming. How the other parameters exactly work has to be tested. The Ao_app.config also provides new parameters:

• DefaultBWQueryDesigner
• DoSelectorHierarchyExplicitSelection
• AutoCompleteFetchMemberLimit
• HanaHttpConnectionTimeout

So it can be possible that the user can choose between the old BEx Query Designer and the new BW Modeling Tools. The default parameter is "0", so at the moment I cannot say what exactly has to be the value for other query designers. The parameter DoSelectorHierarchyExplicitSelection should select only the child nodes of a node and the parent node is not automatically checked. But I haven't got a chance to test this. So I can't confirm this. I don't know at the moment how the parameter AutoCompleteFetchMemberLimit works, I only know the default value is 30. But as soon as possible I add these information to this post.

So after I read a lot about BW/4HANA, I decided to create a own SAP BW/4HANA 1.0 [Developer Edition] instance on my Amazon AWS account. First I had to extend my normal AWS account with a IAM user. For this you choose under Security, Identity & Compliance >> IAM. Under Users you click on Add user.

After we can log on to a BI platform with our .Net application, we want to open a document for example a Analysis Office workbook. First we have to define several variables.

A new feature of Analysis for Office 2.4 is that you have a function called Customize User Interface. On the first view it looks similar to the Excel customize ribbon function. But it has more flexiblity to switch between different interfaces. First you have to open the Customize User Interface dialog via File >> Analysis. The following dialog appears.

Since yesterday Analysis for Office 2.4 is available. You can download it with a S-User under Support Packages and Patches >> By Alphabetical Index (A-Z) >> A >> SBOP Analysis Office >> SBOP Analysis MS Office 2.4.

The SAP help page isn't updated yet. But I think it would be updated in the next days.

After @xjhacking conviced me or say talk round, here is a one day offer for my book Analysis Office - The Comprehensive Guide. You can buy it for 9,99 € until tomorrow evening.

This week I want to test the integration of a BI Platform to my .Net program. So I want to install a test version of the BIP, but my virtual machine hadn't enough space. The first step was to extend the space of the disk. It is very easy in VirtualBox to extend the disk.

1. Open the Windows Console
2. Go to the path where you installed Virtual Box. (Default: C:Program FilesOracleVirtualBox)
3. Type VBoxManage.exe modifyhd "Path to the disk" --resize 100000 (=100 GB)
4. Press Enter and your VirtualBox will be extended

After my VirtualBox was ready I could install the BIP. But the next issue was found very soon. I need a license key.

So it is done, Part 2 finally is written and an example is uploaded to github. It took me about 8 months to release Part 2 of these series. Part 1 was released in March 2016 and now I had time to write an example for Part 2 and upload it to github. Part 1 discover the basics about a RFC Server with the SAP NCo Connector, part 2 now explain how to build a RFC Server more flexible.

If a SAP Design Studio Application has been developed in local mode or on a develop machine, these can be exported and then imported into a new system. Here is a short guide.

This week the new open SAP course BW/4HANA in a Nutshell started. It is a free course. One big thing is that BW/4HANA no longer supports the good old BEx Suite. You now have to use the BW Modeling Tools based on Eclipse. In this and further posts I will go deeper into the BW Modeling Tools and what is possible and what is not possible at the moment.

If you have any questions feel free to ask or correct me ;) So let's get started.

Here is a short guide how to update an extension in SAP Design Studio.

1. Select Help >> About

In this article I describe in a short manual how to set up Eclipse for Design Studio.

1. Open Eclispe and choose Window >> Preferences.

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.

The API command SAPInsertLine is available since version 2.2 SP3 of Analysis for 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 the post Analysis for Office: How to work with Excel formulas as a short video.

Since Analysis for 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 for 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.

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:

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 for 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.

• ShowDSVariantsForWorkbooksWithOneDS

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

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:

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.

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 for Office until version 2.2 SP3. So I looked deeper in this function and want to share my knowledge.

Analysis for 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.

I just got access to a NetWeaver 7.5 SP2 and I want to test it with Analysis for 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 for 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

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

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 for 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:

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.

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 for Office 2.3

• The Analysis for Office API has been extended. It is now possible to save data and to open workbooks.  So you are now able to open workbooks via VBA macro, as then with sapbex.xla!SAPBExReadWorkbook or BExAnalyzer.xla!runWorkbook .
• It is now possible to cancel long-running queries
• You can now rename dimensions, attributes and measures
• There is now an option in the context menu of the components tab, which allows you to logoff from a BW system. This was previously only via VBA API possible. According to this SCN thread it isn't the identical as in Bex Analyzer 3.5/7.0 because you have to replace the system and then reconnect to another system.
• It is now possible to add filter values via upload of a text file. I believe it was already in Analysis for Office 2.2 SP3 available. I will create a detailed post soon.
• You can create restricted measures on the fly in Analysis for Office, but you need BW 7.5 SP4 to do this
• The sheet protection is provided for the next release in Q4
• There is a new report type in the transaction RSBBS called "Analysis Office" available, which allows you to jump from Design Studio into an Analysis for Office workbook.
• There is also a new Table Design Rule Editor.

I think there is some cool new stuff in SAP Analysis for Office 2.3 available. Can't wait to see the next version.

Since yesterday Analysis for Office 2.3 is available. You can download it here, to download it you need a S-User with download credentials.

The admin and user guide isn't available at the moment. I hope SAP will release it in the next days. First impressions can be made at this SCN article from Tammy Powlas.

I just download the version and hope to write more in the next days.

As in the BEx Analyzer you can save also query views in Analysis for Office. Currently in version 2.2 SP2 you can find this option under Tools >> Save View.

Since Analysis Office 2.1 you have a formula editor. You can find this function in the group Data Analysis >> Calculations >> Add Advanced Calculation.

1. Open transaction se38
2. Open program RDDIT076
3. Execute (F8)
4. Enter a request or task number
5. Execute (F8)
6. Doubleclick on a selected entry
7. Switch between Display and Change (F9)
8. Change your request
9. Save (CTRL + S)

• Call transaction sm30
• Open Table/View RSPROCESSTYPES

After SAP still doesn't provide a native button in Analysis for Office 2.2 SP2 to log off from a system, I decided to make a short instruction from my article "Analysis for Office 2.1 Logoff via VBA". Have fun.

If you have developed your own extension with the SAP Design Studio SDK and want to publish this extension on the BI Platform, you have to click on Help >> Platform Extensions.

Now you see the following screen.

I just want to implement a button which should print my application. I found a tutorial, which says the code is APPLICATION.PRINT. But when I write the coding in Design Studio, .Print isn't available.

I just started working intensive with SAP Design Studio and developing own components. When you install Design Studio 1.6, it starts in local mode. In this mode you can save your design studio application only on your local maschine. If you want to use other supported platforms, you have to select the required platform in the settings.

Since three days a new service pack of Analysis Office is available. Now you can use Analysis Office and Excel 2016. 

Here is a summary of new functions

• New settings in the Ao_app.config available for example: MaxNumberOfParallelThreads or NcoTraceLevel
• Separate tabs for Search, Area, Role and Workspaces in the Open Data Source dialog.
• A new callback called BeforeFirstPromptsDisplay is implemented
• More advanced settings are available for example RFCBundling, Allow Client and Language for SSO Logon

Detail information can be find in the What's new Guide.

In some cases you want to trigger an external program from a SAP system. In this part 1 I explain how to build a RFC Server with NCo 3.0. I had several problems when I started with this topic so I decided to write a short example. If someone want the Visual Studio project files, please contact me.

So let's go.

This Post describes how to build a simple RFC Server using SAP NCo 3.0 and the app.config. Part 2 will be describe how to build a RFC Server with RFC Parameter. As example program I use STFC_CONNECTION. It is a good example, because it contains importing and exporting parameters.

First you have to download and install NCo 3.0 (OSS login required). Afterwards you have to start a new project in Visual Studio.

Setting up the Visual Studio Project:

In the properties of the project you have to use a new console application. As target framework I use .Net Framework 4.5.

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

While developing in the Visual Studio for .Net you can get an error like this: "Interop type 'CrystalDecisions.Enterprise.SessionMgr' cannot be embedded. Use the applicable interface instead." or in German: "Interop Typ 'CrystalDecisions.Enterprise.SessionMgr' kann nicht eingebettet werden. Verwenden Sie stattdessen die anwendbare Schnittstelle."

To aviod this error, you have to change the properties of this reference. You have to switch the "Embed Interop Types" from True to False.

In the last time I had several problems with external access to a SAP BW System. The access was realized with an old version of the SAP Connector. And that is the problem. If you have a SAP Business Warehouse System version which is greater than 7.3, you maybe get troubles. For example SAP added new fields or like in 7.4 you now have the option for "Long text is XL" in the master data. So the old versions would not work with this.

I just found a very good table about the maintenance of all versions in Martins blog.

If you want to delete or rename an Analysis for Office Workbook, you have to right click on the Workbook Opendialog.