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