analysis office.

Analysis Office 2.5 - Technical Configuration

A really cool feature of Analysis Office 2.5 can you see in the "backend" under File >> Analysis. The function Customize Analysis offers now new options. Besides the Customize User Interface from Analysis Office 2.4 which allows you to change the ribbon menu of Analysis Office, you now can directly modify the technical settings of Analysis Office.

Read More 0 Comments

Analysis Office 2.5 DataSource Information

This week is very short in case of the day of German unity and we have sprint change next week, I haven't time to write a lot so here is just one goodie. When you insert a DataSource in Analysis Office 2.5 you see the recent inserted DataSource. Now in Analysis Office 2.5 you also see the technical name of the query and the system. I think this is nice if you are testing a query on different systems.

Read More 0 Comments

Analysis Office 2.5 Scaling Factor

In my current project I work a lot in the backend of a BW 7.4 on HANA but I finally found time to look into Analysis Office 2.5. Here is a short explanation about the feature that the scaling factor works now with conditional formatting. If you configure your conditional formatting like the following screenshot and apply after that your favourite scaling factor. It automatically adjusts.

Read More 0 Comments

Analysis Office 2.5 is available

Analysis Office 2.5 is finally available since the end of August for everyone. After I submit my diploma thesis last week, I have now time to see whats new in Analysis Office 2.5. Here is the first short overview of the new feature:

  • You are now able to connect to SAP BusinessObjects Cloud
  • Customize User Interface has new options
  • New different troubleshooting modes
  • New options with Table Design
  • A new API method SAPCallMemberSelector
  • Changes on the grouping Crosstab function
  • New file system setting ShowUtcTimeStampsInDataCells
Read More 0 Comments

Analysis Office 2.5 & AMDP

At the moment it is very quiet about new blog post. This is maybe the summer break everybody talks about. ;)

I am at the final phase of my diploma thesis and have no time to do cool stuff and also SAP doesn't release Analysis Office 2.5. It is still in ramp up and nothing new at the moment.

 

So I could only say, my current project is a BW on HANA and I am testing how HANA SQL Script (AMDP) works and how fast we could build some stuff. First tests show a transformation with routines for 800.000 records about 1:55 minutes. The same transformation with AMDP (ABAP Managed Database Procedures) only need between 15 - 23 seconds. This is really impressive. 

 

So stay tuned, if SAP will release Analysis Office 2.5 and some examples with AMDP.

Read More 0 Comments

Analysis Office 2.4 SP3 is released

Last week the Analysis Office 2.4 SP3 was released. Here is a short overview of notes, which may solves some problems.

  • 2460877 - Excel crashes when opening several workbooks with SAPListOfMessages
  • 2461196 - Advanced calculation editor throws error when inserting a member
  • 2468933 - Effective filter shows only # for compounded characteristic
  • 2463167 - Non-SAP (built-in) formula cells of workbook are not refreshed
  • 2467459 - Prompts dialog clears variable
Read More 0 Comments

Analysis Office - The Comprehensive Guide 2nd edition

After I had time in my parental leave to read proof the second edition of my book, I can say it is now finished. The second edition is now available in the store. You can download the table of content here. This version is only available here and at the moment not on Amazon.

 

If you have any questions feel free to ask and send me either an email or a comment. I hope to publish regularly in the next weeks so stay tuned.

Read More 0 Comments

Analysis Office Video Course

In this blogpost I want to know if there is an interesting about an Analysis Office Video Course. Thanks for your time and your answers. Open the survey.

Read More 0 Comments

Analysis Office 2.4 SP2 is released

On 10.03.2017 a new service pack for Analysis Office was released. The main reason why you should update from SP0 or SP1 is that they fixed a lot of bugs in it (and maybe they build some new). Here is a short overview of the fixed problems:

Read More 5 Comments

Analysis Office Get Calendar Week for SAPSetFilter

A few weeks ago, a colleague of mine, ask me how he can get the calendar week to use it in the Analysis Office API SAPSetFilter. I developed a short macro which defines the calendar week to use it in 0CALWEEK. Maybe someone has another solution for this.

Read More 0 Comments

Analysis Office Hide Customize User Interface

