locked
Report Builder 3.0 - Multi Date Filter RRS feed

  • Question

  • Hi,

    I'm trying to create a RB3 report that has a date filter on 2 date fields (Date Logged / Date Completed) within the report. I've setup 4 Parameters ( @DateLoggedStart / @DateLoggedEnd / @DateCompStart / @DateCompEnd). All parameters are set to date fields and accept 'null' values.

    I've then setup 2 filters (LoggedDate Between @DateLoggedStart and @DateloggedEnd) / (CompletedDate Between @DateCompStart and @DateCompEnd)

    My problem is, the end user may only enter a DateLoggedStart date and leave the other parameter blank. If you do this, the report returns no data. 

    If the user enters a start and end date on the DateLogged parameter, it then returns data, but it isn't correct. It only returns data where the Date Complete date is not entered.  

    Even though I don't enter any dates into the completed date parameter, the report still seems to filter on them. I need someway of getting the report to ignore the filter if the parameters are NULL.

    Does anyone know how to create dynamic date filters, so the user can enter any combination of the parameters and it will return the data correctly?

    Regards,

    Nick

    

    Wednesday, September 25, 2013 2:37 PM

Answers

  • Hi Nick,
     
    According to your description, it seems that this issue occurs when you attempt to create four parameters to filter two date fields, while even though you don’t enter any date into the parameter, the report still filter the parameter with the value “NULL”, so that the report cannot return correct data.

    In Reporting Service, if we Allow null value in Date/Time type parameter, it will select NULL by default when we render the report. And the “NULL” represent a value, so it will filter the parameter with the value “NULL”.  After testing it in my own environment, we can create an extra parameter to give a default date when one date parameter with NULL value. We can refer to the following expression in the default value to work around this issue:

    =IIF(Parameters! DateCompEnd.Value is nothing,today,Parameters! DateCompEnd.Value )

    And then use the new inserted parameter on the query to filter data.

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong

    Thursday, September 26, 2013 1:47 PM