Friday, November 24, 2006 2:47 PM
antxxxx
Running a report against several servers in RS2005
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.