none
SSRS Date Range Filter

    Question

  • I need your help in applying Date Range filter on a report just like in SCSM Defualt report have. In my report i used this querry.

    IncidentDimvw.CreatedDate BETWEEN (@StartDate) AND (@ENDDATE)
    

    And in Paramater properties select datatype as Date/Time which gives me a datepicker like calender. But i want some interactive view here just like in SCSM Default reports have.

    Date Filter


    Regards, Muhammad Ahsan R.E @ Pakistan Petroleum Limited


    Thursday, June 20, 2013 3:56 PM

Answers

  • Hi Ahsan,

    We cannot create a date time parameter which looks like in your screenshot. The Datetime parameter show as a  DateTimePicker in Reporting Services currently. If you have any concerns about this feature, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product.

    Thank you for your understanding.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, June 21, 2013 5:54 AM
  • Are you saying you want to have a datepicker as well as a list of options with "Last Week, Yesterday..."?

    Won't that be redundant?

    Note: I'm only speaking in terms of SSRS, which is pretty inflexible in terms of UI. You could probably achieve something similar to your example above with asp.net etc.

    Friday, June 21, 2013 7:43 AM

All replies

  • Hi,

    If you are using only SSRS then this can't be achived because SSRS doesn't provide much functionalty on parameters.

    But if you are using any .net language then you can create one criteria page (i.e a .net page where user wil select the filter values as mentioned in your question), and you can pass these value to SSRS to generate the report (you have to use report viewer based on the version of .net language)

    -------------------------------------------------------------------------------------------------------------------------------

    --A Meet

    Friday, June 21, 2013 5:46 AM
  • Hi Ahsan,

    We cannot create a date time parameter which looks like in your screenshot. The Datetime parameter show as a  DateTimePicker in Reporting Services currently. If you have any concerns about this feature, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product.

    Thank you for your understanding.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, June 21, 2013 5:54 AM
  • You can't have detailed cascading menus like that for parameters in SSRS.

    You can have, for example an option like "Last Week".

    Set in parameters' Available Values a value with label = Last Week and for it's value enter the expression:

    =dateadd(day, -7, getdate()) 

    That way when you select "Last Week" as your value, last weeks datetime is passed as the parameters value. Likewise you can set different expressions for the other values using SSRS' Date and Time functions.

    Just change the parameter type to Text so you get a dropdown list instead of a datepicker.

    Friday, June 21, 2013 6:08 AM
  • Hi Meet, right now i am developing these reports with Report Builder 3.0. if i`ll work on visual studio, it is possible?

    Regards, Muhammad Ahsan R.E @ Pakistan Petroleum Limited

    Friday, June 21, 2013 7:05 AM
  • Thanks Inarius, you are right, but if i set in parameter`s some available values, I can not avail the facility of datepicker. Is that possible client will avail both facilities in these reports, If yes, then how can i add that.

    Regards, Muhammad Ahsan R.E @ Pakistan Petroleum Limited

    Friday, June 21, 2013 7:10 AM
  • Are you saying you want to have a datepicker as well as a list of options with "Last Week, Yesterday..."?

    Won't that be redundant?

    Note: I'm only speaking in terms of SSRS, which is pretty inflexible in terms of UI. You could probably achieve something similar to your example above with asp.net etc.

    Friday, June 21, 2013 7:43 AM