2015.

After „SAP BW 7.4 Maintain characteristics“ is one of the most read posts. I visualize this post in a short video.

In Analysis for 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 for Office 2.0.

You can add your formula.

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

The most important thing when you are working with VBA in Analysis for 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:

1

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

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

1

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

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

1
2

Dim lResult As Long
lResult= Application.Run("SAPExecuteCommand", "Refresh")

or

1
2

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:

1

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

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.

Since version 2.1 Analysis for 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 for Office 2.1".

The command is:

1
2
3

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

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.

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

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 for Office 2.0, SAP has changed the name of the addin. The command is now:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

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.

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

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.

The previous post about Excel Dashboards has such an appeal found, so I want to show another example.

You want to use the data which is provided by the Business Warehouse in your monthly report or any other report. The SAP Reporting tools like BEx Analyzer or Analysis for Office provides this data in "data tables". These are not easy to read and maybe it isn't possible to see at first glance what you want to express.

You can use the ABAP program RRMX_WORKBOOKS_GARBAGE_COLLECT to find workbooks that are currently assigned to a user or group of users via the transaction SE38. Through the hook "Workbooks found erase" these are removed from the system.

With every new release of SAP HANA functions are moved from the OLAP engine into the database. The current state of the push down can be found in note 2063449. So check this note from time to time, if it is interesting to implement a service pack.

Note: You need a S-User to access this note.

When you get a date from BEx Analyzer or Analysis Office the Excel internal format is Text and not Date. This means that the sorting doesn't work correctly. Use the following VBA source code to convert easily text to date.

A limiting factor in SAP BusinessObjects Analysis for Office 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”.

1. Call transaction se38
2. Run Program RSDDS_AGGREGATES_MAINTAIN
3. Select InfoObjects or hierarchy
4. Run

Unfortunately, SAP shifted the maintenance of master data in SAP BW 7.4 into the Web. Not everyone wants to maintain the master data on the Web. Here is a small workaround.

1. Call transaction sbiw
2. Select menu item "Settings for Application-Specific DataSources (PI) >> Profitability Analysis >> Assign Key Figures
3. Choose either "New Entries" or modify existing one.

1. Call transaction SPRO
2. Calling SAP Reference IMG
3. Controlling >> Profitability Analysis >> Information System >> Report Components >> Define Key Figure Schemes
4. Choose Operating concern
5. Select Elements of the key figure scheme
6. Select a key figure

1. Call transaction se16
2. Select table SMEN_BUFFC
3. Enter Username and/or Report
4. Run
5. Select entry
6. Go to menu >> table entry >> Delete the entry

From time to time there is a mistake in the resolution of structured items and the transfer of material hierarchies to BW. It helps the Note 1410263.

In Excel you can quickly create very complex formulas. If you want to simplify your formula, you can also write your own function in VBA. Here is an example for calculating the percentage variance from the previous year.

1
2
3
4
5

Option Explicit

Public Function prozent(source As Double, target As Double)
  prozent = Application.WorksheetFunction.IfError(IIf(target < 0,(source - target) / -target, (source - target) / target), 0)
End Function

As you can see, a very simple formula. For this purpose the sample as an Excel formula.

=IFERROR(IF(targetCell < 0,(sourceCell - targetCell) / - targetCell,(sourceCell - targetCell / targetCell), 0)

= (ACT) 215 - (PY) 204 / (PY) 204 = 0,055 = 5,5 %

As you can see the own formula is more readable and can be easily combined with another formula.

There is an easy way to delete the contents of an array. Is possible by the command Erase

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Option Explicit

Sub FeldBeispiel()
Dim fFeld(5, 5) As Integer

fFeld(3, 3) = 3
MsgBox fFeld(3, 3)
Erase fFeld
MsgBox fFeld(3, 3)

End Sub

In SAP Support Portal you find the Note 1101143. The Note provides an overview of the factors which affecting the performance of BEx Analyzer.