3 Replies Latest reply on Jan 10, 2013 12:05 PM by 901916

    Date filter conditions

      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.

        • 1. Re: Date filter conditions

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

          • 2. Re: Date filter conditions
            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
              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}
              "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}')
              "Vb D Products"."ProductCode in 'c'

              and it works,