We have a data warehouse on Oracle from which we need to produce a reporting package using SQL Server Reporting Services. We have already produced one package where a Data Transform from Oracle to SQL Server was used and stored procedures accessed data for the SQL Server Reporting Services reports- no problem. I'm looking to see if there are any alternatives to this method such as the Reporting Services report dataset calling an Oracle stored procedure directly. I have managed to configure a dataset to Oracle and to extract data into a report using the DataSet editor Query String however the report users need the ability to select run criteria and so pass to parameters to the Oracle query. Any advice much appreciated?

Went through this very same problem! Found nothing on the Net to help either! so after much trial and error...

1. Set up an OLE_DB connection in RS pointing to your Oracle database.
2. I use the Report Wizard, but this will work either way! When the box comes up for you to input a SQL Statement, type Select * From Dual. You will be getting rid of this soon, but it's the only way to get past that screen. Once you've clicked the finish button your report should open in RS. Click on Data tab, then on Generic Query Designer (looks like 2 tables with a pencil running diagonal). Now in the SQL Window type {call <insert procedure name>()}. This will call a procedure in Oracle say if you want to get a list of values for a parameter box.

3. If you need to pass in parameters:
  again type {call <insert procedure name>()}, but now go to the Layout Tab, create whatever parameters you need from the user. Now go back to the Data tab and change {call <insert procedure name>(?,?,?,?)}. Oracle requires unnamed parameters thus the ?. Also if you have 1 parameter then you would have 1 ?, etc. Now click on ... next to your datasource. Click on the Parameters tab, now for each parameter you just specified you would look like the following
NAME VALUE
? =Parameters!whatever.value

Click OK, Refresh to get your datafields populated. Click ! to run, you will be prompted to input parameters. Data should now show up.

Now you can go to Layout and using the Fields drag and drop them onto your layout, click preview, you will enter parameters and ta da you should have a report that is driven by an Oracle Stored Procedure.

-Matt

Ok, I can't get this to work. Here is my situation.

Table has three columns, SOURCE_SYS (Either 'A' or 'B') REPORT_ID and REPORT_NAME, both are varchar2.

Package Header:
create or replace package PKG_TEST_RS is
TYPE tReport_ID is TABLE of rpt_reports.report_id%TYPE
     INDEX BY BINARY_INTEGER;
TYPE tReport_Name is TABLE of rpt_reports.report_name%TYPE
     INDEX BY BINARY_INTEGER;

PROCEDURE getRptReport
         (vInList IN rpt_reports.source_sys%TYPE,
            Report_ID OUT tReport_ID,
            Report_Name OUT tReport_Name);

end PKG_TEST_RS;

Package Body:
create or replace package body PKG_TEST_RS is

PROCEDURE getRptReport
         (vInList IN rpt_reports.source_sys%TYPE,
            Report_ID OUT tReport_ID,
            Report_Name OUT tReport_Name)
IS
    CURSOR cReports IS
     SELECT rr.report_id,
             rr.report_name
        FROM rpt_reports rr
        WHERE rr.source_sys = vInList;

BEGIN

    FOR tmpReports IN cReports
    LOOP
     Report_ID(cReports%ROWCOUNT) := tmpReports.Report_ID;
     Report_Name(cReports%ROWCOUNT) := tmpReports.Report_Name;
    END LOOP;

END;

end PKG_TEST_RS;

R/S Info
OLEDB Connection

I tried to use the following "queries":

{ call PKG_TEST_RS.getRptReport('B') }
This one doesn't work because I get the wrong number of parameters

I don't know how to set up the parameters coming out of the Stored Procedure as Fields withing Reporting Services. I tried setting them up as Database Fields and Calculated Fields in the Dataset editor but it doesn't seem to work.