Skip to Content
Author's profile photo Former Member

Implementing a calendar like filter in the body of a report

Hi All,

I would like to show you how to implement a calendar like filter in the body of a WEBI report like the one below

/wp-content/uploads/2016/03/calendar_910071.png

This example was build on SAP BO 4.1 SP5, but since there is no dependency of any new feature, I imagine it will work on any version that supports Input Control and object linking.

The idea here is to build a cross-table and make it an Input Control linked to the body of the report.

To start with I have a date object , [date], coming from a calendar table.

Build the following variables  (all variables

are dimension variables, except when explicity mensioned)

1 – Year = Year([date];

2 – Month = Month([date])

3 -DayNumberOfWeek = DayNumberOfWeek([date])

4 – Weekday = =If([DayNumberOfWeek]=1;”Mon”;If([DayNumberOfWeek]=2;”Tue”;If([DayNumberOfWeek]=3;”Wed”;If([DayNumberOfWeek]=4;”Thu”;If([DayNumberOfWeek]=5;”Fri”;If([DayNumberOfWeek]=6;”Sat”;”Sun”))))))

5 – Week = NumberOfWeek([date])

6 – Week_aux = If([Week] >52;1;[Week]+1)

7 a measure variable [day] = NumberOfDayInMonth([date])

Drop a cross-table on the report and set it[s structure like below :

cross table.png

Apply a break on [Month] and set it´s property like below

break prop.jpg

Now, hide the [DayNumberofWeek] row (right click on the Row and choose Hide->Hide Dimension

Repeat the procedure to [Week_aux] column

Name the block (table) as Calendar (right click on the table, choose Format Table->General)

We will create a elements link from this cross-table to the other elements on the report.

To do so, right click on the tabel and choose Linking -> Add Element Link

element link.jpg

Select all objects

element link.png

Click on Nextand enter calendar as the name of the input control

In this next step you will define the elements on the report that will be affected by this input control. Check all boxes except Calendar

element link dependencies.jpg

Now you have a clickable calendar to filter your report.

This sample suposes the the report is already filtered by Year and Month


Post I will showhow to create a clickable list to select the month and year.


Regards,

Rogerio

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      So if you click on  day (1 - 31), it will filter the target tables on the value clicked (i.e. 3)?  And if you click on Day (Mon - Sun), it will filter the target tables on the value clicked (i.e. Tues)?  Just making sure I am understanding that correctly...

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Thomas,

      Exactly.

      Another way of setting a filter on the report.

      Regards,

      Rogerio

      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      Nice Idea. But i have few questions and suggestions on this.

      1) I have not understood the use of Week_aux. Any Week function gives the number of week.

      2) For getting day name you can use directly the webi function =Left(DayName([Date]);3).

      Is there any reason for deriving it from day number?

      3) You are using Month Name in the cross tab and sorting on it. But by default month name will get sorted like August,April etc not in the calendar order. I think you need add month number and sort on it and hide it. Like the way you are doing for day number.


      By the way nice Post

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Divya,

      thanks for your suggestion and comments!!!

      Regards,

      Rogerio

      Author's profile photo vsenni senni
      vsenni senni

      Hi

      I am trying to create the same calendar but i am getting multi value error. i used the below formula.

       

      = DayNumberOfMonth([Date])