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.

[former_member183750]

Adam, apologies for the redirect, but it is better to have .NET posts in one place. That way anyone can search the forum for answers. (and I do not have the rights to move posts).

Anyhow, as long as the report is created, you should be able to pass the datasets as:

crReportDocument.Database.Tables(0).SetDataSource(dataSet.Tables("NAME_OF_TABLE"))

Of course alternatively, (not sure if this is possible in your environment) you could create a multi-table ADO .NET dataset and pass that to the report.

Ludek

[Former Member]

Thank you for your reply.

However, the dataset is not being passed to the report from .NET we are relying on Crystal to connect and execute the stored procedure (which is not a problem). The problem is that the Crystal Designer only shows the fields from the first record set returned from the stored procedure which is preventing us from being able to actually design the report. If we were passing in the dataset that would work great at run time but still prevent us from actually designing the report.

Thanks again for taking the time to reply.

- Adam