SQL Server 2008 shared parameterized Data Source issues

Question

I have a 2008 r2 SSRS report that works perfectly in VS but when deployed to a SSRS web server, I have issues when the deployed report tries to connect to the embedded data source.

I use an embedded parameterized data source as an expression:

="Data Source=" & Parameters!server.Value & 
";Initial Catalog=" & Parameters!database.Value

Since we need authentication for the embedded data source, I figured that it may be easier to make it a shared data source. When I try to use the connection string above, ni a shared data source, I can't even view the data source in VS. I get the following error:

An error occurred during local report processing.
Object reference not set to an instance of an object.

I doubt that the deployed report will work on the web server until I can get the report to at least render in VS.

Any ideas?

Answer 1

Short answer - you can't use an Expression-based Connection String as a Shared Data Source.

See Books Online, Data Connections, Data Sources, and Connection Strings:

When defining the data source connection, do not use a shared data source. You cannot use a data source expression in a shared data source. You must define an embedded data source for the report.

So that's pretty much it, sorry, it's going to have to be either Shared and non expression-based or Embedded and expression-based.