sql server reporting services: Parameter selection that depends upon other parameter values -- sql server reporting services

Parameter selection that depends upon other parameter values

Ed Allison
3/6/2006 4:37:22 PM

sql server reporting services:

Hi everyone,

I am writing a report to display information for sales branches. The report
has two parameters, BranchID and StaffID. Both queries have available
values supplied by stored procedures.

BranchID is easy enough. It's query is a stored procedure with the SQL:

SELECT BranchID, BranchName
FROM Branch

The trouble I have is that we only want to select staff from branch selected
in the other parameter. The values for the StaffID parameter should be
supplied by the query:

SELECT StaffID, StaffName
FROM Staff
WHERE Staff.BranchID = @BranchID

where @BranchID is the BranchID selected as the previous parameter.

What is the best way to to this, please?

I am using Visual Studio 2005 and Reporting Services 2005 but the database
being queried is SQL Server 2000. It would be easy enough to write a
windows front end to select the parameters as I need them, but if at all
possible we want the reports available to be accessed through a browser
only.

Many thanks for any help you can provide. I am relatively new to Reporting
Services, so please go easy if I have missed something obvious in Books
Online or wherever. I tried but could not find anything.

Ed Allison

Re: Parameter selection that depends upon other parameter values

Ed Allison
3/6/2006 5:50:38 PM

Found it. Easy as pie once I found the right bit of msdn. For anyone else
who could not find it, it is explained here under "cascading parameters".

http://msdn2.microsoft.com/en-us/library/ms155917.aspx

Ed Allison

[quoted text, click to view]