In this thread on blogs.sap.com, Michael Hamm asked how to hide or deactivate the Customize User Interface button. At first, at the moment SAP doesn't offer any checkbox which you can check and uncheck in the settings.

Read More 0 Comments

Analysis Office Insert Product Image

In the comments of this blogpost on blogs.sap.com, Stephen Hobbs showed his idea of insert a product Image besides the crosstab in combination with the new Customize User Interface function. So I make my own thoughts how I can realize a thing like this.

Read More 0 Comments

Analysis Office 2.4 Table Design features

In Analysis Office 2.4 SAP improved the Table Design Editor. You can now create and edit formulas. In an earlier post, I described how to work with Excel formulas in a crosstab. You can add a new column with the table design functionality.

Read More 0 Comments

Analysis Office 2.4 SP1 is available

Since 13.01.2017 the SP1 of Analysis Office 2.4 is available. You can download it here. You need a S-User to download it. There are three new file system settings:

  • UseDataSourceDeltaUpdate
  • EnforceDatePickerForCalendarDayVariable
  • SetEmptiedDoubleDataCellsToValue0

There are also some changes in the User Interface Customization.

Read More 0 Comments

Analysis Office 2.4 Workbook Protection and Styles

A new feature of Analysis 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 Office.

Read More 0 Comments

Analysis Office 2.4 File System Settings

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

Read More 0 Comments

Analysis Office 2.4 Customize User Interface

A new feature of Analysis 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.

Read More 8 Comments

What's new in Analysis Office 2.4

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

Read More 0 Comments

Analysis Office - The Comprehensive Guide

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.

Read More 2 Comments

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.

Read More 0 Comments

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

 

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:

Read More 0 Comments

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.

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:

Read More 0 Comments

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.

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.

Read More 0 Comments

Analysis Office Variable Operator Contains Pattern

Analysis Office Prompt without Pattern
Analysis Office Prompt without Pattern
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

Read More 2 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.

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:

Read More 0 Comments

Analysis Office 2.3 features

Here is a short overview about the new features of Analysis Office 2.3

  • The Analysis 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 Office 2.2 SP3 available. I will create a detailed post soon.
  • You can create restricted measures on the fly in Analysis 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 Office workbook.
  • There is also a new Table Design Rule Editor.

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

Read More 0 Comments

Analysis Office 2.3 is available

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

Read More 0 Comments

Analysis Office Save Query View

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

Read More 0 Comments

Analysis Office Data Analysis Advanced Calculations

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

Read More 2 Comments

Analysis Office Logoff

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

Read More 0 Comments

Analysis Office 2.2 SP2

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.

Read More 2 Comments

Rename and delete an Analysis Office Workbook

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

Read More 0 Comments

Analysis Office: How to work with Excel formulas

In Analysis Office 2.2 a new feature was published. You can now use your own Excel formulas in a Crosstab. First you have to add a row or column in your Crosstab. The table design is a feature since Analysis Office 2.0.

 

You can add your formula.

Read More 0 Comments

Analysis Office: Best Practice Connection

After the article "Connecting error in Analysis for Office via VBA" is often read, I would like to publish a few tips and tricks about working with Analysis Office.

 

The most important thing when you are working with VBA in Analysis Office, is the refresh of the DataSource(s). Otherwise nothing works. Once a connection to the Business Warehouse is established, each DataSource needs to be refreshed.

 

If you have only one DataSource, that is the source code:

 

Call Application.Run("SAPExecuteCommand", "Refresh", "DS_1")

 

If you have more than one DataSource, this is the source code:

 

Call Application.Run("SAPExecuteCommand", "Refresh")

 

So that you know if the refresh was successful, you should use the following source code:

 

Dim lResult As Long

lResult= Application.Run("SAPExecuteCommand", "Refresh")

 

or

 

Dim lResult As Long

