如何在水晶报告中使用多个存储过程？

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.

并且第二个存储过程在Select for the report中。我只想通过状态标志选择租户,并且我从存储过程中获取状态标志。

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.

无论如何,这两个程序都没有联系在一起。一个返回一个值(select中的一个),另一个返回multiple(group by中的一个)。既不采用任何参数,也只是存储在数据库中的简单SQL语句。

First Proc: GetAllTenantBalances

First Proc:GetAllTenantBalances

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

Second Proc: [GetTenantStatusID_Current]

第二个程序:[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?

任何人都可以告诉我为什么我不能这样做,以及如何解决它?

3 个解决方案

#1

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.

您可以将第一个sp更改为仅“当前”租户。或者,如果您必须保留两个sp,则必须转到数据库专家并通过tenant_id加入它们。

#2

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.

您还可以使用子报告为您提供所需的数据。在主服务器上使用SP,然后在子服务器上使用另一个SP。

-JFV

#3

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.

除非发生了某些变化,否则您无法从报告中调用多个过程。我怀疑你可以调用多个select语句。您将需要创建一个返回两个数据的proc或为第二个proc创建一个子报告。

#1