SSRS报表支持双日期时间参数吗？空参数时可用预定义参数组？

Absolutely, you can set up both sets of date parameters in SSRS and configure them to work flexibly—let’s break this down step by step:

1. Adding the Predefined Period Parameters (@periodstart & @periodend) #

First, you’ll need to create the datasets for these parameters, then link them to new SSRS parameters:

Step 1: Create Datasets for Predefined Dates #

• For @periodstart, create a new dataset with this SQL query:

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0) AS time, 'last quarter start' AS timename
UNION ALL
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0) AS time, 'this quarter start' AS timename
UNION ALL
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS time, 'this year start' AS timename
UNION ALL
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) AS time, 'last year start' AS timename

• For @periodend, create another dataset with this query:

SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)) AS time, 'last quarter end' AS timename
UNION ALL
SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0)) AS time, 'this quarter end' AS timename
UNION ALL
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS time, 'this year end' AS timename
UNION ALL
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), -1) AS time, 'last year end' AS timename

Step 2: Create SSRS Parameters #

• Go to the Parameters pane in SSRS, add two new parameters named @periodstart and @periodend.
• For each parameter:
  • Set the Data type to DateTime (to match your EntryDay field).
  • Under Available values, select "Get values from a query".
  • Choose the corresponding dataset you created, set Value field to time and Label field to timename.
  • You can set a default value if you want (e.g., "this quarter start" for @periodstart).

2. Allowing @startdate/@finishdate to Be Null & Using Only Predefined Parameters #

Yes, you can configure this—you’ll need two changes: adjust your main dataset’s WHERE clause, and enable null values for the existing date parameters.

Step 1: Enable Null Values for Existing Parameters #

• In the Parameters pane, select @startdate and @finishdate.
• Check the Allow null value option under the General tab. This lets users leave these parameters blank.

Step 2: Update the WHERE Clause for Flexible Filtering #

Your current WHERE clause uses two overlapping BETWEEN conditions, which would only return rows that fall in both date ranges. Instead, we need logic that uses either the custom date range (if filled) or the predefined period (if custom dates are null). Replace your WHERE clause with this:

WHERE RequestSource IN (@reqsource)
AND (
    -- Use custom date range if both @startdate and @finishdate are not null
    (@startdate IS NOT NULL AND @finishdate IS NOT NULL AND EntryDay BETWEEN @startdate AND @finishdate)
    OR
    -- Use predefined period if custom dates are null
    (@startdate IS NULL AND @finishdate IS NULL AND EntryDay BETWEEN @periodstart AND @periodend)
)

This logic ensures:

• If the user fills in @startdate and @finishdate, the report uses those dates to filter.
• If the user leaves both @startdate and @finishdate blank (null), the report uses the predefined @periodstart and @periodend instead.

Optional: Handle Partial Nulls (If Needed) #

If you want to account for cases where only one custom date is filled, you can adjust the logic further, but based on your question, the above should cover your core requirement.

Final Notes #

• Test different parameter combinations to make sure the filtering works as expected (e.g., custom dates filled, custom dates null, etc.).
• Ensure all date parameters use the same DateTime data type to avoid mismatches.

内容的提问来源于stack exchange，提问作者ERud