Wednesday, June 3, 2015

Analysis Add-in for Office

There are very powerful analysis tools which are easy to use within the familiar environment of Microsoft Office. It makes you capable to dig deep into your multidimensional business data to discover and identify hidden trends. SAP Business Objects Analysis for Office is such a powerful tool which is one of the elements of the SAP BI Front-end Spectrum. In this blog I will describe the advantages you have with this tool and highlight some nice features it has to simplify your implementation of an analysis workbook.

Analysis for Office

Analysis for Office is the future replacement of the BEx Analyzer plug-in and is developed which the .NET framework. You can use SAP BEx Queries, query views and SAP Netweaver BW InfoProvider as data sources such as SAP HANA. The data is displayed in the workbook in crosstabs. You can insert multiple crosstabs in a workbook with data from different sources and systems. If the workbook will be used by different users, it is also helpful to add info fields with information on the data source and filter status.


  • You can refine your analysis using conditional formatting, filter, prompting, calculations and display hierarchies. You can also add charts to your analysis. 
  • If you want to keep a status of your navigation, you can save it as an analysis view. Other users can then reuse your analysis. 
  • Data Analyst who navigate through existing workbooks and analyze the data, can include workbooks in a Microsoft PowerPoint presentation and continue the analysis there. 
  • In comparison to the BEx Analyzer there is big performance gain. In this blog it even tells you could end up with a performance improvement of 50%.
  • Analysis functions and API methods are very powerful describe in the following section.

Analysis functions and API methods

It gets very interesting if you need more sophisticated workbook design, because the Analysis plug-in contains a dedicated set of functions and API methods to access data and meta data of connected BW systems. There are an extensive number of API functions available that you can use with the Visual Basic Editor, to filter data and set values for BW variables. These methods and functions can be used in VBA macros which can be connected to UI elements that are available on the Developer tab in the menu.
You can lookup all the functions and API methods in this Source.

Example of a VBA macro

This way you are able to implement with simple VBA code a very powerful and smart workbook. In this example below you can see I used these functions to check if there is a connection with the BW system (SAPGetProperty). Afterwards I fill the query variables (SAPSetVariable) of multiple datasource placed in different tabs of a workbook.

With this tool you can shortcut time and lower implemetation cost and establish complicated analysis in a simple way. If you need advice or assistance with the implementation of Reporting & Analysis, Acorel is pleased to be your sparring or implementation partner.