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:

Manually add fields under "Dataset Properties" (we have to add them
manually because we are using a dynamic database connection):

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:

Save the report.

Creating the Parent Report

Create the new blank report named "_Demo_Report_1"

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.

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

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

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:

Drag the "Subreport" item from the Toolbox to the Tablix area:

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.

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:

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

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.

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
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

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.

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

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.

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?

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.

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.

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.

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.

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?

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.

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

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.

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.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.