· 

Analysis Office Get Calendar Week for SAPSetFilter

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
Sub GetCalendarWeekForSAPSetFilter()
  Dim lresult As Long
  Dim week As Integer
  Dim today As Date
  Dim yearCalWeek As String
  
  today = DateValue(Now)
  yearCalWeek = year(today)

  week = Application.WorksheetFunction.IsoWeekNum(today)

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

End Sub

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

author.


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.

SAP Analysis for Office - The Comprehensive Guide
The book SAP Analysis for Office - The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.
45,00 €
SAP Analysis for Office - The Comprehensive Guide
SAP Analysis for Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains 299 Pages.
37,00 €
SAP Analysis for Office - The Comprehensive Guide
SAP Analysis for Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.6 and contains 272 Pages.
27,00 €

Write a comment

Comments: 2
  • #1

    Momo (Thursday, 29 March 2018 13:46)

    Why didnt you do it with a userexit variable instead?

  • #2

    Tobias (Thursday, 29 March 2018 16:16)

    Hi Momo,

    of course you could build an user exit variable. But if you don't have access to the backend you can solve the problem as I described. It's a solution for the department. In BW you have 100 ways to solve a problem, so yes you are right, if I have access to the backend I build a user exit variable.

    Best regards,

    Tobias