Connecting to a Data Source
In Reporting Services, report definitions include one or more queries, layout information, and data source connections to the external data sources used in the report. A data source connection specifies the data source type, the connection string, and credentials. In Reporting Services, data source connection information is specified in the dataset. The first step in defining a dataset for a report is to define a data source you want to use.
Reporting Services provides data extensions to support the following data source types: SQL Server, Analysis Services, Oracle, ODBC, OLE DB, SAP NetWeaver BI, and XML. Developers can use the Microsoft.ReportingServices.DataProcessing API to create data processing extensions to support additional types of data sources. The connection information stored in a data source varies depending on the data source type. Examples of different kinds of connection strings are provided further on in this topic.
You can specify a data source in the following ways:
- As embedded information stored within a report (this is referred to as a report-specific data source).
- As a shared data source, which is defined separately in Report Designer and saved on the report server as a separate item when reports are published.
- As an expression that is used to select the data source at run time.
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 Defining Report Datasets.
When specifying a data source for use in a report definition, you must create a data source in Report Designer, not Server Explorer. The version of Report Designer included in SQL Server 2005 does not use Visual Studio data sources created in Server Explorer.
After a report is published to a report server, you can configure the report to use a different data source. For example, you can switch from a test data source to a production data source with an identical underlying data structure.
For more information, see Setting Data Source Properties in Reporting Services.
Credentials Used in Preview
When you preview reports in Report Designer, the data is retrieved from the data source. The credentials that are used to connect to the data source are specified on the Credentials tab in the Data Source or Shared Data Source dialog box. These credentials are stored in the local project configuration file and are specific to the computer on which the credentials were stored. If you copy the project files to another computer, you must redefine the credentials for the data source.
Report Specific Data Sources
You can create a data source within a report that is available only to that report. The data source is available to any dataset that is defined in the report, but it is not available to other reports in the project. 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. For more information about specifying a report-specific data source, see How to: Create or Edit a Report-Specific Data Source (Report Designer).
Shared Data Sources
You can create a data source that multiple reports can share to simplify connection maintenance. 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 or when you want to manage a data source as a separate item.
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, see the following:
- Creating, Modifying, and Deleting Shared Data Sources
- How to: Create or Edit a Shared Data Source (Report Designer)
- How to: Set Deployment Properties (Report Designer)
Data Source Expressions
You can put an expression into a connection string to allow users to select the data source at run time. For example, suppose a multinational firm has data servers in several countries. With an expression-based connection string, a user who is running a sales report can select a data source for a particular country before running the report.
The following example illustrates the use of a data source expression in a SQL Server connection string. The example assumes you have created a report parameter named ServerName:
="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks
Data source expressions are processed at run time or when a report is previewed. The expression must be written in Visual Basic. Use the following guidelines when defining a data source expression:
- Design the report using a static connection string. A static connection string refers to a connection string that is not set through an expression (for example, when you follow the steps for creating a report-specific or shared data source, you are defining a static connection string). Using a static connection string allows you to connect to the data source in Report Designer so that you can get the query results you need to create the report.
- When defining the data source connection, do not use a shared data source. You cannot use a data source expression in a shared data source. You must define a report-specific data source for the report.
- Specify credentials separately from the connection string. You can use stored credentials, prompted credentials, or integrated security.
- Add a report parameter to specify a data source. For parameter values, you can either provide a static list of available values (in this case, the available values should be data sources you can use with the report) or define a query that retrieves a list of data sources at run time.
- Be sure that the list of data sources shares the same database schema. All report design begins with schema information. If there is a mismatch between the schema used to define the report and the actual schema used by the report at run time, the report might not run.
- Before publishing the report, replace the static connection string with an expression. Wait until you are finished designing the report before you replace the static connection string with an expression. Once you use an expression, you cannot execute the query in Report Designer. Furthermore, the field list in the Datasets window and the Parameters list will not update automatically.
Special Characters in a Password
If you configure your ODBC or SQL data source to prompt for a password or to include the password in the connection string, and a user enters the password with special characters like punctuation marks, some underlying data source drivers cannot validate the special characters. When you process the report, the message "Not a valid password" may indicate this problem. If changing the password is impractical, you can work with your database administrator to store the appropriate credentials on the server as part of a system ODBC data source name (DSN). For more information, see "OdbcConnection.ConnectionString" in the .NET Framework SDK documentation.
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. These credentials are stored securely on the Report Designer computer.
Common Connection Strings
The following table lists examples of connections string for various data sources.
| Data Source | Example | Description |
|---|---|---|
SQL Server OLTP database |
|
Set data source type to SQL Server. |
Analysis Services database |
|
Set data source type to SQL Server Analysis Services. |
Report model data source |
|
Specify the report server or document library URL and the path to the published model in the report server folder or document library folder namespace. |
SQL Server 2000 Analysis Services server |
|
Set the data source type to OLE DB Provider for OLAP Services 8.0. You can achieve a faster connection to SQL Server 2000 Analysis Services data sources if you set the ConnectTo property to 8.0. To set this property, use the Connection Properties dialog box, Advanced Properties tab. |
Oracle server |
|
Set the data source type to Oracle. The Oracle client tools must be installed on the Report Designer computer and on the report server. For more information, see Defining Report Datasets for Relational Data from an Oracle Database. |
SAP NetWeaver BI data source |
|
Set the data source type to SAP NetWeaver BI. For more information, see Defining Report Datasets for Multidimensional Data from an SAP NetWeaver BI System. |
Hyperion Essbase data source |
|
Set the data source type to Hyperion Essbase. For more information, see Defining Report Datasets for Multidimensional Data from a Hyperion Essbase Database. |
XML data source |
|
Set the data source type to XML. The connection string is a URL to the XML document. For more information, see Defining Report Datasets for XML Data. |
If you fail to connect to a report server using localhost, check that the network protocol for TCP/IP protocol is enabled. For more information, see Connecting to the SQL Server Database Engine.
See Also
Tasks
How to: Create or Edit a Report-Specific Data Source (Report Designer)
How to: Create or Edit a Shared Data Source (Report Designer)
Concepts
Shared Data Sources and Report-Specific Data Sources
Working with Data in a Report Layout
Setting Data Source Properties in Reporting Services
Report Server Folder Hierarchy
Other Resources
Data Source (Credentials Tab, Report Designer)
Shared Data Source (Credentials Tab, Report Designer)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|
17 July 2006 |
|
14 April 2006 |
|
5 December 2005 |
|