Data Sources Supported by Reporting Services
Reporting Services retrieves report data from data sources through a modular and extensible data layer that uses data processing extensions. To retrieve report data from a data source, you must select a data processing extension that supports both the type of data source, the version of software running on the data source, and the data source platform (32-bit, 64-bit x64, or 64-bit Itanium).
When you deploy Reporting Services, a set of Reporting Services data processing extensions that provide access to a variety of data source types are automatically installed and registered on both the report authoring client and on the report server. In addition, custom data processing extensions and standard Microsoft .NET Framework data providers can be installed and registered by anyone with system administrator credentials. To process and view a report, the data processing extensions and data providers must be installed and registered on the report server; to preview a report, the data processing extensions and data providers must be installed and registered on the report authoring client. data processing extensions and data providers must be natively compiled for the platform where they are installed.
Many third-party standard .NET Framework data providers are available as downloads from the Microsoft Download Center and from third-party sites. You can also search the SQL Server Reporting Services public forum for information about third-party data providers.
Note
Standard .NET Framework data providers do not necessarily support all the functionality supplied by Reporting Services data processing extensions. In addition, some OLE DB data providers can be used to author and preview reports, but are not designed to support reports published on a report server. For example, the Microsoft OLE DB Provider for Jet is not supported on the report server.
For more information about custom data processing extensions, see Implementing a Data Processing Extension. For more information about standard .NET Framework data providers, see the System.Data namespace. For information about the differences between the data processing extensions and data providers, see Differences Between Data Processing Extensions and Standard .NET Data Providers.
When a report is processed, each report dataset retrieves data from the data source to which it is bound. There can be only one query against one data source per dataset. Report parameters can also be bound to a column within a report dataset. Data retrieved for a report dataset is expected to conform to certain characteristics. For more information, see General Characteristics of Data Retrieved From a Report Data Source.
When you work with a report dataset in Report Designer, you select a data source type. Each data source type has an associated query designer that automatically opens in the Data view of Report Designer so you can build or edit the query for that report dataset. The query designer associated with a data processing extension can also affect the query you can design and send to the data source. For more information, see Data Sources Supported by Reporting Services.
Platform Support for Report Data Sources
The data sources you can use in a Reporting Services deployment vary by SQL Server edition, Reporting Services version, and by platform. The table later in this topic provides information about supported data sources by version and by platform. For more information about features by edition, see Features Supported by the Editions of SQL Server 2005.
Platform considerations for Reporting Services data sources are separate for the report authoring client and the report server.
On the report authoring client
SQL Server Business Intelligence Development Studio is a 32-bit application. BIDS is not supported on an Itanium-based platform. On an x64 platform, to preview reports in Report Designer, you must have 32-bit data providers installed in the (x86) platform directory.
On the report server
When you deploy a report to a 64-bit report server (either x86 or Itanium), the report server must have natively compiled 64-bit data providers installed. Wrapping a 32-bit data provider in 64-bit interfaces is not supported. For more information, check the documentation for the data provider.
Supported Data Sources
The following table lists Microsoft data processing extensions and data providers that you can use to retrieve data for report datasets and report models. For more information about an extension or data provider, click the link in the second column. The table columns are described as follows:
- Source of report data: The type of data being accessed; for example, relational database, multidimensional database, flat file, or XML. This column answers the question: "What types of data can Reporting Services use for a report?"
- Reporting Services Data Source Type: One of the data source types you see in the drop-down list when you define a data source in Reporting Services. This list is populated from installed and registered data processing extensions and data providers. This column answers the question: "What data source type do I select from the drop-down list when I create a report data source?"
- Name of Data Processing Extension/Data Provider: The Reporting Services data processing extension or other data provider that corresponds to the Reporting Services data source type selected. This column answers the question: "When I select a data source type, which corresponding data processing extension or data provider is used?"
- Underlying Data Provider version (Optional): Some data source types support more than one data provider. These could be different versions of the same provider or different implementations by third-parties for a type of data provider. The provider name frequently appears in the connection string after you have configured a data source. This column answers the question: "After selecting the data source type, what data provider do I select in the Connection Properties dialog box?"
- Data Source <platform>: The data source platform supported by the data processing extension or data provider for the target data source. This column answers the question: "Can this data processing extension or data provider retrieve data from a data source on this type of platform?"
- Version of data source: The version of the target data source supported by the DPE or DP. This column answers the question: "Can this data processing extension or data provider retrieve data from this version of the data source?"
- RS <platform>: The platforms for the report server and report authoring client where you can install a custom DPE or DP. The Built-in Reporting Services data processing extensions are included with any installation of Reporting Services. A custom data processing extension or .NET Framework data provider must be compiled natively for a specific platform. This column answers the question: "Can this data processing extension or data provider be installed on this type of platform?"
Types of data sources
| Source of Report data | Reporting Services Data Source Type | Name of Data Processing Extension/Data Provider | Underlying Data Provider version (Optional) | Data Source Platform x86 | Data Source Platform x64 | Data Source Platform ia64 | Version of data source | RS Platform x86 | RS Platform x64 | RS Platform ia64 |
|---|---|---|---|---|---|---|---|---|---|---|
SQL Server relational database |
Microsoft SQL Server |
Built-in Reporting Services data processing extension |
Extends System.Data.SqlClient |
Y |
Y |
Y |
SQL Server 2005 SQL Server 2000 SQL Server 7.0 |
Y |
Y |
Y |
SQL Server relational database |
OLEDB |
Built-in Reporting Services data processing extension |
Extends System.Data.OledbClient |
Y |
Y |
Y |
SQL Server 2005 SQL Server 2000 SQL Server 7.0 |
Y |
Y |
Y |
SQL Server relational database |
ODBC |
Built-in Reporting Services data processing extension |
Extends System.Data.OdbcClient |
Y |
Y |
Y |
SQL Server 2005 SQL Server 2000 SQL Server 7.0 |
Y |
Y |
Y |
Analysis Services multidimensional database |
Microsoft SQL Server Analysis Services |
Built-in Reporting Services data processing extension |
Uses ADOMD.NET |
Y |
Y |
Y |
SQL Server 2005 Analysis Services |
Y |
Y |
Y |
Analysis Services multidimensional database |
Microsoft SQL Server Analysis Services |
Built-in Reporting Services data processing extension |
Uses ADOMD.NET |
Y |
N |
N |
SQL Server 2000 Analysis Services |
Y |
N |
Y |
Analysis Services multidimensional database |
OLEDB |
Built-in Reporting Services data processing extension |
Extends System.Data.OledbClient Version 9.0 |
Y |
Y |
Y |
SQL Server 2005 Analysis Services |
Y |
Y |
Y |
Analysis Services multidimensional database |
OLEDB |
Built-in Reporting Services data processing extension |
Extends System.Data.OledbClient Version 8.0 |
Y |
N |
Y |
SQL Server 2000 Analysis Services |
Y |
N |
Y |
XML |
XML |
Built-in Reporting Services data processing extension |
XML data sources do not have platform dependencies. |
|
|
|
XML Web Services or documents |
Y |
Y |
Y |
Report Server Model |
Report Model |
Built-in Reporting Services data processing extension for a published SMDL file |
Data sources for a model use Built-in data processing extensions. Oracle requires Oracle client components. Teradata requires the .NET Framework Data Provider for Teradata 12.00.00.01. See Teradata documentation for platform support. |
Models can be created from: SQL Server 2005 SQL Server 2000 SQL Server 2005 Analysis Services Oracle 9.2.0.3 or later Teradata databases Version 12.0 or 6.20. |
Y |
Y |
Y |
|||
SAP relational database |
SAP |
Not built-in. Reporting Services data processing extension |
Install from SQL Server 2005 Feature Pack download site. See SAP documentation for platform support. |
Y |
mySAP Business Suite |
Y |
||||
SAP multidimensional database |
Sap BI NetWeaver |
Built-in Reporting Services data processing extension (SP1) |
See SAP documentation for platform support. |
SAP BI NetWeaver |
Y |
|||||
Hyperion Essbase |
Hyperion Essbase |
Built-in Reporting Services data processing extension (SP2) |
See Hyperion documentation for platform support. |
Y |
Hyperion Essbase |
Y |
||||
Oracle relational database |
Oracle |
Built-in Reporting Services data processing extension |
Extends System.Data.OracleClient Requires Oracle client components. |
Y |
Oracle 10g 9 8.1.7 |
Y |
Y |
Y |
||
Oracle relational database |
Part of OLEDB |
Built-in OLE DB for Oracle |
MSDAORA Part of MDAC from downloads.microsoft.com Requires Oracle client components. See Oracle documentation for platform support. |
Y |
Depends on version of MDAC. For more information, search https://support.microsoft.com for "Oracle". |
Y |
||||
DB2 relational database |
Customized registered data extension name |
Microsoft OLE DB Provider for DB2 |
Install from SQL Server 2005 Feature Pack download site. See HI Server documentation for platform support. |
Y |
Y |
N |
Y |
|||
DB2 relational database |
Customized registered data extension name |
2004 Host Integration Server See HI Server documentation. |
Y |
Y |
N |
Y |
||||
Generic OLE DB data source |
OLEDB |
Built-in Reporting Services data processing extension |
Any data source that supports OLE DB. See the data source documentation for platform support. |
Y |
Any data source that supports OLE DB. See Note. |
Y |
||||
Generic ODBC data source |
ODBC |
Built-in Reporting Services data processing extension |
Any data source that supports OLE DB. See the data source documentation for platform support. |
Y |
Any data source that supports ODBC. See Note. |
Y |
Y |
Y |
Many standard .NET Framework data providers are available from third parties. For more information, search the third-party Web sites or forums.
To install and register a custom data processing extension or standard .NET Framework data provider, you will need to refer to the data provider reference documentation. For more information, see How to: Register a Standard .NET Framework Data Provider.
Return to Data sources table
Reporting Services Data Processing Extensions
The following data processing extensions are automatically installed with Reporting Services. For more information and to verify the installation, see RSReportDesigner Configuration File and RSReportServer Configuration File.
Microsoft SQL Server Data Processing Extension
The data source type Microsoft SQL Server wraps and extends the .NET Framework Data Provider for SQL Server. This data processing extension is natively compiled for and runs on x86, x64, and Itanium-based platforms. Use this extension to connect to and retrieve data from a database on the following versions of a SQL Server instance:
- SQL Server 2005 running on x86, x64, and Itanium-based platforms
- SQL Server 2000 running on x86, x64, and Itanium-based platforms
- SQL Server 7 running on x86
When you select this extension when authoring a report dataset, the Visual Database Tool Designer opens to help you create a Transact-SQL query for this data source. If you use the query designer in graphical mode, the query is analyzed and possibly rewritten. Use the generic query designer when you want to control the exact Transact-SQL used for a query. For more information about authoring report datasets for this extension, see Defining Report Datasets for a SQL Server Relational Database. For more information about the associated query designer, see Query and View Designer Tools (Visual Database Tools) and SQL Server Query Designer User Interface.
The Microsoft SQL Server data processing extension supports multivalue parameters.
Return to Data sources table
Microsoft SQL Server Analysis Services Data Processing Extension
When you select data source type Microsoft SQL Server Analysis Services, you are selecting a Reporting Services data processing extension that extends the .NET Framework Data Provider for Microsoft Analysis Services. This data processing extension is natively compiled for and runs on x86, x64, and Itanium-based platforms.
This data provider uses the ADOMD.NET object model to create queries using XML for Analysis (XMLA) version 1.1. Results are returned as a flattened rowset. For more information about authoring report datasets for this extension, see Defining Report Datasets for Analysis Services Multidimensional and Data Mining Prediction Data. For more information about XMLA, see XML for Analysis Overview (XMLA). For more information about the associated query designer for MDX, see Analysis Services MDX Query Designer User Interface. For more information about the associated query designer for DMX, see Analysis Services DMX Query Designer User Interface.
When connecting to an Analysis Services data source, the Microsoft SQL Server Analysis Services data processing extension supports multivalue parameters and maps cell and member properties to extended properties supported by Reporting Services. For more information about extended properties, see Using Extended Field Properties for an Analysis Services Database.
You can use this extension to connect to and retrieve multidimensional data from versions of a SQL Server Analysis Services database:
- SQL Server 2005 Analysis Services running on x86, x64, and Itanium-based platforms.
SQL Server 2000 Analysis Services
When connecting to a SQL Server 2000 Analysis Services data source, the Microsoft SQL Server Analysis Services data processing extension does not support query parameters.
You can use this extension to connect to and retrieve multidimensional data from the following version of a SQL Server Analysis Services database:
- SQL Server 2000 Analysis Services running on x86 and Itanium-based platforms.
Note
SQL Server 2000 Analysis Services does not run on an x64 platform.
To connect to SQL Server 2000 Analysis Services, the XMLA SDK must be installed on the Analysis Services server and use HTTP protocol with the URL pointing to the XMLA SDK location. TCP/IP protocol is not supported. For more information, see the XML for Analysis 1.1 SDK.
Return to Data sources table
OLE DB Data Processing Extension
The OLE DB data processing extension requires the choice of an additional data provider layer based on the version of the data source you want to use in your report. If you do not select a specific data provider, a default is provided. Choose a specific data provider through the Connection Properties dialog box, accessed through the Edit button on the Data Source or Shared Data Source dialog boxes.
For more information about the OLE DB associated query designer, see Query and View Designer Tools (Visual Database Tools) and SQL Server Query Designer User Interface. For more information about specific support for OLE DB providers, see Visual Studio .NET Designer Tool Supports Specific OLE DB Providers in the Microsoft Knowledge Base.
Return to Data sources table
OLE DB for SQL Server
When you select data source type OLE DB, you are selecting a Reporting Services data processing extension that extends the .NET Framework Data Provider for OLE DB. This data processing extension is natively compiled for and runs on x86, x64, and Itanium platforms. Use this extension to connect to and retrieve data from the following data sources:
- SQL Server 2005 running on x86, x64, and Itanium
- SQL Server 2000 running on x86, x64, and Itanium
- SQL Server 7 running on x86
- SQL Server 6.5 and earlier running on x86
For more information about authoring report datasets for this extension, see Defining Report Datasets for Other Data Sources.
Return to Data sources table
OLE DB for Analysis Services 9.0
To connect to Microsoft SQL Server 2005 Analysis Services, select Microsoft OLE DB Provider for Analysis Services 9.0, select data source type OLE DB, and then select the underlying data provider by name.
This combination of data processing extension and data provider is natively compiled for and run on x86, x64, and Itanium platforms. Use this extension to connect to and retrieve data from the following data sources:
- Microsoft SQL Server 2005 Analysis Services running on x86, x64, and Itanium-based platforms.
Note
This data processing extension has no support for server aggregates, no automatic mapping of extended field properties, and no support for query parameters. The recommended data provider for an Analysis Services data source is Microsoft SQL Server Analysis Services.
For more information about authoring report datasets for this extension, see Defining Report Datasets for Other Data Sources.
Return to Data sources table
OLE DB for Analysis Services 8.0
To retrieve SQL Server 2000 Analysis Services data, use the OLE DB Provider for OLAP Services 8.0. Queries against SQL Server 2000 Analysis Services are written using Multidimensional Expressions (MDX).
Note
This data processing extension has no support for server aggregates, no automatic mapping of extended field properties, and no support for query parameters. The recommended data provider for an Analysis Services data source is Microsoft SQL Server Analysis Services.
Return to Data sources table
OLE DB for OLAP 7.0
OLE DB Provider for OLAP Services 7.0 is not supported.
Return to Data sources table
OLE DB for Oracle
The data processing extension OLE DB for Oracle does not support the following Oracle data types: BLOB, CLOB, NCLOB, BFILE, UROWID.
Unnamed parameters that are position-dependent are supported. Named parameters are not supported by this extension. To use named parameter, use the Oracle data processing extension.
For more information about configuring Oracle as a data source, see How to use Reporting Services to configure and to access an Oracle data source. For more information about additional permissions configuration, see How to add permissions for the NETWORK SERVICE security principal in the Microsoft Knowledge Base.
Return to Data sources table
OLE DB Standard .NET Framework data provider
To retrieve data from a data source that supports OLE DB .NET Framework data providers, use the OLE DB data source type and select the default data provider, or select from the installed data providers in the Connection String dialog box.
Note
Although a data provider may support previewing a report on your report authoring client, not all OLE DB data providers are designed to support reports published on a report server.
Return to Data sources table
ODBC Data Processing Extension
When you select data source type ODBC, you are selecting a Reporting Services data processing extension that extends the .NET Framework Data Provider for ODBC. This data processing extension is natively compiled for and runs on x86, x64, and Itanium platforms. Use this extension to connect to and retrieve data from any data source that has an ODBC provider.
Note
Although a data provider may support previewing a report on your report authoring client, not all ODBC data providers are designed to support reports published on a report server.
Return to Data sources table
ODBC Standard .NET Framework data provider
To retrieve data from a data source that supports a standard ODBC .NET Framework data provider, use the ODBC data source type and select the default data provider, or select from the installed data providers in the Connection String dialog box.
Note
Although a data provider may support previewing a report on your report authoring client, not all ODBC data providers are designed to support reports published on a report server.
Return to Data sources table
Oracle Data Processing Extension
When you select data source type Oracle, you are selecting a Reporting Services data processing extension that extends the .NET Framework Data Provider for Oracle. The Oracle data source wraps and extends the System.Data.OracleClient classes needed by Reporting Services. To retrieve report data from an Oracle database, your administrator must install Oracle client tools. This data provider uses the Oracle Call Interface (OCI) from Oracle 8i Release 3 as provided by Oracle Client software. The client application version must be 8.1.7 or later. These tools must be installed on the report authoring client to preview reports and on the report server to view published reports.
Named parameters are supported by this extension. For Oracle version 9 or later, multivalue parameters are supported. For unnamed parameters that are position-dependent, use the OLE DB data processing extension with the data provider Microsoft OLE DB Provider for Oracle. For more information about configuring Oracle as a data source, see How to use Reporting Services to configure and to access an Oracle data source. For more information about additional permissions configuration, see How to add permissions for the NETWORK SERVICE security principal in the Microsoft Knowledge Base.
You can retrieve data from stored procedures with multiple input parameters, but the stored procedure must return only one output cursor. For more information, see the Oracle section in Retrieving Data Using the DataReader.
For more information, see Defining Report Datasets for Relational Data from an Oracle Database. For more information about the associated query designer, see Query and View Designer Tools (Visual Database Tools) and SQL Server Query Designer User Interface.
Return to Data sources table
XML Data Processing Extension
Reporting Services includes an XML data processing extension so that you can use XML data in a report. The data can be retrieved from an XML document, a Web service, or a Web-based application that can be accessed by way of a URL. For more information about specifying the connection string, see Connecting to a Data Source. For more information about specifying the query, see Defining Report Datasets for XML Data. For more information about the associated query designer, see the Generic Query Designer section in SQL Server Query Designer User Interface. For examples, see Tutorial: Using XML Data in a Report and Reporting Services: Using XML and Web Service Data Sources.
Return to Data sources table
Data Provider for mySAP Business Suite
Reporting Services can retrieve report data from a mySAP Business Suite data source. For more information about how to download this data provider, see Microsoft .NET Data Provider for mySAP Business Suite and Configuring Reporting Services to Use the Microsoft .NET Framework Data Provider for mySAP Business Suite. For more information about the associated query designer, see the Generic Query Designer section in SQL Server Query Designer User Interface.
Return to Data sources table
SAP NetWeaver Business Intelligence Data Processing Extension
Reporting Services 2005 Service Pack 1 and later includes a data processing extension that allows you to use data from an SAP NetWeaver Business Intelligence data source in a report. For more information about specifying the connection string, see Connecting to a Data Source. For more information about specifying the query, see Defining Report Datasets for Multidimensional Data from an SAP NetWeaver BI System. For more information about the associated query designer, see SAP NetWeaver BI Query Designer User Interface. For more information, see Using SQL Server 2005 Reporting Services with SAP NetWeaver Business Intelligence.
Return to Data sources table
Hyperion Essbase Business Intelligence Data Processing Extension
Reporting Services 2005 Service Pack 2 and later includes a data processing extension that allows you to use data from a Hyperion Essbase data source in a report. For more information about specifying the connection string, see Connecting to a Data Source. For more information about specifying the query, see Defining Report Datasets for Multidimensional Data from a Hyperion Essbase Database. For more information about the associated query designer, see Hyperion Essbase Query Designer User Interface. For more information, see the SQL Server 2005 - Reporting Services Technet site.
Return to Data sources table
Report Model Data Sources
You can use report models generated from SQL Server relational databases, Oracle databases running version 9.2.0.3 or later, Teradata databases running version 12.0 or 6.20, or SQL Server 2005 Analysis Services or later multidimensional cubes as data sources for reports that you create in Report Designer and Report Builder. When you select a report model data source, you are selecting a model of the underlying data source. To use a report model as a data source, it must be published to a report server or SharePoint library. You can create and publish models using Model Designer, Report Manager, or SQL Server Management Studio.
To generate a model based on a Teradata database, you must have the .NET Framework Data Provider for Teradata version 12.00.00.01 installed on the report server and on the report authoring client. For more information, see Configuring Reporting Services for Teradata-based Report Models.
For more information about using Report Model data sources from Report Designer, see Working with Report Model Query Designer. For more information about using report model data sources, see Working with Model Designer, Generating Models Using Report Management Tools and How to: Create a Report Model Data Source (Report Designer).
Return to Data sources table
See Also
Tasks
How to: Create a Model Using Report Manager
Concepts
Reporting Services Component Overview
Report Server
Working with Model Designer
Defining Report Datasets for Other Data Sources
Other Resources
File Share Data Processing Extension Sample
Hardware and Software Requirements for Installing SQL Server 2005
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 November 2008 |
|
12 December 2006 |
|
14 April 2006 |
|