lResult= Application.Run("SAPExecuteCommand", "Refresh", "DS_1"

 

Therefore you can check lResult to 1 or 0. If you refresh the DataSource(s) each time, a lot of time is wasted. To counteract this, there is the following source code:

Call Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_1")

 

This command checks if the DataSource is already active. Here an example source code:

Sub AnalysisOfficeStart()

Dim lResult As Long

'Connection is running?

If Application.Run("SAPGetProperty", "IsConnected", "DS_1") Then

'Active DataSource?

If Not Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_1") Then

'Refresh DataSource

lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")

Else

'Show Prompts

lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_1")

End If

Else

lResult = Application.Run("SAPLogon", "DS_1", "Client", "User", "Password")

lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")

End If

End Sub

 

After this you can excute your own code.

Read More 0 Comments

Analysis Office 2.1 Logoff via VBA

Since version 2.1 Analysis Office is now finally a VBA command to log out of the current system.

 

So far is nothing in the help file, but it is called in the SCN Forum under "Whats new in Analysis Office 2.1".

 

The command is:

Public Sub Logoff()

Call Application.Run("SAPLogoff",Parameter)

End Sub

 

Parameter:

  • True: With this value, the connection is restarted the system
  • False: With this value the connection will not restart the system

Result:

  • 0: faulty execution
  • 1: Successful execution
Read More 0 Comments

Analysis Office Goto ala BEx Query

In BEx Analyzer you could jump into another query from a query / workbook. The GoTo-Function make sense if you have one query for overview and one for detail.

Read More 0 Comments

Analysis Office Addin crashes

When you have macros in your Analysis Office Workbook and you remove the Crosstab of a DataSource, Analysis Office maybe crashes. Therefore, if you need the DataSource no longer, you should remove it completely and not only the Crosstab.

Read More 0 Comments

Enable Analysis Office Addin

Before you can use Analysis Office with Visual Basic for Application, you have to load the Analysis Addin. The code for this is in Analysis Office 1.4:

Private Sub EnableAnalysisOffice()

Dim addin As COMAddIn

For Each addin In Application.COMAddIns

If addin.progID= "SBOP.AdvancedAnalysis.Addin.1" Then

If addin.Connect= False Then addin.Connect= True

End If

Next

End Sub

 

In Analysis Office 2.0, SAP has changed the name of the addin. The command is now:

 

Private Sub EnableAnalysisOffice()

Dim addin As COMAddIn

For Each addin In Application.COMAddIns

If addin.progID= "SapExcelAddIn" Then

If addin.Connect= False Then addin.Connect= True

End If

Next

End Sub

 

Now you can continue working with the SAP Analysis commands.

Read More 0 Comments

SAP Analysis Office filter multiple items

Update: Since Analysis Office 2.3 you also can filter your data by uploading a file.

Read More 0 Comments

SAP Analysis Office DataSource Info Field

As in BEx Analyzer, there is important information that you would like to represent in a Workbook. If you have worked with BEx Analyzer, this screenshot should be familiar.

Read More 0 Comments

Analysis for Office Increase data cells

A limiting factor in SAP BusinessObjects Analysis is the restriction to 500,000 cells per DataSource. The limitation is calculated from rows * columns. This limitation can be increased by the following parameter value in the registry:

 

HKEY_CURRENT_USER\Software\SAP\AdvancedAnalysis\Settings\DataSource\ResultSetSizeLimit = -1

 

The Value “0” means that the default value is defined by 500,000 cells. Values greater than zero will return the entered value as number of cells. If you want to use the Business Warehouse settings you have to set the Value “-1”.

The corresponding parameter value for this can be found in the Business Warehouse table RSADMIN. The Object is “BICS_DA_RESULT_SET_LIMIT_MAXHKEY”.

Read More 2 Comments

Define default Workbook in Analysis Office

AO Settings
AO Settings

There are two ways to define a default workbook in Analysis for Office (AO). Either every user can define autonomously a Workbook as default Workbook or the IT sets this globally for all users permanently. The first way is very time-consuming because you have to define the default workbook for every Analysis Office User manually. The second way is much more comfortable.

Read More 0 Comments

Connecting error in Analysis Office

The following source code allows you to connect your DataSource to a BW system. The workbook must contain a DataSource (DS_1). At this moment you can't open a workbook like BEx 3.5 or 7.0. In Analysis Office 2.3 you have now the option to open a workbook with the function SAPOpenWorkbook.

Read More 0 Comments

SAP BusinessObjects Analysis for Office function undo

AO Undo
AO Undo

There are a lot of new features in Analysis Office 1.4.

 

As you can see the Undo/Redo function of Analysis Office is much more extensive than in the good old BEx Suite. it is now possible to go several steps back and to revoke.

Read More 0 Comments