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