SSRS Single report parameter used to set dataset query parameters

Question

I'm a bit of an SSRS noob and would appreciate some assistance with report parameters.

I have a SQL query similar to the following:

SELECT [abc],[xyz],[etc]
FROM [database].[dbo].[db_view]
WHERE [Date] BETWEEN @StartDate AND @EndDate

Normally this will result in two report parameters being created, [Start Date] and [End Date]. However the default date picker scenario becomes tedious for users when running reports several times for a prior periods and can often select incorrect start and end dates.

The preferred approach is to provide users with parameters like [Year] and [Month] and have these defaulted to current values, e.g.: Year:[2014] Month:[March], and use these selections to set the SQL query parameters accordingly, i.e. the latter selection would be used to set @StartDate='2014/03/01' and @EndDate='2014/03/31'

I'm not sure how parameters can be set as described above; any guidance will be greatly appreciated.

Answer 1

Without parameter expressions (requires SQL Server 2012 data source)

1. In SSRS, ensure separate parameters are created: @Year, @Month, @StartDate, @EndDate

2. Use DATEFROMPARTS to build the start and end dates in your dataset

   SELECT [abc],[xyz],[etc]
   FROM [database].[dbo].[db_view]
   WHERE [Date] BETWEEN DATEFROMPARTS(@Year, @Month, @StartDate)
                AND DATEFROMPARTS(@Year, @Month, @EndDate)

Answer 2

In SSRS, add 2 hidden Parameters, Start and End date. Set the defaults of these to an expression. Build out your date strings using the values from your visible year/month parameters, and use CDATE() to convert them to a date type. Use these 2 hidden parameters in your SQL query(s).

For the year/month parameters, simply add those, navigate to their "Available values" property tab, and either hard-code in your collection of months & years, or wire up a dataset to provide the available values to the parameter.