Howto make SSRS report parameters depend on each other

Question

My report has two dropdown list -
1) List one shows all fruit-sellers
2) List two shows all fruits

If user selects one seller, I want the second list to show only those fruits sold by selected seller.
If the user select a fruit, I would want to show list of only those sellers selling the fruit (this will effectively override the selected seller in the list)

From whatever I have found, ssrs report does not allow forward dependency. I can only make one of these parameters dependent. How do I achieve two-way dependency**??**

PS: I have third parameter also which is price range - if user selects a price range, I would want only those fruits & sellers which fall in that price range but if I can figure out above problem, I should be able to fix this as well.

Answer 1

This is called Cascading Parameters.

You would want one dataset for your Sellers parameter that lists all the Sellers.

You'd need another dataset for your Fruits parameter. In your fruits dataset, you use a parameter to filter it by the Seller so that only fruits sold by the selected Seller are listed in the Fruits dataset for the Fruit parameter. As you figured, the price range parameter would work similarly.

Here's some good info: http://www.mssqltips.com/sqlservertip/3466/cascaded-parameters-in-sql-server-reporting-services/

And here's Microsoft's info: https://msdn.microsoft.com/en-us/library/dd255197.aspx

Answer 2

Its not elegant but will probably be easiest for end users.

Have two reports one for those interested in where to get a particular fruit. The other for those interested in seller fruit range.

You could finally use price range as a further filter parameter on both reports.

Answer 3

I know you asked this question long ago but I stumbled into it when working with PowerBI Report Builder (formerly known as SSRS).

It is possible to make pulldowns of parameters depending on each other. I assume you have a 'leading' parameter and a 'dependent' parameter.

1. Create a dataset that shows values depending on the value of a parameter, for instance: (under 'Parameters' you should substitute this dataset-parameter for one of your public parameters)

IF @PARAM = 'VALUE'   SELECT DISTINCT * FROM (VALUES      ('Value 1'),        ('Value 2'),        ('Value 3'),    ) AS NAAM(NAAM) ELSE    SELECT DISTINCT * FROM (VALUES      ('Value 4'),        ('Value 5'),        ('Value 6'),    ) AS NAAM(NAAM)

2. Under 'Default Values' (or 'Available Values', according to your needs) of your dependent parameter, you choose the dataset created under (1).

3. Notice that the leading parameter should be prior to the dependent parameter in the parameter list.