Anthony Brown

Whilst I was looking at building custom reports in management studio, I finally got round to looking at reporting services in sql 2005.

One really neat feature I found is the ability to use an expression to build a connection string. Using this, you can dynamically set which server the report connects to when it runs. You can then embed this report as a subreport on another report and so get it to run against several servers all from one report. If you look after several servers, this can give you a nice easy way to check all your servers are OK.

To do this, first create the report you want to run against each server. Don't worry about the data source while you are designing it - just connect to any machine and make sure it is designed how want.

When you are happy with it, add a parameter to the report called ServerName. This should be a string datatype and the ‘allow blank value’ checkbox should be unchecked.

Next you need to change the datasource to use an expression.(one thing to note is you can’t use an expression on a shared datasource, so you may need to create a new datasource for the report). Edit the datasource and click the fx button next to the connection string and change the expression to

="Data Source=" & Parameters!ServerName.Value

If you now try and preview the report, you need to enter the servername parameter and it runs against whichever server you specify.

Now for the second report. First create a dataset that will return all the names of the servers you want to run the report against.

On the layout tab, add a table and bind it to that dataset. Then drag a subreport into the detail part of the table. Right click on the subreport and select properties and in the subreport drop down box, select the first report you created. Finally go to the parameters tab and in the parameter name column select ServerName and in the parameter value column select the column from the dataset that contains the servername.

In the download on this entry are couple of reports to show how this all works.

The first report is SingleServerInfo that just shows some information that it connects to. You can change which server it connects to using the ServerName parameter.

The second report is MultipleServerInfo and has a table with SingleServerInfo as subreport in one of the cells. This means it is called for each server returned by the dataset on this report (which needs a table called ServerNames which can be created using the script in the zip file)

These reports will only run against sql 2005 servers as they use some of the dynamic management views available on there. However, there is no reason why you cant use this technique to run a report against several sql 2000 servers (or a mixture of versions)

Please make sure you look at the queries these reports run and are happy with them before running the reports against any of your servers.