Parameters in Microsoft SQL Server 2000 Reporting Services

There are numerous types of parameters but some of the most useful to know are:

1: Selecting All or One item from a list
and
2: Selecting The Year and Month or Year, Month and Week.

The more you plan your reporting system, the less reports you need to write and the less you need to modify. One reporitng system I was recently involved with had 15 reports but only 5 reports were required. With parameter driven reporting, you can allow the user to generate their information filtered, sorted or visible in many different ways.

1: Selecting All or one item from a list First Create a Dataset to return @Parameter1 :
SELECT DISTINCT Name FROM Organisations
UNION
SELECT '.All' AS Name
ORDER BY 1

Then the main dataset add to the front IF @Parameter = '.All' SELECT And after the SQL, add ELSE and repeat the SQL

2: Selecting The Year and Month or Year, Month and Week. For This a set of Datasets where one is filtered by the other, provides the final Parameters for your main dataset.

Dataset Called Year
select distinct year(date) as Year from vTimes
order by 1 desc
Note how this is sorted in descending order. This is so the latest Year ( parameter) is displayed.
Also note, is one of the columns returned in another dataset is also Year, then Reporting services will return an error. Change the name of the dataset to fix this. E.g. Year1

Dataset Called Month
select distinct month(date) as Month from vTimes
where year(date) = @Year
order by 1 desc

Dataset called Week
select distinct datepart(wk,date) as Week
from vTimes
where year(date) = @StartYear and month(date) = @StartMonth
order by 1 desc

Note how we used the datepart and not the datename function as this doesn