Using Dynamic Connection Strings in SSRS2008

Question

Hi,

I have a bunch of SSRS 2008 reports which I'd like to run on several different machines (development, test, production). Each machine has it's own database, so I need to use different connection strings depending on where the report is running.

One workaround I found is to specify the server and catalog name in a hidden parameter that is passed to the report at runtime. It's described in this tutorial, but it applies to SSRS 2005 and I could not make it work in SSRS 2008.

Everything works fine when the connection string in my shared datasource looks like this:

Just so to see if expressions can be used at all for the connection string, I replaced the connection string with this:

But this gives me the following error when I try to preview the report in Visual Studio:

An error occurred during local report processing. The item '/Zeiterfassung-Adrian' cannot be found.

Are dynamic connection strings still working in SSRS 2008?

If yes, what am I doing wrong?

If not, what else can I do?

Answer 1

What about using a hidden report parameter?

then you should be able to do:

="data source=" & Parameters!MyServerParameter.Value & ";initial catalog=DBName.."

Here is an article which should help you out: http://msdn.microsoft.com/en-us/library/ms156450.aspx

Answer 2

The string should have double double quotes for server name. ="data source="" & Parameters!MyServerParameter.Value & "";initial catalog=DBName.."

Answer 3

Just create your report datasource for each environment and deploy them. Then switch your deploy option to not overwrite a datasource. Though to make for simple deployments you will have to configure the configuration manager in bids for each environment. This is how we work in our multiple environments.

Hope it helps, let me know if you have any questions on this.