title: How can I set a custom SSRS date parameter?
body: <p>I have a series of reports that require date ranges. To "help" the customer, we have a dropdown for quick dates (Last 7 days, this month, last month, etc.) One of the dropdown items is "Custom".</p>
  
  <p>When the user selects anything other than custom, we use a dataset within the Available Values  and Default Values of the StartDate and EndDate parameters to force the correct datestamps, accordingly. </p>
  
  <p>However when the user selects "Custom", we want to allow the user to select ANY date in the StartDate and EndDate parameters. The Available Values for these parameters can then be anything, which can't be done since we have defined a set of Available values. I tried returning NULL from my stored proc, but that just prevents the user from setting anything.</p>
  
  <p>What's the best way to accomplish this?? This task sounded easy at first, but I'm already about to pull my hair out.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I have a dropdown, SelectDateRange, where am pulling my options for the date ranges from the following DB Function:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[ufn_ReportDateRanges]
  (
  )
  RETURNS @Dates TABLE
      (
      DateRangeID   INT,
      DateRangeName VARCHAR(255),
      StartDate     DATE,
      EndDate       DATE
      )
  AS
  BEGIN
  
      INSERT INTO @Dates SELECT 1, 'Today',
                    GetDate(), 
                    GetDate()
      INSERT INTO @Dates SELECT 2, 'Previous 7 Days', 
                    GetDate() - 7, 
                    GetDate()
      INSERT INTO @Dates SELECT 3, 'Current Week',
                    DateAdd(dd, -(DatePart(dw, GetDate()) - 1), GetDate()),
                    DateAdd(dd, (7 - DatePart(dw, GetDate())), GetDate())
      INSERT INTO @Dates SELECT 4, 'Previous Week',
                    DateAdd(dd, -(DatePart(dw, GetDate()) - 1) - 7, GetDate()),
                    DateAdd(dd, -(DatePart(dw, GetDate()) - 1) - 1, GetDate())
      INSERT INTO @Dates SELECT 5, 'Current Month',
                    DateAdd(day,-(DatePart(day, getdate()) + 1), GetDate()),
                    DateAdd(month, 1, DateAdd(day, -DatePart(day, GetDate()), GetDate()))
      INSERT INTO @Dates SELECT 6, 'Previous Month',  
                    DateAdd(month, DateDiff(month, 0, GetDate())-1, 0),
                    DateAdd(month, DateDiff(month, -1, GetDate())-1, -1)
      INSERT INTO @Dates SELECT 7, 'Year To Date',  
                    DateAdd(yyyy, DateDiff(yyyy, 0, GetDate()), 0),
                    GetDate()
  
      INSERT INTO @Dates SELECT 99, 'Custom',  
                    NULL,
                    NULL
  
      RETURN
  END
  </code></pre>
  
  <p>My dataset, DateRanges, is then:</p>
  
  <pre><code>SELECT        DateRangeID, DateRangeName, StartDate, EndDate
  FROM          dbo.ufn_ReportDateRanges()
  WHERE         DateRangeID = @SelectedDateRangeID
  </code></pre>
  
  <p>In my StartDate and EndDate parameters, I then set Available Values to:</p>
  
  <pre><code>Dataset: DateRanges
  Value: StartDate (or EndDate, respectively)
  Label: StartDate (or EndDate, respectively)
  </code></pre>
  
  <p>and their Default Values are set to:</p>
  
  <pre><code>DataSet: DateRanges
  Value: StartDate (or EndDate, respectively)
  </code></pre>
  
  <p>This works GREAT for forcing the fields to always match the dropdown selection. But when the user selects "Custom", I would like to free up the fields to be any date, using the date picker (not a very long dropdown list of all possible dates in all of history). Is this even possible???</p>