locked
How to get the range of Values in ssrs date parameter RRS feed

  • Question

  • Hi Experts,

    I have a date column as [Submissiondate] in my query and also it is a parameter.

    I want to see it as a from date and to date in parameter .that means I want to select range of values in ssrs parameter.

    if I select from as 30-04-2017 and to as 20-05-2017,i want to get all values in between those dates.

    is there any posssibilty to do in ssrs.

    quick response is highly appreciated.

    Best Regards,

    Tuesday, June 6, 2017 11:27 AM

Answers

  • Hi ds_999,

    Please refer to below steps.

    Create two report parameters named as @StartDate and @EndDate. Set its data type to date/time in parameter properties pane. Here, there is no need to specify any available value or default value for these two parameters so that you can directly use calendar to select date time and filter tablix.

    Then, add filters in tablix properties pane.

    Preview result.

    Alternatively, if you want users to select date from parameter drop down list, and all the date options are those values coming from field [Submissiondate], you could consider below solution.

    First, you should create two extra dataset which returns values for parameter available values.

    //dataset for startdate parameter
    
    SELECT DISTINCT Submissiondate
    FROM            test_1
    
    
    //dataset for enddate parameter
    
    SELECT DISTINCT Submissiondate
    FROM            test_1
    where Submissiondate>=(@StartDate)

    Create two report parameters as mentioned above. The only difference is specifying available values for both parameters. Then, add filters to tablix.

    Preview result.

    If you still have any question, please feel free to ask.

    Best regards,
    Yuliana Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Yuliana Gu Friday, June 16, 2017 2:11 AM
    • Marked as answer by ds_999 Monday, August 27, 2018 10:37 AM
    Wednesday, June 7, 2017 3:14 AM

All replies

  • Hi ds_999,

    Please refer to below steps.

    Create two report parameters named as @StartDate and @EndDate. Set its data type to date/time in parameter properties pane. Here, there is no need to specify any available value or default value for these two parameters so that you can directly use calendar to select date time and filter tablix.

    Then, add filters in tablix properties pane.

    Preview result.

    Alternatively, if you want users to select date from parameter drop down list, and all the date options are those values coming from field [Submissiondate], you could consider below solution.

    First, you should create two extra dataset which returns values for parameter available values.

    //dataset for startdate parameter
    
    SELECT DISTINCT Submissiondate
    FROM            test_1
    
    
    //dataset for enddate parameter
    
    SELECT DISTINCT Submissiondate
    FROM            test_1
    where Submissiondate>=(@StartDate)

    Create two report parameters as mentioned above. The only difference is specifying available values for both parameters. Then, add filters to tablix.

    Preview result.

    If you still have any question, please feel free to ask.

    Best regards,
    Yuliana Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Yuliana Gu Friday, June 16, 2017 2:11 AM
    • Marked as answer by ds_999 Monday, August 27, 2018 10:37 AM
    Wednesday, June 7, 2017 3:14 AM
  • Hi ds_999,

    Have you resolved your problem? If so, please kindly mark the corresponding reply as an answer so that some other users having similar requirement can benefit from it. If I have something misunderstood, please correct me.

    Regards,
    Yuliana Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 7, 2017 9:11 AM
  • Hello,

    I have the above set in my report and it still will not recognize the data ranges when changing months

    Is there anthing else I should look at?  Belwo is my query.

    SELECT        DOCNUMBER, [Batch Date], BATCHID, Entity, [GAAP Validation], [Reported GAAP], [TOC Validation], [Reported TOC], [Responsibilty Center], [FERC Validation], [Reported FERC], Intercompany, [Line Description], CR_Amount, 
                             DR_Amount, aaTRXDimCode, ActivityStatus, Id
    FROM            vw_ConcurPayExtracttoGLFinalFile
    WHERE 
      CONVERT(VARCHAR, [Batch Date] ,105) BETWEEN CONVERT(VARCHAR, @StartDate,105) 
      AND CONVERT(VARCHAR,@EndDate , 105)

    Monday, March 4, 2019 7:49 PM
  • Hello,

    I have a couple of issues. :)

    My filter is changing from date back to text and I'm also not pulling any data from today's date.  Any ideas?

    

    Thanks in advance!

    Tara

    Wednesday, August 14, 2019 10:02 PM