locked
Show All Data with a Date Range Filter RRS feed

  • Question

  • I have an SSRS Report with a Date Range Filter that works fine when you select two dates and hit View Report. But I would like to make it so all the data shows by default and then you can narrow it down by the date range filter. Is there a way to make all the data display by default with a Date Range Filter?

    So basically you run the report and ALL data displays then you can use the Date Range Filter to narrow the data down further.

    I've tried everything I can think of and no luck.

    Thanks!

    Tuesday, January 15, 2013 7:34 PM

Answers

  • Hi,

    Say you have two parameters StartDate and EndDate, the simplest solution is to define a default value to both using min and max  functions of the dates available on your dataset.

    By this, create an extra dataset using  SELECT MIN(yourdatefield) as sdate, MAX(yourdatefield) as edate from yourtable group by whateverfield.

    Then use this dataset as default value for the your two parameters, using sdate for your StartDate and edate for your EndDate.

    When your report loads, it will automatically fill in these dates to display that spans  all your data.

    An alternative solution is to use an expression in your main dataset, depending on whether you have other criteria in your where clause other than dates, this might become more complicated. But assuming you only have the date as your criteria, it will be something like this:

    ="SELECT * FROM YourTable " & IIF(IsNothing(Parameters!StartDate.Value) and IsNothing(Parameters!EndDate.Value), "", " WHERE yourDateField BETWEEN " & Parameters!StartDate.Value & " AND " & Parameters!EndDate.Value)

    So the idea is to check if the two parameters have values. If not, don't add a where clause which will return all your dates. If there are values, then add the where clause applying the date range.

    The sql expression above is just an example to give you an idea. The syntax maybe wrong depending on your database. You will have to build the proper sql string to process the dates and use functions to convert them from string to dates for evaluation.


    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z


    • Edited by krootz Tuesday, January 15, 2013 7:56 PM
    • Proposed as answer by Fanny Liu Wednesday, January 16, 2013 1:46 AM
    • Marked as answer by CrazyeD1583 Wednesday, January 16, 2013 2:29 PM
    Tuesday, January 15, 2013 7:54 PM