By: Ray Barley
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
- Take a look at Data Connections, Data Sources, and Connection Strings in Report Builder 3.0 for additional details.
- We will build some sample reports in the tutorial that use the ContosoRetailDW database which is available for download with the Microsoft Contoso BI Demo Dataset for Retail Industry. Follow the instructions to restore the ContosoRetailDW.bak file the ContosoRetailDW database.
