How to enable cascading parameters in ssrs

Question

I have 5 parameters on my report. They are @time_from, @time_to, @order_id, @material, @batch, and @sscc

I need to do following:

1. Select time range
2. Fill filters
   1. All orders
   2. All batches
   3. All SSCC

3. Then I must show all data. (I did until 4th condition)

4. If all data loaded Operator has option to select 1 order from dropdown, this should reload the filters and only show sscc, batch, and material related to that order.

First, after users choose @time_from and @time_to, the other parameters will be enabled. But rhe fourth case breaks down the first three conditions. In the fourth condition; @material, @batch and @sscc depends on order_id. That's why I use cascading parameter. But if I don't select order_id, I cannot select @material, @batch, and @sscc because they will be disabled.

After selecting @time_from and @time_to parameter, I want to see all parameters and I want @material, @batch, and @sscc to depend on order_id.

For example; when I select @time_from and @time_to I must choose @material but when I select @time_from and @time_to and then I choose @order_id, @material, @batch and @sscc must depend on @order_id.

I want to use cascading parameters, but the parameters must not be disabled.

I attached some screenshots. How can I do this?

1-all parameter depends on time_from and time_to parameter

2-after I choose @time_from and @time_to I want to show all parameter

Answer 1

If you allow null values, you can then set up a SQL query to power your parameters along the lines of..

select
    'All' as [Label],
    null as [Value]

union all

select 
    o.Name as [Label],
    o.ID as [Value]
from dbo.Orders as [o]

This assumes that your orders have columns for Name and ID. You can then use this to power the list of available values for the parameter for Orders. You can then filter your later queries by using..

where (b.ID in (@Orders) or (@Orders) is null)

This will check to see if the OrderID is in the list, or if the list of orders is null (thus returning all).