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:
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")
😎 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:
SAPSetFilterComponent - how to enter/paste multiple values:
I hope you enjoyed reading.
Martin
- SAP Managed Tags:
5 Comments
