SSRS Dynamic Connection Strings
Quick how to on Dynamic Connections. If you have a report that you want to run against multiple servers, you properly don’t want to create the same report 10-100 times. With SSRS its incredibly easy to set your data source as a dynamic string.
First Create a Parameter called “Server” (enter a default value if you like):
Next go to your Data Source and select the Expression button
Enter the following Code:
="data source=" & Parameters!Server.Value & ";Initial Catalog=master"
In my example im using my code from my blog:
Set up your report like you want, with a query that can run against any server (if you want an quick easy query just to test, maybe use something like
When you run the report, you should end up with something like this (Note the Parameter at the top where you can enter a SQL instance name to connect to and run your dataset agains.):
Type in a new server name and either press Return or click the view Report button and you get details on the new server