Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (27)   |   Related Tips: > Reporting Services Development

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 "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:

    Dataset

    • 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

      Tablix

    • 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 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:
     
    Tablix

  6. Drag the "Subreport" item from the Toolbox to the Tablix area:
    Subreport
  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:

      Subreport

    • 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
    
    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
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.


Last Update:






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.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

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 http://www.toadworld.com/platforms/sql-server/w/wiki/10419.central-management-server.aspx

 


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

Svetlana and group:

It's my credentials on the dynamic connection...it 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

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... :)

Poka

 

Don


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

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.

 

Svetlana


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

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

Hi Dilip,

 

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

Try to download the reports from here: https://onedrive.live.com/redir?resid=CDED41917F091136!505&authkey=!AM_B7Dt7VttB3i0&ithint=file%2c.zip

 

Svetlana


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

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

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 06, 2014 - 5:36:25 AM - Dilip Back To Top

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

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

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

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.

Thanks,
Kamlesh


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

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.

 

Thanks,

Svetlana


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

Hi Svetlana,

I was able to fix the problem.

Thanks,

Pete

 


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

Hi Svetlana,

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

 

Pete

 


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

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.

Thanks,

Pete

 

 


Friday, November 08, 2013 - 7:32:59 PM - Svetlana Golovko Back To Top

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.

 

Thanks,

Svetlana


Friday, November 01, 2013 - 3:41:59 PM - Chris Back To Top

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,

Chris


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

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.

Svetlana


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

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

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: http://www.mssqltips.com/sqlservertutorial/222/sql-server-reporting-services-ssrs/.


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

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.

 

Shared


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

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

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

Thanks,

Pete

 

 

 


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

Thank you for noting this, Angela.


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

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.


Learn more about SQL Server tools