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:

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.

today = DateValue(Now)

yearCalWeek = year(today)

 

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

week = Application.WorksheetFunction.IsoWeekNum(today)

 

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

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:

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.

Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
29,99 € 1
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
39,99 € 1

Write a comment

Comments: 0