Share via

  • Article

How to: Retrieve Data from an Oracle Data Source (Report Builder 2.0)

Reporting Services provides two ways to connect to an Oracle relational database: the .NET Framework Managed Provider for Oracle data processing extension or the OLE DB data processing extension that uses the Oracle data provider. For more information, see Data Sources Supported by Reporting Services (Report Builder 2.0).

To use data from an Oracle database in your report, you must define an Oracle data source and one or more report datasets. When you define the data source, you must specify a connection string and credentials so that you can access the data source from your client computer.

You can create an embedded data source definition for use by a single report or a shared data source definition that can be used by multiple reports. The procedures in this topic describe how to create an embedded data source. For more information about shared data sources, see Embedded and Shared Data Sources (Report Builder 2.0) and Creating, Modifying, and Deleting Embedded Data Sources (Report Builder 2.0).

After you create an Oracle data source definition, you can create one or more datasets. After you create a dataset, the name of the dataset appears as a node in the Report Data pane under its data source.

After you publish your report, you may need to change the credentials for the data source so that when the report runs on the report server, the permissions to retrieve the data are valid.

To create an embedded data source that uses the .NET Managed Provider for Oracle data processing extension

  1. On the toolbar in the Report Data pane, click New, and then click Data Source.

  2. In the Data Source Properties dialog box, type a name in the Name text box or accept the default name.

  3. Verify that Embedded connection is selected.

  4. From the Type drop-down list, select Oracle.

  5. Specify a connection string that works with your Oracle data source.

    Contact your database administrator for connection information and for the credentials to use to connect to the data source. The following connection string example specifies an Oracle database on the server named "Oracle9" using Unicode. The server name must match what is defined in the tnsnames.ora configuration file as the Oracle server instance name:

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

  6. Click Credentials.

  7. Set the credentials to use to connect to the data source.

  8. Click OK.

    The data source appears in the Report Data pane. For more information, see Using the .NET Framework Data Provider for Oracle on MSDN.

To create an embedded data source that uses the OLE DB data processing extension

  1. On the toolbar in the Report Data pane, click New, and then Data Source. The Data Source Properties dialog box opens.

  2. In Name, type a name or accept the default name.

  3. Verify that Embedded connection is selected.

  4. From the Type drop-down list, select OLE DB.

  5. Provide a connection string that specifies the Microsoft OLE DB Provider for Oracle and works with your Oracle data source.

    Contact your database administrator for connection information. The following connection string example specifies using the Microsoft OLE DB Provider for Oracle for an Oracle database on a server named "Oracle9". The server name must match what is defined in the tnsnames.ora configuration file as the Oracle server instance name:

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

  6. Click Credentials.

    Set the credentials to use to connect to the data source. For more information, see Specifying Credentials for a Report Data Source (Report Builder 2.0).

    Note

    To test the data source connection, click Edit. In the Connection Properties dialog box, click Test Connection. If the test is successful, you will see the informational message "Test connection succeeded". If the test fails, you will see a warning message with more information about why the test was not successful.

  7. Click OK.

    The data source appears in the Report Data pane. For more information, see Using the .NET Framework Data Provider for Oracle on MSDN.

To create a dataset for an Oracle data source

  1. In the Report Data pane, right-click the name of the data source that connects to an Oracle data source, and then click Add Dataset.

  2. In the Query page of the Dataset Properties dialog box, type a name in the Name text box or accept the default name.

  3. In the Data source text box, verify that the name of the data source you right-clicked appears.

  4. Verify that Text is selected in the Query type box. Click Query Designer. The text-based query designer opens.

  5. Type or paste a query from the clipboard directly into the Query pane, or import an existing SQL query from a file or from another report. For more information, see Text-based Query Designer User Interface (Report Builder 2.0).

  6. Click OK.

    The dataset and its field collection appear in the Report Data pane under the data source node.