SQL Reporting Services Reports from Multiple Databases

I've been trying to work out how to get a single SQL report to run on different databases of the same structure so that I don't have to release each report thirty times to different customers who all have exactly the same structure of database.

There are some ideas out there which I couldn't get to work properly and they wouldn't work in the report deigner anyway so were a clunky to work with.

What I've come up with is a procedure to build a report and, either add the filters at the end so that design is much quicker (I hate having to type usernames and password every time a test a flippin' report), or will work from within the designer as you wish.

I start off with a regular shared DataSource and a report that's pretty much complete;

1. Add a single string Parameter called DatabaseName
2. In the data designer, click the properties button to the right of the current Data Source
3. In the window that opens, click on the properties button to the right of the current Data Source
4. Untick Use shared data source reference
5. Type the following as the Connection String:
   
   ="Data Source=RCOPEH2K3VS;Initial Catalog=" & Parameters!DatabaseName.Value
   
6. Click on the Credentials tab and select Prompt for credentials then type in a prompt (if you want)
   

You're now ready to roll! Close all those windows with Ok and then click on the Preview tab.

First, you'll be asked for a username and password and then you'll be asked for a database. The same happens if you run the deployed report from a web page.

If you have any other parameters for your report, you'll have these requested on the same window as the Database name (though, if you're a developer, there's every chance you'll be able to pass this parameter anyway).

The only drawback I can see from this is that you're using built in SQL Server accounts and not standard AD accounts.