SQL Server reporting services - is it possible to select database as part of the report

Question

We have been trying out SQL server reporting services. We are using SQL 2008 and Visual Studio 2008.

I Have a couple of linked reports, along the lines of
Report1: Summary of tickets that are still open past the date that they should have closed. Click through on a line to:
Report2: Details of a ticket and it’s dependant history data.

We have about ten databases, one for each client, with names like “TicketsDatabase_ClientA” “TicketsDatabase_BCustomer”.
I have deployed these reports to a SSRS server for internal review and testing, using “TicketsDatabase_ClientA” as the DB on the data source.

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,

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.

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 edited copies of Report1, each edited to link to a different version of Report2.

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?

---

Update:

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.

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 ...')

Answer 1

Disclaimer: I have really only used SQL Server Reporting Services 2005

I think you have three options.

1) Use dynamic connection strings ala sqlservercentral.com

2) Get a stored procedure to select the data based on input for you.

3) Get SSRS to get the data from a web service.

Answer 2

I'd consider using a ReportViewer control in local mode

This allows the database connection to be managed by ASP.NET rather than Reporting Services.

Using a pure SSRS solution here is awkward as you mentioned.

Answer 3

I don't know if this fits in with your request for "best practice" or whether it could be better described as "nasty hack" :) However, here's what we do in this situation:

We have a central database. In it is a table called Databases with a list of client and database names. For example:

ClientName      DatabaseName
Client A        TicketsDatabase_ClientA
B Customer      TicketsDatabase_BCustomer

We add a dataset to the report called Databases which has the following Sql statement:

SELECT ClientName, DatabaseName FROM Databases

We add a string parameter to the report called Database which uses this dataset as its "From Query" setting with the Value field being DatabaseName and the label field being ClientName. We also add a small 6pt font label on the report header with the expression =Parameters!Database.Label so we know what database this report is using.

So now we have a way to choose the database and as we create more databases we can add them to our centralised table and all reports that allow choosing databases will automatically have the new database as an option.

Now we simply have to update the Sql statement for our main dataset that the report is based on to take information from the correct database, like so (remember, the Sql statement is just a string expression so, like everything else in Reporting Services, you can build it dynamically):

="SELECT Field1, Field2, Field3 "
&"FROM " & Parameters!Database.Value & ".dbo.MyTable "

Sql Server doesn't mind jumping out of the current database to look in another database as long as the credentials are acceptable, so doing this allows you to dynamically select data from whatever database you want to, regardless of where the actual data source is connected to.

Of course, this assumes that your table structures are the same for the data you are reporting on.

Answer 4

You could always consume an SSIS package from SSRS.

This could perhaps have a looping container which could dynamically pick up all data for "each database name held in a table"

That way you would only need to insert a new row into the table and the SSIS package would automatically pick up the data.