Share via

  • Article

Connecting to a Data Source (Reporting Services)

A data source definition specifies the data source type, the connection string, and credentials. Data source definitions can be shared for use by multiple reports or embedded in the report definition. For more information, see Managing Report Data Sources.

The connection information stored for a data source varies depending on the data source type. Reporting Services provides data extensions that support the following data source types:

  • Microsoft SQL Server

  • Microsoft SQL Server Analysis Services

  • OLE DB

  • Oracle

  • ODBC

  • Report Server Model

  • SAP NetWeaver BI

  • Hyperion Essbase

  • Teradata

  • XML

System administrators can install and configure additional data processing extensions and .NET Framework data providers. For more information, see Data Processing Extensions and .NET Framework Data Providers.

Developers can use the Microsoft.ReportingServices.DataProcessing API to create data processing extensions to support additional types of data sources.

Specifying a Data Source

When you first create a report, you are prompted to create a data source definition. You can create a new data source definition or use an existing shared data source. To connect to a data source, you must have the following information:

  • **Data source type   **The specific type of data source, for example, Microsoft SQL Server. Choose this value from the list of supported data source types. 

  • Connection information   Also known as the connection string, connection information includes the name and location of the data source, and sometimes a specific version of the data provider. If the data source is a database, you can specify the name of the database in the connection string. For embedded data sources, you can also write expression-based connection strings that are evaluated at run time. For more information, see Expression-based Connection Strings later in this topic.

  • Permissions   You must have been granted the appropriate permissions to access both the data source and the specific data on the data source, using the credentials you specify. For example, to connect to the AdventureWorks sample database installed on a network server, you must have permission to connect to the server and also read-only permission to access the database.

    Note

    Credentials that you use to preview your report on a local system may differ from credentials you need to view your published report. For more information, see How to: Verify a Connection to a Data Source.

After you have connected to a data source, the data source definition appears in the Report Data pane. The Report Data pane displays embedded data sources and references to shared data sources. Shared data sources appear in Solution Explorer under the Shared Data Source folder. You can specify additional data sources for a report in the Report Data pane.

For more information, see:

Note

When creating an embedded data source for a report in Business Intelligence Development Studio, you must create the data source in Report Designer, not Server Explorer. SQL Server Report Designer does not use Visual Studio data sources created in Server Explorer.

Common Connection String Examples

The following table lists examples of connections strings for various data sources. For more information about the configurations needed to connect to these data source types, see Data Sources Supported by Reporting Services.

Data source

Example

Description

SQL Server database on the local server

data source="(local)";initial catalog=AdventureWorks

Set data source type to SQL Server.

SQL Server database on the local server

data source="(local)";initial catalog=AdventureWorks

Set data source type to SQL Server.

SQL Server Instance

database

Data Source=localhost\MSSQL10.InstanceName; Initial Catalog=AdventureWorks

Set data source type to SQL Server.

SQL Server Express database

Data Source=localhost\MSSQL10.SQLEXPRESS; Initial Catalog=AdventureWorks

Set data source type to SQL Server.

Analysis Services database on the local server

data source=localhost;initial catalog=Adventure Works DW

Set data source type to SQL Server Analysis Services.

Report model data source on a report server configured in native mode

Server=http://myreportservername/reportserver; datasource=/models/Adventure Works

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.

Report model data source on a report server configured in SharePoint integrated mode

Server=https://server; datasource=https://server/site/documents/models/Adventure Works.smdl

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

provider=MSOLAP.2;data source=<remote server name>;initial catalog=FoodMart 2000

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

data source=myserver

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 Creating Report Datasets from an Oracle Database.

SAP NetWeaver BI data source

DataSource=http://mySAPNetWeaverBIServer:8000/sap/bw/xml/soap/xmla

Set the data source type to SAP NetWeaver BI. For more information, see Creating Report Datasets from an SAP NetWeaver BI Data Source.

Hyperion Essbase data source

Data Source=https://localhost:13080/aps/XMLA; Initial Catalog=Sample

Set the data source type to Hyperion Essbase. For more information, see Creating Report Datasets from a Hyperion Essbase Data Source.

Teradata data source

data source=<NNN>.<NNN>.<NNN>.<NNN>;

Set the data source type to Teradata. The connection string is an Internet Protocol (IP) address in the form of four fields, where each field can be from one to three digits.

XML data source, Web service

data source=http://adventure-works.com/results.aspx

Set the data source type to XML. The connection string is a URL for a web service that supports Web Services Definition Language (WSDL). For more information, see Creating Report Datasets from XML Data.

XML data source, XML document

https://localhost/XML/Customers.xml

Set the data source type to XML. The connection string is a URL to the XML document. For more information, see Creating Report Datasets from XML Data.

XML data source, embedded XML document

Empty

Set the data source type to XML. The XML data is embedded in the report definition. For more information, see Creating Report Datasets from 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.

Credentials for Data Sources

When you preview reports in Report Designer in BI Development Studio, the report processor retrieves the data from the data source using the credentials you specified when you created the data source. 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. 

When you publish your report, the credentials you used locally may not be sufficient for viewing the report on the report server. The data source credentials needed to access the data from your computer may differ from the credentials needed for the report server to access the data. A good practice is to verify that the data source connections continue to connect successfully after you publish a report to the report server. If you need to change the credentials, you can modify them directly on the report server. For more information, see Specifying Credential and Connection Information for Report Data Sources and How to: Store Credentials for a Data Source (Report Manager).

Expression-based Connection Strings

Expression-based connection strings are evaluated at run time. For example, you can specify the data source as a parameter, include the parameter reference in the connection string, and allow the user to choose a data source for the report. 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 an embedded 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 Report Data pane 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.