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
Like this:
Like Loading...
You will get the following warning sign below. Limiting the report to one page, by setting the interactive height to zero, means everything needs to be loaded all at once. This will create lag when a user loads the report and interacts with it.
If you are basically using SSRS as a means for end users to pull a data dump via excel, heed this warning. If you just have a few aggregated tables and you want them all on the same page you should be fine.
Tidbytez 