Hi, You can use Cascading parameter. See this link http://www.resultdata.com/adding-cascading-parameters-to-a-ssrs-report/ http://beyondrelational.com/modules/2/blogs/101/posts/13370/ssrs-51-using-parameters-in-ssrs-cascading-parameter-2.aspx Hope this will help you !!! Sanjeewan

Yes this is possible. You just have to make sure the Department report parameter is above the Employee Parameter in the RDL file. Here's my report parameter snippet from the rdl <ReportParameter Name="UserName"> <DataType>String</DataType> <DefaultValue> <Values> <Value>=User!UserID</Value> </Values> </DefaultValue> <Hidden>true</Hidden> </ReportParameter> <ReportParameter Name="MediaServer"> <DataType>String</DataType> <Prompt>Media Server</Prompt> <ValidValues> <DataSetReference> <DataSetName>NodeFamily</DataSetName> <ValueField>Pkey</ValueField> <LabelField>Name</LabelField> </DataSetReference> </ValidValues> </ReportParameter> <ReportParameter Name="FKDevice"> <DataType>String</DataType> <AllowBlank>true</AllowBlank> <Prompt>Device</Prompt> <ValidValues> <DataSetReference> <DataSetName>Devices</DataSetName> <ValueField>Pkey</ValueField> <LabelField>FullDeviceName</LabelField> </DataSetReference> </ValidValues> </ReportParameter> In my case the Employee dropdown is populated by a stored proc, so I just set the parameter in the DataSet Parameters to the Report Parameter. This the snippet from my rdl for the dataset portion. <DataSet Name="Devices"> <Query> <DataSourceName>CCMDataSource</DataSourceName> <QueryParameters> <QueryParameter Name="@DeviceType"> <Value>11</Value> </QueryParameter> <QueryParameter Name="@NodeFamily"> <Value>=Parameters!MediaServer.Value</Value> </QueryParameter> <QueryParameter Name="@UserName"> <Value>=Parameters!UserName.Value</Value> </QueryParameter> </QueryParameters> <CommandType>StoredProcedure</CommandType> <CommandText>proc_GetSecureReportingDevices</CommandText> </Query> <Fields> <Field Name="Pkey"> <DataField>Pkey</DataField> </Field> <Field Name="FullDeviceName"> <DataField>FullDeviceName</DataField> </Field> </Fields> </DataSet>

Hello In SSRS 2008 R2 using BIDS, is it possible to create a report with two dropdown lists where the available choices of the second list dynamically change depending on what is selected in the first drop down list? E.g. List 1 = Departments List 2 = Employees (of the selected department) There is an existing post but it talks about WebParts and InfoPath. We don't have any of these and need a solution in pure SSRS. Amir

Hi, You can use Cascading parameter. See this link http://www.resultdata.com/adding-cascading-parameters-to-a-ssrs-report/ http://beyondrelational.com/modules/2/blogs/101/posts/13370/ssrs-51-using-parameters-in-ssrs-cascading-parameter-2.aspx Hope this will help you !!! Sanjeewan

Yes this is possible. You just have to make sure the Department report parameter is above the Employee Parameter in the RDL file. Here's my report parameter snippet from the rdl <ReportParameter Name="UserName"> <DataType>String</DataType> <DefaultValue> <Values> <Value>=User!UserID</Value> </Values> </DefaultValue> <Hidden>true</Hidden> </ReportParameter> <ReportParameter Name="MediaServer"> <DataType>String</DataType> <Prompt>Media Server</Prompt> <ValidValues> <DataSetReference> <DataSetName>NodeFamily</DataSetName> <ValueField>Pkey</ValueField> <LabelField>Name</LabelField> </DataSetReference> </ValidValues> </ReportParameter> <ReportParameter Name="FKDevice"> <DataType>String</DataType> <AllowBlank>true</AllowBlank> <Prompt>Device</Prompt> <ValidValues> <DataSetReference> <DataSetName>Devices</DataSetName> <ValueField>Pkey</ValueField> <LabelField>FullDeviceName</LabelField> </DataSetReference> </ValidValues> </ReportParameter> In my case the Employee dropdown is populated by a stored proc, so I just set the parameter in the DataSet Parameters to the Report Parameter. This the snippet from my rdl for the dataset portion. <DataSet Name="Devices"> <Query> <DataSourceName>CCMDataSource</DataSourceName> <QueryParameters> <QueryParameter Name="@DeviceType"> <Value>11</Value> </QueryParameter> <QueryParameter Name="@NodeFamily"> <Value>=Parameters!MediaServer.Value</Value> </QueryParameter> <QueryParameter Name="@UserName"> <Value>=Parameters!UserName.Value</Value> </QueryParameter> </QueryParameters> <CommandType>StoredProcedure</CommandType> <CommandText>proc_GetSecureReportingDevices</CommandText> </Query> <Fields> <Field Name="Pkey"> <DataField>Pkey</DataField> </Field> <Field Name="FullDeviceName"> <DataField>FullDeviceName</DataField> </Field> </Fields> </DataSet>

Thanks: both links were very helpful Amir

Thanks for the code sampleAmir