How can I set a custom SSRS date parameter?

Question

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".

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.

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.

What's the best way to accomplish this?? This task sounded easy at first, but I'm already about to pull my hair out.

UPDATE

I have a dropdown, SelectDateRange, where am pulling my options for the date ranges from the following DB Function:

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

My dataset, DateRanges, is then:

SELECT        DateRangeID, DateRangeName, StartDate, EndDate
FROM          dbo.ufn_ReportDateRanges()
WHERE         DateRangeID = @SelectedDateRangeID

In my StartDate and EndDate parameters, I then set Available Values to:

Dataset: DateRanges
Value: StartDate (or EndDate, respectively)
Label: StartDate (or EndDate, respectively)

and their Default Values are set to:

DataSet: DateRanges
Value: StartDate (or EndDate, respectively)

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???

Anup Agrawal Anup Agrawal · Accepted Answer · 2014-04-30T04:21:16

Update

Option 3

Set default values to None.

When the user selects Last 7 days, Last Month etc in DateRangeParameter. it doesn't populate startdate and end date. You table datasets calculates the startdate and enddate based on the dateRangeId entered by the user.

When user picks Custom and enters the startdate and enddate, your table dataset will use the user selected startdate and enddates.

Option 1. Why do you need available values. I think you should keep it as None. Only customize the default values tab.

Option 2. If option 1 doesn't work try this.

You would need to update the available value dataset to pull all the dates.

For Startdate try something like this

WITH CTE as
(
SELECT 'Custom' QuickDateParam, CAST('01/01/1980' as datetime) as D
  UNION ALL
SELECT 'Custom' QuickDateParam, DATEADD(d,1, D)
  FROM CTE
  WHERE D < getDate()
)

SELECT QuickDateParam, D FROM CTE
UNION ALL
SELECT 'Last7Days', DATEADD(d, -7, GETDATE())
UNION ALL
SELECT 'LastMonth', DATEADD(m, -1, GETDATE())
option (maxrecursion 0)

In the dataset for available values pass the QuickDateParam and it will show the corresponding dates.

Same way you can do for the end date also.

Note: Can you share your datasets and data for Default Values/Available values as that will make it easier to answer your question.

How can I set a custom SSRS date parameter?

2 Answers