Creating Cascading Parameters with SSRS

I was creating a report today for servers with a specific Operating System where I needed to include two parameters (Operating System and Service Pack level.)  The values of the second parameter need to be dynamic based on the value selected for the first parameter.  This can be accomplished using cascading parameters.

1. Create the first parameter (OSVersion) for the Operating System.
2. Create a new Dataset (OSServicePacks) (with a query that references the first prompt) that will be used to populate the values for the second parameter.
   (I created an expression for my query, because I want to search for the word Server and the Windows Version, which is particularly necessary since Windows 7 and Server 2008 R2 are both version 6.1
   =”SELECT DISTINCT v_GS_OPERATING_SYSTEM.CSDVersion0 FROM v_R_System JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID WHERE v_R_System.Operating_System_Name_and0 LIKE ‘%Server%” & Parameters!OSVersion.Value & “%'”)
3. Create the second parameter (OSSP) for the Service Pack selection.  Because I want the values of this parameter to be dynamic based on the various Service Pack levels of servers with the specified Operating System I have gone to the Available Values section of the Parameter Properties window and made the following selections
   • Select from one of the following options: “Get values from a query”
   • Dataset: OSServicePacks
   • Value Field: The field that will contain the value of the parameter
   • Label Field: The field that will contain the label for the parameter – the text that will actually appear in the parameter drop-down list (this can be the same as the Value Field)
4. Create the Dataset that will return the data for the body of the report (that will reference one or both of the parameters.)
5. Run the report.  The second parameter will be greyed out until a selection is made for the first parameter.  Then the values in the second parameter will be dynamically populated.
6. If the value for the first parameter is changed, the values for the second parameter will be re-populated based on the new value for the cirst parameter.