Print Page | Close Window

1 to Many w/o Sub Reports???

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7811
Printed Date: 02 May 2025 at 9:36pm


Topic: 1 to Many w/o Sub Reports???
Posted By: moosehead
Subject: 1 to Many w/o Sub Reports???
Date Posted: 27 Sep 2009 at 3:28pm
I have a large, complicated report with a structure like so:
 
Project Record (Parent) Fields  1 to 1 with Project
   Sub Records 1   0 to many with Project
   Sub Records 2   0 to many with Project
   Sub Records 3   0 to many with Project
   Sub Records 4   0 to many with Project
   Sub Records 5   0 to many with Project
   Sub Records 6   0 to many with Project  
 
I've tried the normal approach which for me is to create a sub report for each of the sub sections. The performance is so bad that it is not close to usable.
 
I've tried all kinds of groupings with suppressions, etc with everything on the parent report but the results are wildly unpredicatable. All of the sub sections appear to react to each other in ways that I can't control with limiting formulas etc.
 
Has anyone successfully faced this problem before?
 
Thanks, Moosehead


-------------
thanks!
moosehead


Replies:
Posted By: lockwelle
Date Posted: 28 Sep 2009 at 6:12am
Subreports, in general, are a bad idea.  Each subreport results in a another hit to the database.  So if your main report has 100 records, you will make 601 calls to the database...very resource intensive.
 
How I tend to handle any report is to create a stored proc which has the data for every field in every record, so there may be lots of records, but only 1 call to the database.  With all the data, the rest is just grouping or sorting.
 
I deal with a special case where subreports don't call the DB...I use a disconnected recordset (ADO.net), so my subreports just look at the data that is already there, but I still try to avoid subreports.
 
HTH

Posted By: moosehead
Date Posted: 28 Sep 2009 at 6:23am
Thank you for your reply. How do you get around pulling a cartesien product for the sub report data?
 
For instance, since I have 6 sub reports how do you create one data set that doesn't have those reports creating a huge number of rows?


-------------
thanks!
moosehead

Posted By: lockwelle
Date Posted: 28 Sep 2009 at 10:39am
It depends, since I don't know your data, and I don't know how disjointed it is, the best that I can say, is...you don't.
 
You get a huge number of rows, and then you use conditional suppression to...no wait.
 
I see your point, the data isn't going to group/display correctly.
 
usually I only have 1 detail section...maybe multiple lines but they are alternative views of the detail line, not 6 completely different sets of data in what is basically 1 detail line.
 
I must be blessed with much simpler reports as all of my data is related and my users just want their numbers and not lots of other data
 
sorry, I guess my solution won't work for you.

Posted By: moosehead
Date Posted: 28 Sep 2009 at 10:59am
Thanks for your try! The strategy that I'm working with is to move only the most complex and resource hogging sub report into the main report. I will end up with a slow report, but not one that dies on the highway!

-------------
thanks!
moosehead


Print Page | Close Window