Stored Procedure With Multiple Result Sets As Report Source : Crosspost

[Former Member]

Hello Everyone,

I have an issue where i have created a stored procedure that returns multiple result sets

/* Input param = @SalesOrderID */
 
SELECT * FROM Orders TB1
  INNER JOIN OrderDetails TB2 ON  TB1.ID = TB2.ID
WHERE TB1.OrderID = @SalesOrderID
 
SELECT * FROM Addresses
  WHERE Addresses.OrderID = @SalesOrderID AND Addresses.AddressType = 'Shipping'
 
SELECT * FROM Addresses
  WHERE Addresses.OrderID = @SalesOrderID AND Addresses.AddressType = 'Billing'

This is just a quick sample, the actual procedure is a lot more complex but this illustrates the theory.

When I set the report source in Crystal X to the stored procedure it is only allowing me to add rows from the first result set.

Is there any way to get around this issue?

The reason that I would prefer to use a stored procedure to get all the data is simply performance. Without using one big stored procedure I would have to run at least 6 sub reports which is not acceptable because the number of sub reports could grow exponentially depending on the number of items for a particular sales order.

Any ideas or input would be greatly appreciated.

TIA

- Adam

P.S

Sorry for the cross post, I originally posted this question [here|/community [original link is broken];

but was informed that it might be the wrong forum

Edited by: Adam Harris on Jul 30, 2008 9:44 PM

[former_member208657 · Accepted solution]

Crystal Reports doesn't support multiple result sets. Please see note 1208799 for some supporting info.

https://www.sdn.sap.com/irj/sdn/businessobjects-notes

[Former Member · Accepted solution]

Hello David,

Thank you for the reply, although it was not the answer I was hoping for but at least i can start looking at alternatives.

Thanks again

- Adam

[former_member208657 · Accepted solution]

You may want to look into using subreports and use a unique stored proc for each one.