Using a CTE in a SSRS Hidden Cascading Parameter

I had a situation where a client wanted to allow the report developer to tie each year of a report to a different dataset.  The user would choose the report year and based on a hidden parameter set by the developer it would use the correct data set.  The catch was I couldn’t create anything on the server.  The solution I came up with was a hidden cascading parameter using a CTE.
The visible parameter is @AcademicYear with the following parameter labels

20062007

20072008

20082009

20092010

20102011

For the hidden parameter I created a dataset called SnapShot with the following query.

WITH SnapShots
(AcademicYear, Snapshot) AS
(SELECT '20062007','1'
UNION
SELECT '20072008','2'
UNION
SELECT '20082009','2'
UNION
SELECT '20092010','3'
UNION
SELECT '20102011','4'
)
SELECT  AcademicYear, SnapShot
FROM SnapShots
WHERE AcademicYear=LTRIM(RTRIM(@AcademicYearLabel))

The parameter @AcademicYearLabel is based on @AcademicYear.  To create it choose parameters in the DataSet Properties dialog




Click the button to create an expression and choose the following

Category – Parameters

Item – All

Values – AcademicYear

Under Set Expression for: Value change

=Parameters!AcademicYear.Value

To

=Parameters!AcademicYear.Label

Click OK.  The parameter value should be updated.




Now we’ll create a parameter named @SnapShot to go with the dataset.  Set the Name and Prompt to SnapShot, Data Type to Text and Parameter Visibility to Hidden. 




Choose DefaultValues.  Select Get values from a query and choose the SnapShot dataset and SnapShot Value field.







The report is now customizable by dataset.