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.

First we need different variables:

1
2
3
4

Dim lresult As Long
Dim week As Integer
Dim today As Date
Dim yearCalWeek As String

After this, I needed the actual date and year, so I used the DateValue-function.

1
2

today = DateValue(Now)
yearCalWeek = year(today)

Now I need the week, for this I used the WorksheetFunction IsoWeekNum.

1

week = Application.WorksheetFunction.IsoWeekNum(today)

So I calculated the week and the year and now we have to put it into the SAPSetFilter.

1

lresult = Application.Run("SAPSetFilter", "DS_1", "0CALWEEK", week & yearCalWeek, "INPUT_STRING")

 As you can see it is very easy to get the week from the actual date. Here is the complete source code:

I hope this idea helps someone to build a dynamic report.

These posts might also be interesting:

• Analysis Office Insert Product Image
• Analysis Office 2.4 Table Design features
• Analysis Office Restrictions
• Analysis Office 2.5 DataSource Information

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

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.

My first thought was, where can I store the pictures and how can I get a product number and the picture together. The solution is very easy, I created a folder on my hard drive and put the
pictures in it. The name of the pictures are the same like the product numbers.

The next step is to write the corresponding VBA code, so that the pictures are insert into a cell. Open the Visual Basic Editor (ALT + F11) and
insert a new module. After we have to define serveral variables.

1
2
3
4
5
6
7
8
9

Dim material As String
Dim i As Integer
Dim picturePath As String
Dim rowMax As Long
Dim picture As Object
Dim t As Double
Dim l As Double
Dim w As Double
Dim h As Double

Now we have to find out, how many rows our crosstab has.

1
2

'Number of rows
rowMax = Range("SAPCrosstab1").Rows.Count

Now we can add our pictures.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

For i = 5 To rowMax

  'Set height for each row
  Rows(i & ":" & i).RowHeight = 100
  'Material
  material = Sheet3.Cells(i, 3).Value
  'Define Picture Path
  picturePath = "U: 40 Analysis Office Workbookspics" & material & ".jpg" 
  
  Set picture = ActiveSheet.Pictures.Insert(picturePath)
  'Get size of the Picture Cell
  With Range("B" & i & ":" & "B" & i)
    t = .Top
    l = .Left
    w = .Offset(0, .Columns.Count).Left - .Left
    h = .Offset(.Rows.Count, 0).Top - .Top
  End With

  'Format Picture into the right size
  With picture
    .Top = t
    .Left = l
    .Width = w
    .Height = h
  End With
  Set p = Nothing

Next

The pictures are added on the left side of the crosstab. If you want to add your pictures automatically you can use the callback AfterRedisplay.

It is just an example what you can do. So If you have any suggestions for other examples, post them into the comments.

These posts might also be interesting:

• Enable Analysis for Office Addin
• Analysis for Office 2.4 Workbook Protection and Styles
• Analysis for Office Restrictions
• Analysis for Office Get Calendar Week for SAPSetFilter

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

The API command SAPInsertLine is available since version 2.2 SP3 of Analysis for 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

Here is an example code, more detail information can be found in my book. This code insert a new
line after the dimension 0Material.

1
2

Dim ret As String
ret = Application.Run("SAPInsertLine", "NewLine1", "DS_1", "After", "DIMENSION", "0MATERIAL")

This example code insert a new line before the dimension 0Material.

1
2

Dim ret As String
ret = Application.Run("SAPInsertLine", "NewLine1", "DS_1", "Before", "DIMENSION", "0MATERIAL")

More examples can detailed information can be found in my book SAP Analysis for Office – The
Comprehensive Guide.

These posts might also be interesting:

• Using SAPGetVariable with VBA
• Analysis for Office Table Design, API and Design Rules
• Analysis for Office SAPOpenWorkbook
• Analysis for Office 2.1 Logoff via VBA

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

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:

So the command need the data source and the variable, in my example DS_1 and 0S_CUS. It is very simple, but at the moment there is no example in the Analysis Office user guide.

These posts might also be interesting:

• Analysis Office SAPInsertLine
• Analysis Office Table Design, API and Design Rules
• Analysis Office SAPOpenWorkbook
• SAP BusinessObjects Analysis for Office function undo

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

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

In Analysis for Office the macro SAPOpenWorkbook use the connection of an existing DataSource. So you can’t
define your own connection with a super user or something like that.

Here is an example code:

The name Demo_5 is the technical name of the workbook from a SAP NetWeaver server. If you want to use a document, which is
stored on a BI Platform, you need the CUID.

The connection parameters will be used from the DataSource DS_1. This parameter is mandatory if you have several connections in a workbook. If you have only one connection, it is
optional. The target workbook will be opened from the referenced connection of DS_1.

The variable parameter for ZCOUNTRY_VAR_02 is optional, but if you have a mandatory variable you have to assign a value, like the 0I_FPER variable.

If you want to refresh your workbook on opening, you have to set the parameter “Refresh Workbook on Opening”. You also need an established connection or you see the logon popup.

New is the feature that you can predefine your variable value for the new workbook. This is nice but it is still not the same as in BEx Analyzer,
because you have to use the connection from a existing DataSource. I hope SAP will provide us more in the future.

These posts might also be interesting:

• SAP Analysis for Office Logoff via VBA
• Analysis for Office Filter Data by Member
• Analysis for Office: Best Practice Conncetion
• Analysis for Office Variable Operator Contains Pattern

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

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

These posts might also be interesting:

• SAP Analysis for Office filter multiple items
• Enable Analysis for Office Addin
• Analysis for Office Addin crashes
• Analysis for Office: How to work with Excel formulas

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

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.

These posts might also be interesting:

• Analysis for Office SAPOpenWorkbook
• Analysis for Office Logoff
• Open a Bex workbook via VBA
• Create own Excel formula

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

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

These posts might also be interesting:

• Analysis for Office Logoff
• Analysis for Office: Best Practice Connection
• Settings for the Analysis for Office Plug-in
• Enable Analysis for Office Addin

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.