locked
Setting Date Filter in SSRS RRS feed

  • Question

  • User-1938990490 posted

    I searched everywhere, but did not find anything to my issue.  I guess not a lot of people using SSRS, because it ...


    Here is the scenario:

    I'm trying to create a report (rdl) againt SP list.

    I have a Date parameter, just 1.

    When a user is selecting a date.  It checks against my queried date, a day before and a day after.  It works fine.

    When a user is NOT selecting the date.  I want the my queried date between a MINIMUM date and a MAXIMUM date.  Since I couldn't find the Min & Max date in SSRS report filtering, I trying to hard code the date. 

    I tried DateValue(...with variety of different format...), as well as CDate(...with variety of different format...). 

    None of them is working.  What exactly is the format SSRS accepting? 

    As for my CDate(Fields!ows_Date.Value)'s result, it is displaying as "yyyy-mm-dd hh:mm:ss", I also tried that format, and still, not working.


              <Filter>
                <FilterExpression>=CDate(Fields!ows_Date.Value)</FilterExpression>
                <Operator>Between</Operator>
                <FilterValues>
                  <FilterValue>=IIf(IsDate(Parameters!DateParameter.Value), DateAdd("d", -1, Parameters!DateParameter.Value), DateValue("01/01/1900"))</FilterValue>
                  <FilterValue>=IIf(IsDate(Parameters!DateParameter.Value), DateAdd("d", 1, Parameters!DateParameter.Value), DateValue("01/01/2100"))</FilterValue>
                </FilterValues>
              </Filter>

    Any pointers are appreciated.

    :(

    Friday, January 28, 2011 2:50 PM

All replies

  • User-1938990490 posted

    First of all, there is nothing wrong with DateValue() or CDate().

    Secondly,  I thought it was IsDate(), because it is expecting an object, and the actual value is null.  I changed IsDate() to IsNothing(), still not work.  I even changed it to: CStr(...) is nothing or CStr(...) = "", still not work :(


    Then, I got across this link:

    http://stackoverflow.com/questions/1229575/passing-date-parameters-to-oracle-query-in-ssrs

    which doesn't make a lot of sense.  But testing, it is somewhat true.  Apparently, "the problem is with using a parameter name twice.


    Let re-examine the case, I have 1 Date parameter against the record's date.

    If the date parameter Has Value, I added 1 day before, and 1 day after ==> parameter's date - 1 day<= record's date <= parameter's date + 1 day.  It's work, which proving the statement is incorrect.

    If the date parameter is NULL, it's break; proving the statement is correct. 

    I changed my filter to be: if null, Min Date <= record's date, it works.  But it's not exactly what I wanted, because the case of Has Value, it will return:  input Value <= record's date; rather than,  input Value - 1 day <= record's date <= input value + 1 day


    I, even added another parameter.  In this, I have: From Date, and To Date parameters.  Consequently, the comparision is:  From Date <= record's date <= To Date.  It still not work, if the From Date & To Date are null.  Yes, in case if null, From Date = Min Date, and To Date = Max Date.


    The problem is using the "record's date" twice ???

    :(

    If I make any sense at all.

    Thursday, February 3, 2011 5:25 PM