How can I use more than one Stored Procedure in a crystal report?

Question

I have two stored procedures I wish to use in my stored procedure, but once I do that it fails to load with the error: "Invalid Argument provided, no rowset retrieved." If I remove either one of them it starts working again.

I have the crystal report set up something like this:

Report:
  Group By Tenant.ReferedBy
    stored proc here that calculates the tenant's balance

and the second stored proc is in the Select for the report. I only want to select tenant's by a status flag, and I'm getting the status flag from a stored proc.

Neither of the two procedures are linked together in anyway. One returns one value (the one in the select), the other returns multiple (the one in the group by). Neither take any parameters and are both just simple SQL statements stored on the database.

First Proc: GetAllTenantBalances

SELECT (SUM(tblTransaction.AmountPaid) - SUM(tblTransaction.AmountCharged)) AS TenantBalance, tblTransaction.TenantID
 FROM tblTransaction
 GROUP BY tblTransaction.TenantID

Second Proc: [GetTenantStatusID_Current]

SELECT ID FROM tblTenantStatus WHERE Description = 'Current'

Can anyone tell me why I can't do this, and how to get around it?

Answer 1

Unless something has changed, you can't call multiple procs from a report. I doubt you can call multiple select statements. You will either need to create one proc that returns both pieces of data or create a subreport for the second proc.

Answer 2

You could change the first sp to only sum 'Current' tenants. Or, if you must keep two sp you will have to go to the database expert and join them by the tenant_id.

Answer 3

You could also use sub-reports to give you the data you want. Use on SP on the main, and then another SP on a sub-report.

-JFV