Translate Subreport into Stored Procedure?

[alembong]

We are using CR9, Oracle 9 and I have a report that has several subreports. I think because of the subrpts, the report runs super slow. So, I was thinking that I need to try to recreate the report using a stored procedure. My problem is that I've never created a subreport, but I've modified SPs that other people have created.

The main report is grouped by Employee, Budget Code and Payroll Code from a Payroll table.
The employee info, Sum(PayrollAmount) and Sum(PayrollHours) are displayed on the Payroll Code group footer level.
The Budget Code group footer contains a subreport that is linked on employee and budget code which displays the Sum(DollarAmt) for Budget code from the GL table.
The Budget Code group footer also has a subreport which lists transactions from the GL table.

I need to get the stored proc to return the data from the two tables, but I guess I really don't know how I join the tables in the SP since the subreport links per Budget Code. I tried an equal join and got multiplicate information and the outer join I set up returned nothing back to the report.

Any help and/or direction would be appreciated.

 

[IdoMillet]

It would be easier to answer if you provide the table names, relevant columns, the SQL you tried, desired output and actual output.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

To do so in a SP you would probably use a correlated subquery, however I urge you to contatc your dba for writing the SP if you're unfamiliar with this.

-k