This discussion is archived
3 Replies Latest reply: Jan 10, 2013 4:05 AM by 901916 RSS

Date filter conditions

976425 Newbie
Currently Being Moderated
I have a requirement where we take a from and to date value as prompt from the user, I have two filter conditions that will have to run based on the condition the user selects.

1) filter the rows based on user inputs from -to date - run the report this condition
2) based on the to date, need to calculate -7 days from the input values and then filter the report based on the this condition. - ignore the conditions for over 7 days and take in other values.

ex : for 1-Jan-2013 to 30-Jan-2013, give values a and b for a column and for 23-Jan-2013 to 30-Jan-2013 include value c

Request inputs to implement this in reports.

Thanks
  • 1. Re: Date filter conditions
    fiaz0419 Journeyer
    Currently Being Moderated
    Hi.....

    Not complex....... Give the sample data and explain what should be the values....

    fiaz
  • 2. Re: Date filter conditions
    976425 Newbie
    Currently Being Moderated
    from 1-Jan-2013 to 30-Jan-2013 value the user inputs

    1) between 1-Jan to 30-Jan, dispaly all the records for a and b values
    2) 30-Jan -7 days, i.e 23-Jan to 30-Jan dispaly all the records for value c
  • 3. Re: Date filter conditions
    901916 Explorer
    Currently Being Moderated
    Hi,
    It requires some small workaround because in this case between operator can't be applied in prompt while getting input like 01-jan-2013 and 31-jan-2013 and to store it into a presentation variable. So create an alias column for date field.

    For ex) if you have datetime column in the calendar table, create an alias as datetime1
    when desigining prompt, Create presentation variable for datetime1 such as,

    datetime is equal to is in presentation variable date1
    datetime1 is equal to is in presentation variable date2

    Need to create two combined requests in case of selecting a,b records alone in first combined request and c alone in the second combined request

    In the first one, the filter is to be like
    cast("- Vb D Calendar Dates"."DateTime" as date) is between @{date1}{1/1/2013} and @{date2}{31/1/2013}
    and
    "Vb D Products"."ProductCode in 'a','b'


    In the second one, the filter is
    cast("- Vb D Calendar Dates"."DateTime" as date) is between @{date2}{31/1/2013} and TIMESTAMPADD(SQL_TSI_DAY, -7, date '@{date2}{2012-01-31}')
    and
    "Vb D Products"."ProductCode in 'c'

    and it works,

    Regards,
    Anitha.B

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points