Skip to Content
author's profile photo Former Member
Former Member

Stored Procedure With Multiple Result Sets As Report Source : Crosspost

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on Jul 30, 2008 at 09:44 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 30, 2008 at 08:06 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.