Data Source expression not working

Question

Hello, I am trying to use a data source expression but I'm getting an error. Here is my data source expression:

 

="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks"

 

I have created a report parameter called ServerName. However, when I deploy the report and report viewer opens, I get the following error:

 

An error has occurred during report processing. (rsProcessingAborted) Error during processing of the ConnectString expression of datasource ‘AdventureWorks’. (rsDataSourceConnectStringProcessingError)

 

Is there some really obvious step I am missing here? The relevant parts of my rdl are below.

 

Thanks in advance

 

<DataSources>

<DataSource Name="AdventureWorks">

<ConnectionProperties>

<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=AdventureWorks"</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rdataSourceID>(some stuff here)</rdataSourceID>

</DataSource>

</DataSources>

...

<ReportParameters>

...

<ReportParameter Name="ServerName">

<DataType>String</DataType>

<Prompt>ServerName</Prompt>

</ReportParameter>

</ReportParameters>

Answer 1

Found the solution in another thread...

(quoting)

Very simple solution, it turns out... just make sure the ServerName and DBName parameters appear above all other parameters in the report definition.

 

Would be great if this msdn article were edited to mention that!

Answer 2

Useful to note that you need to use '&amp;' in the expression based connection string NOT just an '&' which is suggested in the SSRS help on MSDN here: http://msdn.microsoft.com/en-us/library/ms156450%28SQL.90%29.aspx