How to cascade parameters in SSRS having specific

问题:

I have 2 parameters 'Groupby1' and 'Groupby2' in my report,for the first parameters i have specified some values like Column A,column B,Column C. Now i need to make the 2nd parameter cascading based on the first one like if i select Column A in Groupby1 parameter it should display only Column B and Column C in Groupby2 parameter.Is this achievable?

回答1:

Yes, it's easily achievable. The trick is to make a dataset dependent on just the first parameter, and use it's results for the available options of the second parameter.

A little more detail on how you'd make that happen:

1. Create the first parameter with options. (GroupBy1)

2. Create a dataset that uses that parameter either in Where or as a filter.

   SELECT 'Web' as Department WHERE 'IT' in ( @GroupBy1 )
   UNION ALL
   SELECT 'Database' as Department WHERE 'IT' in ( @GroupBy1 )
   UNION ALL
   SELECT 'Accounts Payable' as Department WHERE 'Accounting' in ( @GroupBy1 )
   UNION ALL
   SELECT 'Shipping' as Department WHERE 'Warehouse' in ( @GroupBy1 )
   UNION ALL
   . . .
   

3. Create a parameter that uses that data set as available options. (GroupBy2)

Use any combination of these parameters in your core data query or filters.

One restriction is that the parameters must be ordered in the report so that GroupBy1 is before GroupBy2.