.png)
Connecting to a Data Source
The first step in defining a dataset in a report is to define a data source. A data source contains connection information, such as the data source type, a connection string, and credentials. Reporting Services provides the following types of data sources: SQL Server, Oracle, ODBC, and OLE DB. You can retrieve data from Analysis Services using an OLE DB data source. Developers can create data processing extensions to provide additional types of data sources. The connection information stored in a data source varies depending on the data source type. You can store a data source within a report or you can specify a shared data source, which is defined separately in Report Designer and saved on the report server when reports are published.
Data sources do not contain query information. Query information is contained within datasets, which use data sources to connect to a database. For more information, see Querying a Data Source.
Note You must create a data source in Report Designer, not Server Explorer. Report Designer does not use data sources created in Server Explorer.
Shared Data Sources
You can create a data source that multiple reports can share. A shared data source provides a single point of entry for connection information. If you have multiple reports that all use the same data source, and the connection information for those reports changes, you only have to change the connection information once for all reports. This is useful when moving reports from a test environment to a production environment.
When you create a shared data source in Report Designer, it is stored as a separate file in the report project. This file is an XML document that contains the name of the data source, a data source ID, and connection information. When you publish the reports in the project, the data source is also published. If the data source already exists on the server, the OverwriteDataSources property for the project determines whether the data source in the project overwrites the data source on the server. You can change this property through the deployment properties for the project. After the report is published, the data source exists alongside the other reports in the project and can be managed separately.
To view instructions about working with a shared data source, click a topic in the following list:
- How to create or edit a shared data source (Report Designer)
- How to set deployment properties (Report Designer)
Report Specific Data Sources
You can also create a data source within a report that is available only to that report. Multiple datasets in a report can use the data source, but datasets within other reports cannot. You use a report-specific data source when only one report requires a specific connection and you do not want to manage the data source separately after it is published. After the report is published, the data source is managed as part of the properties for the report.
To view instructions about working with a report-specific data source, click the following topic:
Common Connection Strings
To connect to SQL Server 2000, you must set the data source type to Microsoft SQL Server. The following example shows a connection string for the AdventureWorks database on a local SQL Server default instance.
data source="(local)";initial catalog=AdventureWorks
To connect to an Analysis Services 2000 server, you must set the data source type to OLE DB. The following example shows a connection string for the FoodMart 2000 database on a local Analysis Services server.
Provider=MSOLAP.2;Data Source=localhost;Initial Catalog=FoodMart 2000
To connect to an Oracle server, you must set the data source type to Oracle. The Oracle client tools must be installed on the Report Designer computer and on the report server. The following example shows a connection string for the an Oracle server with a name of myserver.
Data Source=myserver
You can also use OLE DB and ODBC to connect to other sources of data. For example, you can specify the OLE DB data source type, select the OLE DB Provider for Microsoft Directory Services, and connect to your local Active Directory.
Note It is recommended that you not add login information such as passwords to the connection string. Report Designer provides a separate tab on the Data Source dialog box that you can use to enter credentials.
