cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic Month to Date Filter in Universe

Former Member
0 Kudos

Hi All,

We have a requirement for a prompt on Order Date (ORD_TABLE.ORD_DT) field. Whenever a user runs the webi report, it should prompt for an order date range. Such as user can run the report between two order dates. if the user doesn't give a date range, the report should automatically run for current month to date.

We are trying to implement it in the universe (UDT) as a filter so that we/users can easily drag the filter into reports (canned and adhoc).

We are running on BO 4.1 14.1.5 and our backend is Oracle.

Thanks in advance for your responses. Let me know if I didn't make it clear.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Create two objects in Universe or IDT:

From_Date = [Date] (Date column from  DATE Dimension)

To_Date = [Date] (Date column from  DATE Dimension)

Both should be Dimension.

Now create another two dimension in UDT or IDT:

From_Date_selected = case when From_Date is null then add_months(sysdate,-1)+1 else From_Date end

To_Date_selected = case when To_Date is null then sysdate else To_Date end

Now on WEBI report level, create two queries(Data providers):

In Query1:

apply prompt on From_Date & To_Date objects and in select drag From_Date_Selected & To_Date_Selected objects.

In Query2:

Drag all the objects required for report in selected clause & in where clause apply condition like

[Date] between [From_Date_Selected] and [To_Date_Selected]

I hope this will help you.

Thanks,

Swapnil