Switching Data Sets When Working With Microsoft Reporting Services With SQL

Introduction

This article describes how to switch data sources (datasets) when working with a report using Microsoft Reporting Services with SQL Server Business Intelligence Development Studio. This is important when your report may utilize different data sources, for example, one for test and one for production. This solution is based on using the SQL Server Business Intelligence Development Studio to easily make the changes to the data source as opposed to manually editing the report definition language XML source code, which opens up the chance for errors if you don't know what to edit.

Background

This article assumes a working knowledge of using Microsoft Reporting Services with SQL Server Business Intelligence Development Studio.

Using the GUI

You'll first need two shared data sources already set up under "Shared Data Sources" in Solution Explorer. Here I have two listed:

Next, go into an existing report and click on the data tab as shown below. You'll notice I am using a Test data set named "EduLendingTest":

Next, click on the ellipsis button next to the DataSet dropdown menu. The tooltip "Edit Selected DataSet" will appear as shown:

The DataSet window pop-up will appear. In this window, select the "Data Source" dropdown menu, and select the data source you wish to switch to. In this instance, I am switching to "EduLendingProd (shared)" as shown:

Next, change the name of the data set. In this example, I used the same name as the data source I created highlighted here, then clicked OK:

Next, click on the Layout tab:

Click on the report table, then right-click the report table and select "Properties":

In the Table Properties window, select the new data set name from the "Dataset name:" dropdown list as shown here and then click OK:

Save your report and you should be able to use your new data set with the underlying data source.

Points of Interest

This approach was much easier than trying to edit the underlying XML rdl code, which got me in trouble. Hopefully this important, but not well known function will be made easier to use in future releases and will be better documented.

History

Submitted 09/11/2007