Dear all,

over the last year, I created some small applications in Analysis and thought it’s time to collect a few best practice ideas, which I experienced during development and test.

This document does not go too much into detail, since my idea is that also beginners of VBA in Analysis should be able to utilize some tips.

FYI: I worked only in 1.4.x versions so far. It might be that Analysis behaviour already changed in 2.x versions.

Here we go:

1) Always refresh, first!


The most important thing when doing anything in VBA related to Analysis is to refresh your datasource (DS) first … otherwise, nothing will work!

To prevent a refresh every time, you can check if the DS is already active

Dim lResult As Long

lResult = Application.Run(“SAPGetProperty”, “IsDataSourceActive“, “DS_1”)

If lResult = False Then

    lResult = Application.Run(“SAPExecuteCommand”, “Refresh“, “DS_1”)

‘ Example: You can also skip the else

Else

    lResult = Application.Run(“SAPExecuteCommand”, “ShowPrompts“, “DS_1”)

End If

2) Formulas and own columns upfront!

If you have the task to include your own Excel formula columns in your sheet, then always try to aviod putting it at the end of the Data source! Instead, put it at the beginning:

/wp-content/uploads/2015/09/example2_786655.jpg

In that way you don’t have to take care about removing the columns when any drill-downs are happening and replacing them afterwards at the new end of the report. Of course you might need to adjust the formulas if they rely on certain key figure columns, but for “VLookups” it quite often saves work.

3) Find out the crosstab size


To find out the last column and row of your crosstab, you can use:

Dim lCols, lRows as Long

lCols = Range(“SAPCrosstab1”).Columns.Count

lRows = Range(“SAPCrosstab1”).Rows.Count

For this, also 1) applies! If your Datasource is not refreshed, you will not get the correct result. So if you need reliable variable contents you should store the previous “dimensions” of the crosstab in any cells on the sheet.

Especially if you “Remove Data Before Saving”. Then the crosstab is not existing and will cause you trouble if you execute a macro during Workbook initialization.

Therefore I try to avoid the checkbox “Remove Data Before Saving”.

4) Freeze the screen while macro runtime


To prevent a user is bothered with jumping through several thousand lines or several sheets, you always should freeze the screen before VBA execution and activate the screen update afterwards again. I prefer using:

‘At the very beginning

Application.ScreenUpdating = False

‘all the code in between…

‘At the very end

Application.ScreenUpdating = True

5) Fill the missing table headers for “Key and text” characteristics

Nothing more is annoying – when you want to create a PIVOT table on top of your datasource’s results – than the missing feature from the very first release: No headers on characteristics with “Key and text” display

For preventing problems 3) you should execute the macro only after 1):

Dim DS1_C, DS1_R, lResult As Long

Dim DS1 as String

DS1 = “your sheet name”

lResult = Application.Run(“SAPGetProperty”, “IsDataSourceActive”, “DS_1”)

If lResult = True Then

   DS1_C = Range(“SAPCrosstab1”).Columns.Count

   DS1_R = Range(“SAPCrosstab1”).Rows.Count

 

   For i = 1 To DS1_C – 1

       If Worksheets(DS1).Range(“A5”).Offset(0, i + 1) = “” Then

          Worksheets(DS1).Range(“A5”).Offset(0, i + 1) = Worksheets(DS1).Range(“A5”).Offset(0, i) & “2”

       End If

   Next i

End If

In that way every blank header cell receives the text of the previous one including an attached “2”.

6) Actions after the Refresh of a Datasource: Register Callback

When you want a macro to execute everytime your datasource has been refreshed, e.g. by drill-down, execution of Variable prompt, etc. you can register a callback:

Step 1: Include this into “ThisWorkbook”

Public Sub Workbook_SAP_Initialize()

‘ register callbacks

Call Application.Run(“SAPExecuteCommand”, “RegisterCallback”, “AfterRedisplay”, “Callback_AfterRedisplay”)

End Sub

Step 2: In your VBA module you put all your coding between these lines:

Public Sub Callback_AfterRedisplay()

‘…

End Sub

Attention: This coding is executed almost every time – even if you don’t really do anything, but just click “save”.

For that reason, you could read this note – there is a hint about improvements in your coding:

http://service.sap.com/sap/support/notes/1880033

(I did not test it, yet. So I cannot give you any advice here)

7) Clear variable contents

Maybe you have the requirement to handover a long list of input characteristics to a query variable (like you would do it via the “copy from clipboard”-button), and refresh it. Unfortunately I did not found a “clear” method to remove the variable contents again.

For that reason I helped myself by setting one “default” value as key, which exists in the master data, and afterwards the user is able to simply remove it in the variable input screen:

Dim lResult As Long

lResult = Application.Run(“SAPExecuteCommand”, “Refresh”, “DS_1”)

lResult = Application.Run(“SAPSetVariable”, “<tech. variable name>”, “<any default value>”, “KEY”, “DS_1”)

lResult = Application.Run(“SAPExecuteCommand”, “ShowPrompts”, “DS_1”)

8) SAPGetVariable is missing


One more function is missing in my opinion… There is no SAPGetVariable() available.

That means if a user filled some variables in the prompt screen, you have to define an area in your workbook where you can get the list of all the variables, by using the formula:

=SAPListOfVariables(“DS_1″;”KEY”;”PROMPTS”)

Afterwards, you have to read the value of the respective cell with the right variable content:

Dim Input as Variant

Input = Sheets(“Your sheet”).Cells(<row>, 2).Value

Finally you can use this Input to fill another variable in the second datasource, for example.

I think there are many ideas more, but for the moment it should be sufficient.

For the VBA-advanced readers, two more helpful links here:

Placing two tables in Analysis for office without overlap: http://scn.sap.com/message/14758376

SAPSetFilterComponent – how to enter/paste multiple values: http://scn.sap.com/message/15429535

I hope you enjoyed reading.

Martin

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Rui Romba

    Hi Martin,

    If I have 3 Datasources in my Workbook, the CallBack_AfterReDisplayed will be executed several times, I didn’t understand why so many times.

    Is there anyway to execute a macro only after all Datasources are refreshed?

    Many thanks,

    Rui

    (0) 

Leave a Reply