Tuesday, October 21, 2014

Consolidate your Reports with Dynamic Data Sources

I love writing SSRS reports that assist me in consuming data across my instances.  In some cases I set up subscriptions to my reports so I don't forget to review the data periodically.  But how many times have you created a really nice report and made a copy of it so you could have it query data from another instance?  I was modifying a report today and since I had three copies (one for three different instances), I set out to find a better solution.

If you write reports, you know that when you create a data source, it is tied to an instance of SQL Server.  If you want your report to be able to query more than one data source, you will want to create what is called a "dynamic data source".  Here is one way you can go about doing that.  Note that this solution is tailored around the fact that I have databases in each of my instances with the same schema to harvest performance related data, for example, but you might have a report that queries a DMV - that would be another use case.

Create Shared Dataset
The first thing I'm going to start with is a blank report.  I'm then going to create a Shared Dataset in my Visual Studio Solution called CMSInstances.  Creating a shared dataset allows you to use it in all the reports in your solution.  I prefixed it with CMS because I'm going to get a list of instances from my Central Management Server (CMS).  If you are not using a CMS, you should be!  But if that were the case you would simply specify the parameter values individually, keeping in mind that if you add/remove instances - you will have to edit every report!

Configure the shared dataset to connect to your CMS instance and paste the following query in the query window:

SELECT  g.[name] + ' - ' + s.[name] AS [SQLAlias]
       ,[server_name] AS [InstanceName]
FROM    [msdb].[dbo].[sysmanagement_shared_server_groups_internal] g
        INNER JOIN [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] s ON g.[server_group_id] = s.[server_group_id]
WHERE   g.[server_type] = 0
        AND g.[is_system_object] = 0

Create your Data Source
Now that we have our shared dataset created, we can go to our report and add it as a data source.  In the Report Data window, right-click Data Sources and add a new one.  Call it "DynamicDataSource". Next, in the embedded connections section click the expression button.

Enter the following expression:
="Data Source=" & Parameters!InstanceName.Value

Create your Report's Dataset
Because we created the shared dataset prior, we can use that dataset over and over in any report we author.  To add that shared dataset to this report, simply right-click Datasets, give it a Name and select the shared dataset.

Create your Parameter
Now that you've created the reports dataset to populate your instance pull-down, we can create the Parameter.  Right-click on Parameters and Add Parameter.  For Name type in InstanceName and Prompt just type in Instance.  Next, click on Available Values and configure it as follows:

You're all done!
You should now be able to Preview the report and your Instance pull-down will be populated with all the instances configured in your CMS.  If you group your servers by environment, you can further refine the query as needed.  Any queries using this data source would obviously have to be drawing their data from similar databases and table names.