Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Reporting Services Development
 Oracle Stored Procedures in RS

Author  Topic 

LoriM
Starting Member

29 Posts

Posted - 2005-06-07 : 12:02:40
Hi,

I've been working a little in Reporting Services for a couple of months, not doing anything very complicated.

Since RS works so well, we'd like to try hooking into our Oracle server for some complicated reports. We can connect and see tables just fine, but don't want to have to convert all the Oracle SQL into Sql Server SQL.

I read the other posts regarding RS/Oracle. Can I just use the Graphical Query Editor to use the Command Type- StoredProcedure and call the stored procedure that exists on the Oracle server? That way it does the processing over there? Or do I need to actually plug through and recreate these oracle queries?

Also, do I need to install the Oracle client on the Report Server to view the report?

Thanks for any help,

jhermiz

3564 Posts

Posted - 2005-06-07 : 13:31:23
2 ways to access the data: OLE source or ODBC.

Have you looked at books online for RS ?

here you go:

quote:

Retrieving Data from Other Data Sources
You can retrieve data from any source of data that can be accessed through OLE DB or ODBC.

To view instructions about creating a dataset, click the following topic:

How to create a dataset (Report Designer)
Retrieving Data from Oracle
When you create a dataset based on an Oracle data source, you can use the graphical or generic query designer to design queries for the report. Although the queries you write are specific to Oracle, the designer is the same designer that is used for datasets based on SQL Server. For information about the graphical and generic query designers, see Retrieving Relational Data from a SQL Server Database.

You can use one of two data processing extensions to connect to an Oracle database: the Oracle data processing extension, or the OLE DB data processing extension using the Oracle data provider.

Support for parameters in Oracle depends on the data provider that is used to connect to the Oracle database. If your query contains named parameters (for example, SELECT * FROM <table> WHERE <column name> = :ParameterName) then you must use the Oracle data processing extension. If your query contains unnamed parameters (for example, SELECT * FROM <table> WHERE <column name> = ?), then you must use the OLE DB data processing extension and select Microsoft OLE DB Provider for Oracle as a data provider.


Note In some cases, the graphical query designer does not function correctly with parameters in an Oracle query. If your query contains parameters, use the generic query designer.

Retrieving Data from OLE DB and ODBC
The graphical query designer does not support all OLE DB and ODBC data sources. If the graphical query designer returns undesired results, use the generic query designer.

Populating the Fields List
When you create a query and then view the results of the query or switch to Layout view, Report Designer attempts to automatically populate the Fields list. For some data sources, Report Designer may be unable to retrieve field data and populate the Fields list. There are several steps you can take to resolve this issue.

Make sure that you have permissions to retrieve field information from the database. For some data sources, you may have permissions to access the table or object, but not have permissions to view the columns within the table or object.
Refresh the fields list. In Data view, click the Refresh Fields button. Report Designer will attempt to retrieve field information and populate the Fields list.
Run the query in the generic query designer. The graphical query designer may not be able to run queries against some data sources.
Construct the Fields list manually. In Data view, click the Edit Selected Dataset (...) button. On the Fields tab, for Field Name, type the name of the field as you want it to appear in Report Designer. For Type, select Database Field, and for Value, type the name of the column as it is returned from the table or object. Repeat these steps for each field that you want to use in your report.




Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection --
[url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page
   

- Advertisement -