Click here to Skip to main content
Click here to Skip to main content

Switching Data Sets When Working With Microsoft Reporting Services With SQL

, 14 Sep 2007
Rate this:
Please Sign up or sign in to vote.
Switching Data Sets when working with Microsoft Reporting Services
Screenshot - DataSourcePic.jpg

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:

Screenshot - DataSourcePic.jpg

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

Screenshot - DataTab.jpg

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

Screenshot - EditDataSetButton.jpg

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:

Screenshot - SelectProdDataSet.jpg

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:

Screenshot - ChangeDataSetNameToProd.jpg

Next, click on the Layout tab:

Screenshot - LayoutTab.jpg

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

Screenshot - SelectReportProperties.jpg

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

Screenshot - TablePropertiesWindow.jpg

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

harryteck
Web Developer
United States United States
Harry is a .NET Developer for a major financial institution building web and stand alone applications and class libraries. He also has experience teaching courses on ASP.NET and Microsoft databases. He holds a graduate degree in Information Science.

Comments and Discussions

 
GeneralREgenerating Field List Pinmembershumakercs8-Apr-11 12:38 
GeneralUsing report parameters to build report's connection string would be easier... [modified] PinmemberNino Mrdjen20-May-08 3:24 
GeneralRe: Using report parameters to build report's connection string would be easier... Pinmembercaradens17-Oct-08 15:22 
GeneralRe: Using report parameters to build report's connection string would be easier... PinmemberPonyRydr13-May-11 3:44 
GeneralI can't disagree more! PinmemberKABay20-Sep-07 11:57 
There is absolutely no need for this procedure because one should never using datasets and datasources that are named to 'explain' where they point. One should no more do this than change the names of the databases, tables and fields 'dependent' on what kind of data is in a database, i.e;

SELECT TestName, TestAddr1, TestAddr2
FROM MyTestServer.TestAddresses
WHERE TestState = 'CA'
ORDER BY TestZipCode
 
when pulling data from a 'testing' database containing 'test' addresses and then turn around and have to do:

SELECT ProdName, ProdAddr1, ProdAddr2
FROM MyProductionServer.ProdAddresses
WHERE ProdState = 'CA'
ORDER BY ProdZipCode
 
when you deploy to production.
 
As you are developing a report you should name Data Sources for the database/'source' it points to, not where. In the scenario presented you would create a Data Source named EduLending and set it up to point to your development database (MyDevServer.EduLending). If, as it appears to be in the scenario there is only one DataSet that uses this Data Source you should name it the same, or at least not somethingTest that you will need to change later. In reality you will probably use a Data Source to provide resources for report parameters as well as the report and, possibly, subreports. So you would named DataSets for what the data set contains, but with no reference to where it came from.
 
When you deploy your report to your staging server for user acceptance testing or directly to your production environment your will need only to reconnect your report to the Shared Data Source you should have created already (but don't have to have) which is/will be named EduLending. It will be configured to point to the appropriate databaseserver.databasename All you will need to do is open up the report's properties page, select Data Sources and reconnect the report to the Data Source(s) it uses.
 

GeneralRe: I can't disagree more! [modified] Pinmemberharryteck24-Sep-07 5:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141216.1 | Last Updated 14 Sep 2007
Article Copyright 2007 by harryteck
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid