November 12, 2009 at 11:39 pm
I have a Crystal Report with the subreport and main report both use the same sql Storedprocedure. is there any way to avoid the stored procedure being executed Twice for both main and subreports so as to save execution time? (i am running this report directly from the Crystal Reports 11 application and not from a .net app)
Thanks.
Ron
December 15, 2009 at 9:18 am
Ron,
Let me ask one question first before answering yours, do you know if the subreport is showing summary info, showing summary info for the details that are shown in the main report? It has been my experience that more often then not, when you have a crystal report with 1 or more subreports and both the main report and all subreports use the same data source be it a stored procedure or view or SQL Query, it is done so because the report designer was not able to (or just didn;t know how to) set up summary sections in the report and opted to use subreports instead.
Kindest Regards,
Just say No to Facebook!December 21, 2009 at 2:04 am
Hi YSLGuru
thanks for the reply .
i have tried to use summary sections in the main report but the subreport became necessary as it summarises the data very differently to the main report.
Thanks .Ron
December 21, 2009 at 2:07 pm
Ron,
Something to consider with your scenario is changing your data source from using a STORED PROCEDURE to using a table. I say this for a couple of reasons. I don;t believe there is a way to recycle the SP call meaning no way to telkl Crystal to execute the SP just once and then use the results in the main report and then pass those same results down to a sub-report so that the SP isn't executing multiple times.
Technically when you use an SP, the same SP for your main report and again for 1 or more sub-reports you don;t have a way to gurantee a tie out between the main report and all of the sub-reports. Now it may be that the data the SP references is not very volatile so that it doesn't change between the multiple calls to the SP that Crystal makes however there's no guarantee of that. At my work we use SQL Server to store accounting data that is very volatile and it's mission critcial that all parts of a report that deals with the same data show the exact same info or at least info that ties out so that the detail info in one area of a report ties out to the summary section in a sub-report.
To handle this we use a table as a temporary place holder that stores the results of calling the Stored Procedure we use.
Kindest Regards,
Just say No to Facebook!December 22, 2009 at 1:23 am
This is another thread discussing the same topic:
http://www.sqlservercentral.com/Forums/Topic771488-361-1.aspx
I hope you find it useful.
Regards
Gianluca
-- Gianluca Sartori
December 22, 2009 at 12:07 pm
Gianluca Sartori (12/22/2009)
This is another thread discussing the same topic:http://www.sqlservercentral.com/Forums/Topic771488-361-1.aspx
I hope you find it useful.
Regards
Gianluca
Ron - This article/post referenced by Gianluca details exactly the process I was trying to describe; you should definately check it out.
Gianluca - I never knew (for certain) that the probelsm with using SP's in Crystal was a Crystal problem and not just my not being versed enough in Crystal Reports. Thanks for the lead.
Kindest Regards,
Just say No to Facebook!December 28, 2009 at 10:30 pm
thanks for the help and advise guys. Gianluca, your posting explains it well!.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply