SSRS - can connect two datasets but not three

Question

I'm making my first report with Sql Server Reporting Services (2012) and have managed to create two datasets successfully, each one connecting to the same Data Source. This Data source uses an embedded connection and the user can select the server and Database at runtime. The connection string is as follows

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

The two parameters in question are created and everything works fine for these two datasets, the report displays with a choice of servers and databases and displays the relevant data when I click on Show Report.

However, when I try to add a third DataSet (I need a separate SQL command to pull separate data for the footer and header) this one doesn't connect.

I've tried the following:

-Right click on the existing data source to create the data set, selecting the existing source. When I open the query designer it fails to connect

-Create a new data source with a fixed connection string. This works but is not what I want

-Create a new data source with another name and the same connection string as the original. This fails, and is not really what I want anyway.

Is there anything obvious I'm overlooking? For info, I originally had the first two data sets created and running with a fixed connection string before I changed the connection string in the data source to use a dynamic one. I've restarted the report designer since, though, and the first data sets continue to connect using the dynamic string. I tested with multiple databases just to be sure

Have you deployed your report after adding the third data set? — P.Salmon
Never done this before myself but the last 2 sentences in the section Expression-Based connection Strings msdn.microsoft.com/en-us/library/… looks to me like you need to revert to a fixed connection string to do your update then change back to dynamic when ready to publish? — P.Salmon
Yeah, I think that's exactly it. I tried again with the fixed connection string, then previewed the report and finally went back and changed to the data source dynamic connection string and deleted the fixed one. It seems to work. — pickarooney
@P.Salmon you should add this as the answer so it can be accepted allowing future searchers to see there is an accepted answer for this problem — Chris Latta

P.Salmon P.Salmon · Accepted Answer · 2016-05-26T11:21:31

Never done this before myself but the last 2 sentences in the section Expression-Based connection Strings msdn.microsoft.com/en-us/library/… looks to me like you need to revert to a fixed connection string to do your update then change back to dynamic when ready to publish?

SSRS - can connect two datasets but not three

1 Answers