Multiple DataBases single report

Ok Here's the senario,

I am currently designing a report which will be used in a production environment by multiple users. Now each of these users might be looking at a different production database, now i want to be able to create a single report that can be pointed at any one of these databases depending on which user calls the report. All the databases have the same structure but may contain different data, so in effect the query I am using can remain the same it is just the datasource connection info that needs to change dependant on the user and the database they are looking at. I could create multiple instances of the same report each with a connection to the different databases, but i would prefere to have only a single report.

I am considering writing a seperate App which the user could launch from within the production environment which would then pass the connection iformation to the report based on what ever user is running it, this however seems like a bit of a hack, Is there a better way of doing it.

Cheers

James R

Can you pass a parameter in when they run the report where the stored procedure will pull the info from the correct server?

For instance, if you had a dataset that contained the log-in user id and a code to identify what server to use (say 'A' would be Prod01 and 'B' would be Prod02)

Then in you could pass the server code to the stored procedure that pulls the data you could do something like

IF @.srvrCode = 'A'
BEGIN
SELECT *
FROM PROD01.DATABASE.dbo.Table
END

IF @.srvrCode = 'B'
BEGIN
SELECT *

FROM PROD02.DATABASE.dbo.Table
END

Hopefully that could offer some assistance