Image with the text 3000 years later in giant letters

How to provide a dynamic parameter drop down of year options in SSRS

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.

/*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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s