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

Problem

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.

Solution

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 "Internal"
   • 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: 
     
     
     
     
   • 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 adynamic database connection): 
     
     
     
     
5. In the Design View of the report:
   • Add a table with three columns using the Dataset created in the step 4: 
     
     
     
     
     
     
     
   • 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:
      
     
   • Reduce the report's area to fit the table's height:
     
     
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: 
   
   
   
   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:
   
   
   
   
4. Add Dataset using the Data Source from 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 s.name 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":
     
     
     
     
   • 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: 
     
     
     
     
9. Save and preview the 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:

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
    
    UNION ALL
    
    SELECT e.parent_id, e.server_group_id, e.name 
     FROM dbo.sysmanagement_shared_server_groups AS e
         INNER JOIN RegServers AS d
         ON e.parent_id = d.server_group_id 
)
SELECT s.name, g.parent_id, sg.name as parent_group_name, g.server_group_id, g.name 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
    
    UNION ALL
     
    SELECT e.parent_id, e.server_group_id, e.name 
    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, sg.name as parent_group_name 
 -- OR g.server_group_id, g.name 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

Referred from MSSQLTIPS.COM