SSRS Report from Oracle DB - Use stored procedure
- by Emtucifor
I am developing a report in Sql Server Reporting Services 2005, connecting to an Oracle 11g database. As you post replies perhaps it will help to know that I'm skilled in MSSQL Server and inexperienced in Oracle.
I have multiple nested subreports and need to use summary data in outer reports and the same data but in detail in the inner reports. In order to spare the DB server from multiple executions, I thought to populate some temp tables at the beginning and then query just them the multiple times in the report and the subreports.
In SSRS, Datasets are evidently executed in the order they appear in the RDL file. And you can have a dataset that doesn't return a rowset. So I created a stored procedure to populate my four temp tables and made this the first Dataset in my report. This SP works when I run it from SQLDeveloper and I can query the data from the temp tables. However, this didn't appear to work out because SSRS was apparently not reusing the same session, so even though the global temporary tables were created with ON COMMIT PRESERVE ROWS my Datasets were empty.
I switched to using "real" tables and am now passing in an additional parameter, a GUID in string form, uniquely generated on each new execution, that is part of the primary key of each table, so I can get back just the rows for this execution.
Running this from Sql Developer works fine, example:
DECLARE
ActivityCode varchar2(15) := '1208-0916 ';
ExecutionID varchar2(32) := SYS_GUID();
BEGIN
CIPProjectBudget (ActivityCode, ExecutionID);
END;
Never mind that in this example I don't know the GUID, this simply proves it works because rows are inserted to my four tables.
But in the SSRS report, I'm still getting no rows in my Datasets and SQL Developer confirms no rows are being inserted.
So I'm thinking along the lines of:
Oracle uses implicit transactions and my changes aren't getting committed?
Even though I can prove that the non-rowset returning SP is executing (because if I leave out the parameter mapping it complains at report rendering time about not having enough parameters) perhaps it's not really executing. Somehow.
Wrong execution order isn't the problem or rows would appear in the tables, and they aren't.
I'm interested in any ideas about how to accomplish this (especially the part about not running the main queries multiple times). I'll redesign my whole report. I'll stop using a stored procedure. Suggest anything you like! I just need help getting this working and I am stuck.
If you want more details, in my SSRS report I have a List object (it's a container that repeats once for each row in a Dataset) that has some header values and then contains a subreport. Eventually, there will be four total reports: one main report, with three nested subreports. Each subreport will be in a List on the parent report.
