Skip to Content
author's profile photo Former Member
Former Member

Dynamic Month to Date Filter in Universe

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jan 13, 2016 at 09:12 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.