2015.

Video How to maintain Master Data in SAP 7.4 

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

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

One further Excel Dashboard Example

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

Read More 0 Comments

Create simple Excel Dashboards

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.

Read More 0 Comments

SAP BW find orphaned workbooks

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.

Read More 0 Comments

Relocation of OLAP functions on SAP HANA

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.

Read More 0 Comments

How to convert a text field into a date field with VBA

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.

 

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

SAP BW hierarchy and attribute change run for all InfoObjects

  1. Call transaction se38
  2. Run Program RSDDS_AGGREGATES_MAINTAIN
  3. Select InfoObjects or hierarchy
  4. Run
Read More 0 Comments

SAP BW 7.4 Maintain characteristics 

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.

Read More 2 Comments

SAP COPA keyfigure assignment

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

Define SAP COPA key figure schemes

  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
Read More 0 Comments

Remove BW Query from user favorites

  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
Read More 0 Comments

Explosion of structured items 2LIS_03_BF

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.

Read More 0 Comments

Create own Excel formula

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.

 

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.

Read More 0 Comments

Delete VBA array content

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

 

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

Read More 0 Comments

Factors affecting the performance of the BEx Analyzer

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

Read More 0 Comments