Friday, November 5, 2010

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

image


Click the image 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






image


Click OK.  The parameter value should be updated.

image


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. 

image


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

image





The report is now customizable by dataset.

No comments:

Post a Comment