Change database based on parameters in SSRS

Question

Im trying to retrieve data from our SQL server with the Report Builder. Since we have different databases for different customers I want to switch the database, the table I query has the same name in every database.

The idea I have is to use a parameter, so this is what I came up with:

use @db

Select H.TK_RC, R.Result_Description, count(*) as aantal from dbo.TK_HISTORY as H
LEFT JOIN CWESystemConfig..Result_Code as R on H.Project_ID = R.Project_ID and H.TK_RC = R.Result_Code
Where R.Result_Group = 1 and  H.Project_ID = @pid and H.TK_CD between @startdate and @enddate
group by H.TK_RC, R.Result_Description

The code itself is working, the problem I encounter is when I create a dataset in the report builder. I think it's because the report builder can't find a startingpoint(of course) it keeps throwing the "define query parameters" pop-up, whatever I do it gives me the error: Could not update a list of fields for the query. Verify that you can connect to the datasource etc.

If I remove the use @db it does work, so this is the issue. I can see the issue, since this is a variable the report builder doesn't see a valid query.

I just can't get this to work, anyone with suggestions for a fix or a workaround?

Answer 1

You should be able to do this by using an expression as your datasource connection string. There is no need to use the USE statement in your dataset query.

For a simple connection to SQL Server you would use something like

="data source=mySQLServer;initial catalog=" & Parameters!db.Value

Where your @db parameter has the name of the database

NOTES:

• You must use an embedded datasource - you cannot expressions in a shared datasource
• You should design and test the report using a fixed connection, then once your are happy with it, change the connection to use an expression.

Here's an screen shot of what you should be looking at