Reporting Services, how to use a connection string from a query result

Question

A report needs to be run on multiple sites, each with its own connection string. These site names and connection strings are stored centrally.

I want to make a report that

1. Accepts the site name as a parameter
2. Queries for the sites connection string
3. Uses the sites connection string to get the report's data

I've tried using two data sources in the report, where the first data source uses the site name parameter to retrieve the connection string and the second uses an 'embedded connection' with a function for its connection string.

=First(Fields!ConnectionString.Value, "SiteDetails")

However when I run the report I get:

[rsFieldInQueryParameterExpression] The expression used for the parameter ‘SiteDataSource’ in the dataset ‘SiteDataSource’ refers to a field. Fields cannot be used in query parameter expressions.

Is there a way around this, to use a connection string from a database?

I am using SQL Server 2008 Reporting Services.

Answer 1

I have had this working in SSRS 2005 by storing the connection string in a hidden report parameter, then setting the connection string of the second query using that parameter.

Answer 2

I wrote a blog post about something similar a while ago, you might find it usefull - http://www.beakersoft.co.uk/2008/06/22/setting-a-data-source-at-runtime-on-sql-reporting-services/