Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Cost Effective SQL Server Transparent Data Encryption - Free Webinar
 

Report Builder Data Sources



By:

Overview

A data source provides the details necessary to establish a connection to a data provider.  Report Builder can access data from a number of providers such as a SQL Server database, a SQL Server Analysis Services cube, and so on.  This topic will discuss the types of data sources and walk through creating one that we will use in building our sample reports.

Explanation

A report contains one or more data sources that provide the details necessary for executing queries that retrieve data for your report.  When you add a data source to a report, you can select a shared data source or you can create a new one that will be stored inside of your report (an embedded data source).  As the name implies, a shared data source can be defined once and used in multiple reports.  Using shared data sources is a best practice.  An embedded data source is stored within a single report.  If you use embedded data sources, you will have to edit the report if the data source were to change; e.g. your database is moved to a different server.

Report Builder does not have an option to create a shared data source.  Use one of the following methods to create a shared data source:

  • Create the data source using the Report Manager web application (when SQL Server Reporting Services is running in native mode)
  • Create the data source from a SharePoint document library (when SQL Server Reporting Services is running in SharePoint Integrated mode)
  • Create the data source in a Business Intelligence Developer Studio report project (the developer tool for creating reports)

To create a shared data source using the Report Manager, you will need to get the appropriate URL from your IT department; e.g. http://SERVERNAME/Reports.  Open your browser, navigate to the desired folder (e.g. Data Sources), and click New Data Source on the toolbar:

Fill in the dialog as shown below:

The main points about the above dialog are:

  • A shared data source should typically be created by your IT department as they know the server names, database names, security options, etc.
  • Change the connection string to reflect the SERVERNAME and database name in your environment
  • Set Connect using based on your environment
  • Click Test Connection to make sure that it works

When you add a data source to a report in Report Builder, you will be prompted to select a shared data source or create an embedded data source:

You can select from the list of data sources that you have previously used or click the Browse button and navigate to a folder in Report Manager or a SharePoint document library and select a shared data source.

Alternatively you can add an embedded data source:

Click the Select connection type dropdown to see the list of connection types supported by Report Builder:

Additional Information


Last Update: 6/23/2011




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



    



Learn more about SQL Server tools