Udostępnij za pośrednictwem

Defining Report Datasets for Relational Data from an Oracle Database

  • Artykuł

New: 14 April 2006

Reporting Services provides a data processing extension that supports report data retrieval from an Oracle relational data source. After you have connected to an Oracle data source, you can create a report dataset that defines the data you want to use from that data source in your report. A Reporting Services dataset includes a data source, a query that retrieves data when the report is processed, and a collection of fields. The dataset definition is saved in the report definition. The Datasets window shows the current dataset definitions in a report.

To create a dataset, you can either select a stored procedure from a drop-down list or create a SQL query. You can also specify properties and define report parameters and query parameters. For instructions about defining a dataset, see Report Data How-to Topics.

There are two types of query designers: a generic query designer or a graphical query designer. The generic query designer opens by default and accepts non-standard query syntax (for example, expressions in the connection string). To use the graphical query designer, click the Generic Query Designer (Icon of the Generic Query Designer button) toggle button on the Data view toolbar. For more information about the user interface, see SQL Server Query Designer User Interface.

Using Query Parameters

If your query contains parameters, Report Designer automatically creates corresponding report parameters in the report definition when you type the query. When the report runs, values for the report parameters are passed to the query parameters.

Using Stored Procedures

You can use stored procedures to return data in a dataset. To do this, in the Command Type text box, select StoredProcedure, and then provide the name of the stored procedure. Reporting Services supports stored procedures that return only one set of data.

To Create a Dataset

Reporting Services provides two ways to connect to an Oracle relational database: the .NET Managed Provider for Oracle data processing extension or the OLE DB data processing extension using the Oracle data provider.

To choose the .NET Managed Provider for Oracle data processing extension as a data source:
  1. In Report Designer in the Data view, from the Datasets drop-down list, select <New Dataset>.
    The Dataset dialog box opens.
  2. From the Data source drop-down list, select New Datasource.
    The Data Source dialog box opens.
  3. From the Type drop-down list, choose Oracle.
  4. In UserName, enter the name of the database and a password.
    You can also set the database credentials in the Data Source dialog box on the Credentials tab. For more information, see Connecting to a Data Source. For more information, see "Using the .NET Framework Data Provider for Oracle" at msdn.microsoft.com.

The following connection string example specifies an Oracle database on server Oracle9 using Unicode. The ServerName must match what is defined in the tnsnames.ora configuration file:

Data Source="Oracle9"; Unicode="True"

To choose the OLE DB data processing extension as a data source:
  1. In Report Designer in the Data view, from the Datasets drop-down list, select <New Dataset>.
    The Dataset dialog box opens.
  2. From the Data source drop-down list, select New Datasource.
    The Data Source dialog box opens.
  3. From the Type drop-down list, choose OLE DB.
  4. Click Edit.
    The Connection Properties dialog box opens.
  5. From the OLE DB Provider drop-down list, choose Microsoft OLE DB Provider for Oracle.
  6. In UserName, enter the name of the database and a password.
    You can also set the database credentials in the Data Source dialog box on the Credentials tab. For more information, see Connecting to a Data Source. For more information, see "OLE DB Provider for Oracle" at msdn.microsoft.com.

The following connection string example specifies an Oracle database on server Oracle9. The ServerName must match what is defined in the tnsnames.ora configuration file.

Provider="MSDAORA.1";Data Source="Oracle9"

See Also

Concepts

Defining Report Datasets
Query Design Tools in Reporting Services
Connecting to a Data Source

Help and Information

Getting SQL Server 2005 Assistance