title: SQL Server reporting services - is it possible to select database as part of the report
body: <p>We have been trying out SQL server reporting services. We are using SQL 2008 and Visual Studio 2008.</p>
  
  <p>I Have a couple of linked reports, along the lines of<br>
  <strong>Report1</strong>: Summary of tickets that are still open past the date that they should have closed. Click through on a line to:<br>
  <strong>Report2</strong>: Details of a ticket and it’s dependant history data.</p>
  
  <p>We have about ten databases, one for each client, with names like “<code>TicketsDatabase_ClientA</code>” “<code>TicketsDatabase_BCustomer</code>”.<br>
  I have deployed these reports to a SSRS server for internal review and testing, using “<code>TicketsDatabase_ClientA</code>” as the DB on the data source.</p>
  
  <p>Table structures on all the databases are the same. there are other databases as well, including one that can provide us with a list of client databases,</p>
  
  <p>But I’d like to roll them out for all clients’ data. What is the simplest way to deploy these reports so that we can look at all customers’ data? Ideally the report would start with a drop-down to select customer by name and then proceed on the appropriate database.  </p>
  
  <p>If this is not possible, I’d settle for a page that lists customers. It looked like we could upload multiple copies of Report1, and just put a different connection  string on each one. But  Report1 connects to the linked report Report2 by name, so I’d need multiple copies of Report2, each with a different name, and multiple <em>edited</em> copies of Report1, each edited to link to a different version of Report2.</p>
  
  <p>This is looking like a really unpalatable, lengthy manual process that will need to be repeated whenever there’s a new report or a new customer.
  Is there a way to choose the connection to use for a set of reports? What is best practice with this kind of case?</p>
  
  <hr>
  
  <p><strong>Update:</strong></p>
  
  <p>We have ended up with dynamic SQL - The reports have a Parameter bound to a dataset to select the database, and the main dataset using an SQL exec statement, e.g.</p>
  
  <pre><code>declare @dbName varchar(64)
  
   SET @dbName = 
  (SELECT 'TicketsDatabase_' + db.[Name]
   FROM MainDb.dbo.Clients db (nolock)
   WHERE db.Id = @clientId)
  
   EXECUTE ('USE ' + @dbName + '
  
  SELECT Datatable.*
  FROM ...
  WHERE ...')
  </code></pre>