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