Say you’ve developed a report which returns data based on a inputted year parameter value, e.g. “give me all the sales in 2014”.

How do you provide the year options for the SSRS report?

Well there’s three ways that come to mind.

Select distinct from a date field, e.g. SELECT DISTINCT YEAR(SalesDate) FROM Sales

This would certainly provide you with all the available years but the database could have millions of sales. So it’s not too efficient.

You could manually populate years in the parameter settings in the SSRS report, kind of primitive but it would work.

But for me the robust and efficient way is the solution provided below.

The following SQL query dynamically populates an integer field in a temporary table with years. The query uses a base year variable, which can be set to as far back as when the required data fields and values existed in the database. A loop then provides the years up to and including the current year. This query can be used to generate a dataset for an SSRS report and then this dataset can then be used to provide parameter values for the report. The report will then always create a list of years between the base year and the current year. Meaning the years parameter will never need to be adjusted again.

(For a tutorial on how to turn a month name and year into dates for the first and last day of the month see this tutorial)

/*Create temp table populated with the years from a base year to the present year*/
IF OBJECT_ID('tempdb..#availableYear') IS NOT NULL
    DROP TABLE #availableYear

CREATE TABLE #availableYear ([Year] INT)

DECLARE @baseYear AS INT
DECLARE @i AS INT

/*Change the base year to the earliest year the database has the required data available*/
SET @baseYear = 2013
SET @i = 0

WHILE @i <= YEAR(GETDATE()) - @baseYear
BEGIN
    INSERT INTO #availableYear
    SELECT @baseYear + @i

    SET @i = @i + 1
END

SELECT * FROM #availableYear

Within the Stored Procedure that populates the report you can then do something like below to make sure the date range matches the year chosen.

DECLARE @yearChosen AS INT

SET @yearChosen = 2013

DECLARE @startDate date
DECLARE @endDate date

SET @startDate = CONVERT(CHAR(4), @yearChosen) + '0101'
SET @endDate = CONVERT(CHAR(4), @yearChosen) + '1231'

PRINT @startDate
PRINT @endDate    

--OR For example

YEAR(SaleDate) = @yearChosen