Parameter selection for update

Question

I am trying to convert one of our most simple reports to Reporting Services.

The original excel report calls several stored procedures using the results of one to structure the next as it drills down through the data.

I currently have 2 datasets both of which call stored procedures.

The first is called GetGlobalCustomers. This populates a resultset which returns the connection info for each database (we have split our database per customer) and returns the fields ID, Name, Customer and Server.

The second is called GetSchedules which needs the Server and Name provided by GetGlobalCustomers to query the correct database.

However I only want to select the Customer by name, not the individual server/name (remembering which customer is on which server and what their database name is nigh impossible).

So I have a parameter called CustomerName, pushed to the top of the parameters list which presents the user the Customer to select first with the value being the ID. I also have two parameters called DBServer and DBName. How do I set DBServer and DBName to be dependent on the Customer selected? There doesn't appear to be a readily apparent means.

I am guessing that it needs to be part of Available Values and Specify Values but I can't figure out how to say "For this customer ID use this database/Name". I think I could by creating two extra datasets which perform a select by id queries for dbserver/dbname, but if I need to do that each time I want to select by parameter I will have dozens of datasets.

Thanks

Answer 1

You can do that though your datasource with one caveat: you cannot use shared data sources.

The data source connection string can contain parameters in this case. For example:

="Data Source="+ Parameters!P_Environment.Value + ";Initial Catalog=MyDB;"

You are correct in saying that it needs to be part of available values. One possibility is to make the customer reference the full connection string which would be passed to the datasource. So in this case you would have one parameter which presents the customer name as the parameter text and the connection string as value.