Implementing a calendar like filter in the body of a report
I would like to show you how to implement a calendar like filter in the body of a WEBI report like the one below
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 :
Apply a break on [Month] and set it´s property like below
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
Select all objects
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
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.
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...
Another way of setting a filter on the report.
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
thanks for your suggestion and comments!!!
I am trying to create the same calendar but i am getting multi value error. i used the below formula.