Creating a multi-server query SSRS report using Central Management Servers

By:   |   Comments (31)   |   Related: > Reporting Services Development


We would like to have a single SQL Server Reporting Services (SSRS) report that will display SQL Servers versions for all of our SQL Servers that are registered in Central Management Server.


Sometimes it is not easy to have results from different data sources in one data set. To help with the solution we will create two reports - a parent report and a sub-report with a dynamic data source connection. The parent report will display current versions of all managed SQL Servers and it will look as though it is a single table.

Dynamic Connection String

In this report we will use a dynamic connection string. Starting with SQL Server 2005 Microsoft introduced Expression-based Connection Strings. You can have report parameters that will allow you to select different SQL Server names and use this parameter in the data source expression:

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

Create the Subreport

  1. Create a blank report named "_Demo_SubReport_1"
  2. Add the new parameter to the report:
    • Set name to "isp_SQLServer"
    • Set "Select parameter visibility" to "Hidden"
    • Set "Available Values" to  - "None"
    • "Default Value" - "None"
    • Data type - "Text"
  3. Create the new Data Source:
    • Give it any name (we will set it to "Dynamic_DataSource")
    • Select "Embedded connection" and click on the "Expression" button:

      Data Source

    • Enter the following expression for the Connection string (note that we use the report's parameter as Server Name in the connection string):

      ="data source=" & Parameters!isp_SQLServer.Value & ";initial catalog=master"
    • Make sure that the Credentials are set to the authentication that you will be using to run the report

  4. Create the new Dataset using "Dynamic_DataSource" created in step 3:


    • Use this query (or other query that you want to run against all SQL Servers):

      SELECT  @@SERVERNAME AS Server_Name, 
       SERVERPROPERTY ('ProductVersion') AS [Version], 
       SERVERPROPERTY ('Edition') AS [Edition]
    • Manually add fields under "Dataset Properties" (we have to add them manually because we are using a dynamic database connection):

      Dataset Fields

  5. In the Design View of the report:
    • Add a table with three columns using the Dataset created in the step 4:

      Tablix Properties


    • Resize the columns: "Version" to 1.5 inches, "Server_Name" and  "Edition" to 3 inches (we will use these sizes later in the parent report)
    • Delete the title (column names) row:
      Delete the Title row
    • Reduce the report's area to fit the table's height:

      Final subreport
  6. Save the report.

Creating the Parent Report

  1. Create the new blank report named "_Demo_Report_1"
  2. Add the new Shared Data Source to the project:

    Shared Data Source

    This could be connection to the msdb database on your Central Management Server (CMS) or connection to another data source that contains your SQL Servers' names. In our example we will use connection to the CMS server.

  3. Create the new report's Data Source using the Shared Data Source created in step 2:

    using the Shared Data Source created in the step 2

  4. Add Dataset using the Data Source from step 3:

    Add Dataset using the Data Source from the step 3

    Use the query below to get SQL Server names registered in CMS (or use the query to your own data source with SQL Server names):

    SELECT FROM dbo.sysmanagement_shared_registered_servers s
  5. In design view add to the report a List from the Toolbox items. This will create a Tablix report item. Set the "DataSetName" to the "Servers_List" dataset created in the step 4 using the Tablix item's property:

  6. Drag the "Subreport" item from the Toolbox to the Tablix area:
  7. Right click the "Subreport" and set the following properties:
    • "All_Versions" as the "Name"
    • Select "_Demo_SubReport_1" from the "Use this report as a subreport" drop-down list:


    • Under the "Parameters" property add the new parameter and select "isp_SQLServer" under the "Name" column and select "[name]" as the "Value":

      Subreport parameter

    • Set the height of the Subreport the same as the height of the Tablix.
  8. Add three textboxes above the Subreport. These textboxes will be the columns titles:
    • Resize the textbox similar to the columns in Subreport: "Version" to 1.5 inches, "Server_Name" and  "Edition" to 3 inches
    • Select all three textboxes and set background color and borders:
      Subreport title

      Subreport title properties
  9. Save and preview the report:
    Final Report

Customizing the Report

To make the report more flexible you can add a parameter to the parent report and select a group or parent group in CMS:

CMS Groups

In order to do this replace the "Servers_List" dataset's query with this:

WITH RegServers (parent_id, server_group_id, name ) AS 
    SELECT parent_id, server_group_id, name 
     FROM dbo.sysmanagement_shared_server_groups 
    WHERE parent_id IS NULL
    SELECT e.parent_id, e.server_group_id, 
     FROM dbo.sysmanagement_shared_server_groups AS e
         INNER JOIN RegServers AS d
         ON e.parent_id = d.server_group_id 
SELECT, g.parent_id, as parent_group_name, g.server_group_id, as group_name
 FROM RegServers AS g JOIN dbo.sysmanagement_shared_registered_servers AS s
  ON s.server_group_id = g.server_group_id
 JOIN dbo.sysmanagement_shared_server_groups AS sg 
  ON g.parent_id = sg.server_group_id
WHERE g.parent_id = @p_RegServer_ParentGroup -- OR g.server_group_id = @p_RegServer_Group

Use a similar dataset for the parameter's query:

WITH RegServers (parent_id, server_group_id, name ) AS (
    SELECT parent_id, server_group_id, name 
     FROM dbo.sysmanagement_shared_server_groups 
    WHERE parent_id IS NULL
    SELECT e.parent_id, e.server_group_id, 
    FROM dbo.sysmanagement_shared_server_groups AS e
        INNER JOIN RegServers AS d
        ON e.parent_id = d.server_group_id 
SELECT DISTINCT  g.parent_id, as parent_group_name 
 -- OR g.server_group_id, as group_name
 FROM RegServers AS g JOIN dbo.sysmanagement_shared_registered_servers AS s
  ON s.server_group_id = g.server_group_id
 JOIN dbo.sysmanagement_shared_server_groups AS sg 
  ON g.parent_id = sg.server_group_id
Next Steps
  • Run different queries in a Subreport (or use different Subreports) to gather information across your SQL Servers and display it as a single table in one report.
  • Run multi-server queries using CMS as in this tip or using Registered Servers. This will give you similar results, but you would not be able to export the results to PDF, to schedule report, to use specific formatting or to use other SQL Server Reporting Services features.
  • Read more about creating and using subreports here.
  • Use this technique to generate the SSRS documentation for your databases. Schedule the report to run every month to have the most current configurations. Keep report snapshots to be able to review configuration changes.
  • Download the RDL files for this tip.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, June 7, 2019 - 5:39:19 PM - Svetlana Golovko Back To Top (81372)

Hi Brenda,

I would start with comparing you CMS query result with SSRS report results. Does your query returns duplicate server names?

SELECT FROM dbo.sysmanagement_shared_registered_servers s

I just tested the steps and it worked for me as expected (I have duplicates, but only for the servers that are registered multiple times in CMS). If it's still doesn't work for you - try to download the report'sdefinition here and test it again:!505&authkey=!AM_B7Dt7VttB3i0&



Monday, June 3, 2019 - 3:13:39 PM - Brenda Back To Top (81312)

 I created this exactly and it retuns the same infomation over and over instead of information for each server in my CMS group.  The info it prints is for the CMS server.


Server 1 13.0.4224.16 Enterprise Edition (64-bit)

Server 1 13.0.4224.16 Enterprise Edition (64-bit)

Server 1 13.0.4224.16 Enterprise Edition (64-bit)

Instead it should be returning this

Server 1  13.0.4224.16 Enterprise Edition (64-bit)

Server 2  13.0.5081.1        Enterprise Edition (64-bit)

Server 3  10.50.6560.0 Enterprise Edition (64-bit)

Monday, October 22, 2018 - 11:39:50 PM - Svetlana Golovko Back To Top (78029)

Hi Kate


It's hard to say what exactly is the problem without knowing the specifics of your environments. But I would start with SPNs checking. This could be a double-hop issue.



Monday, October 22, 2018 - 12:03:21 PM - Kate Back To Top (78024)



Love this report and can see I will get a lot of use out of it.

I've got two different instances or SSRS running, one local for testing and another live environment on our reporting server.

I have setup both reports to retrieve version information from all my servers, which works great on my local test bed. However, I've uploaded the two report files to my live environment and I get the "Error: Subreport could not be shown" message against one of my servers. 

Ironically, the server throwing the error is contained in the same network as my live reporting services, but when I run it on my "remote" test bed I have no problems. I can also run the sub-report query against CMS in SSMS without any errors and I've made sure my shared data sources match on both environments. 

Any ideas?


Tuesday, November 25, 2014 - 8:30:23 PM - Svetlana Golovko Back To Top (35403)

Thank you for your comment, Shafiq.


You are absolutely right.

But this tip is also about using multiple data sources in Reporting Services.

Tuesday, November 25, 2014 - 2:59:33 PM - Shafiq Back To Top (35402)

You can run a simple query by just selecting the top Node under Central Management Servers . The query will be executed for each server and combine result will be displayed in one resultset. Thanks to this tip


Friday, November 14, 2014 - 10:02:13 AM - Don Back To Top (35293)

Svetlana and group:

It's my credentials on the dynamic reads the cms fine, finds the correct number of records, but each record comes up with error running sub-report...

Thursday, November 13, 2014 - 1:05:18 PM - Don Back To Top (35279)

Privyet Svetlana,

Very appreciative of you sharing your solution...I am also getting the subreport error, but it appears that all of my registerd servers in cms are ONLINE...i downloaded the .rdl(s) from this site, everything looks okay, but I am missing something...not giving up yet... :)




Saturday, September 13, 2014 - 7:44:13 PM - Svetlana Golovko Back To Top (34521)

Hi Colin,


Yes, you can do this. It's pretty easy if you take this report as a template. All you need is to replace the query in sub-report or add another dataSet with another table below the server information table.



Thursday, September 11, 2014 - 6:29:31 AM - Colin Back To Top (34491)

Hi Svetlana,

great post thank you...

I have been asked however, to return the status of all databases on all instances in a single report (traffic light system) without using a parameter option as above. Can this be done using the registered servers?

many thanks

Wednesday, July 16, 2014 - 10:18:05 AM - Svetlana Golovko Back To Top (32733)

Hi Dilip,


Make sure that isp_SQLServer parameter is "Hidden" (not "Internal").

Try to download the reports from here:!505&authkey=!AM_B7Dt7VttB3i0&



Tuesday, July 15, 2014 - 4:14:55 PM - GarciaW Back To Top (32720)

Never mind, I got it to work.  I missed to insert the subreport in the listbox of the parent report.

Tuesday, July 15, 2014 - 12:49:54 PM - GarciaW Back To Top (32714)

It does not work for me, However I do not get an error message.  I'm using VS 2008v9 pointing to a SQL 2008R2 database, The one thing I see different is in the subreport parameter for isp_SQLServer which has an expression as =First(Fields!name.Value, "Server_List").  Any idea what may be causing it not to work?  Thank you.

Sunday, July 6, 2014 - 5:36:25 AM - Dilip Back To Top (32561)

The idea seems to be good, but this solution is not working for me. Could you please send me the RDL file, so that I can import the same and try it once.

Tuesday, June 10, 2014 - 6:04:56 AM - Samba Shiva Back To Top (32166)

I tried above steps parameter visibility" to "Internal" and it is not showing in main report.

Error: Subreport could not be shown.

Please help me to solve the issue 

Friday, February 21, 2014 - 10:59:09 AM - Lisa Back To Top (29531)

Thanks so much for sharing the information.  This is a great article!!!!  I followed the steps and created a report successfully

Tuesday, January 21, 2014 - 11:38:14 PM - Kamlesh Back To Top (28167)

Hi Svetlana,

Exactly what I was searching for but ran into a small problem.

As directed, the parameter in sub-report is set to Internal but that makes it read-only and hence doesn't work for all servers.
I tried making it Visible and Hidden but it works fine for only single server as it prompts for server name. Would be gr8 if you can suggest a workaround for this.

I am using BIDS VS 2009.


Friday, November 22, 2013 - 6:33:30 PM - Svetlana Golovko Back To Top (27585)

Hi Pete,

Sorry, I just noticed that my last reply to you is not showing up here. I will contact site support to find out why.




Friday, November 22, 2013 - 2:15:59 PM - Pete Back To Top (27578)

Hi Svetlana,

I was able to fix the problem.




Monday, November 18, 2013 - 8:50:58 AM - Pete Back To Top (27528)

Hi Svetlana,

Do you have any work around or fix of the error I am getting?   Thanks for any assistance.




Tuesday, November 12, 2013 - 12:04:36 PM - Pete Back To Top (27466)

Hi Svetlana,

Thanks for your advise to take the Tutorial.   It really helped.  I followed your instructions and when i previewed the report I got this error messaged: "An error occurred during local report processing. The defination of the report '/_Demo_Report_1' is invalid. The value expression for the text box "Server_Name' refers to the field 'Server_Name' refers to the field 'Server_Name'. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.





Friday, November 8, 2013 - 7:32:59 PM - Svetlana Golovko Back To Top (27452)

Hi Chris,

You will see the subreport error when you have offline servers registered in you Central Management Server. Try to remove the offline servers or run report for the group in CMS where all server are online.


As per the white space - make sure that subreport does not have any white space around the table and make sure that columns are exactly the same size as textboxes in parent report.


I hope this will help.




Friday, November 1, 2013 - 3:41:59 PM - Chris Back To Top (27371)

Hi. Great article.

I have this "somewhat" working but have run into a couple problems.

When executing the report (preview or right-click & run) the space between the results are off.  It's like there are 4 or 5 hard returns in between the row results.  I've googled this but not finding anything to correct the format issue.

Also, after deploying the reports and shared data source to our SQL Server Reporting environment I get the following error when attempting to run the "_Demo_Report_1" report.

Error: Subreport could not be shown. Any thoughts what I could be missing in the "deployment" steps or else where?

Thanks for any assistance,


Thursday, October 24, 2013 - 11:13:55 AM - Svetlana Golovko Back To Top (27268)

Hi Robert,

I am glad you found the tip useful. I am a big fan of SSRS - it comes with SQL Server and very flexible.

Yes, you can export SSRS report above and schedule it by setting up stored credentials. It could be SQL or Windows login that has to be setup on each SQL Server registered in Central Management Server.

What I meant by saying that "subscriptions cannot be created..." is that you can use SSMS to run similar reports/scripts again CMS, but it has it's limitations. SSRS gives you extra functionalities, such as export to PDF, subscriptions etc.

Let me know if you have other questions.


Wednesday, October 23, 2013 - 4:44:36 PM - Robert Weston Back To Top (27247)

Even without your "Customizing the Report" suggestion to parameterize CMS Groups, this technique is cutting-edge and a rare find on SQL sites.  It's a very powerful capability to query multiple servers from a single location, and it's even more powerful to schedule such a query to run regularly, without having SSMS open.  Having just followed the example to try this out in BIDS 2010 for SQL 2012, and then switching the SELECT query to get "SQL Job Failures in 24 hours" across my servers, I can now browse to my SSRS site, and run a report every morning that shows all SQL Agent Job Failures across my environment (whatever servers are registered).  Unfortunately, maybe I am mis-reading your comment that "but you would not be able to export the results to PDF, to schedule report, to use specific formatting or to use other SQL Server Reporting Services features.", when I conclude that these kinds of CMS reports cannot be "Subscribed" to for auto-emailing them out every day.   The message that I get if I click the report and pick "Subscribe" is:  "Subscriptions cannot be created because the credentials used to run the report are not stored..."   Which is understandable, but maybe there's a way to configure security even with the mandatory Windows Account that CMS requires, that you might know of?   If not, do you think that a potential work-around would be to run the report via a command line (then use Windows Scheduled Tasks to e-mail), or possibly to dump the output for each of the registered servers' "failed jobs" into a permanent table in an dministrative DB on the CMS Server, and then create a regular report to query that table, and then use a SQL Account to create another report that could be subscribed to, for automatic every day delivery.  If you are too busy and don't reply, no worries.  Thank you extremely for publishing this!    Robert



Friday, September 27, 2013 - 7:14:10 PM - Svetlana Golovko Back To Top (26979)

No, I used Business Intelligence Development Studio (that is part of the SQL Server tools). You will need to create the new Reporting Services project and add the report you created to the project.

If you are new to the Reporting Services I think this will be the good start for you:

Wednesday, September 25, 2013 - 12:40:36 PM - Peter Nerida Back To Top (26943)

I'm new in reporing services.   Did you use report builder 3.0 because I can can't create the Parent Report.  I don't know how to go to Shared Data Source Properties. I created the _Demo_SubReport_1 already.



Friday, September 20, 2013 - 2:14:29 PM - Svetlana Golovko Back To Top (26881)

I created and tested this report in SSRS 2008 R2. These instructions are for SQL Server 2008 R2.

Friday, September 20, 2013 - 12:55:00 PM - Peter Nerida Back To Top (26880)

Do you have a 2008 r2 or 2012 versions of you instructions.






Tuesday, May 21, 2013 - 6:10:49 PM - Svetlana Golovko Back To Top (24069)

Thank you for noting this, Angela.

Wednesday, May 15, 2013 - 9:59:54 AM - Angela Back To Top (23959)

Your directions say to set the parameter on the sub report to Internal.  If you do that the parameter will be read-only, you should be setting it to invisible instead so it can be updated for every server in the list.

get free sql tips
agree to terms