Do I *need* a subreport?

Posted on 2006-05-16
Last Modified: 2006-11-18
Man, the subreports I described in are S-L-double-O.  Sure ain't fun watching the record counter go over and over and over the same data for the subreports.  So I'm taking a step back and wondering if I need to do it that way.

Particularly, maybe my JOINs aren't doing as much for me as they could.  I think I'm just getting the hang of what an aggregate record is, and CROSS JOIN vs OUTER JOINs.  

The report originally reported on products that have a low order level, by looking at order history to see what was ordered. But that was a mistake - we realized we need to *start* with the list of products (duh) and then look at order history.  Then we added the realization that we need to look at each plant & warehouse separately.

(Obviously we're having a lot of fun discovering what CR can help us see, that's been buried in our piles of legacy data files that were never connected before. :-))

Then we discovered that there are numerous products that no longer have any kits that use them (!).  So for each product, the subreport goes out to the kit list and fetches all kits that use that product.

Now I'm thinking: you dork, you should START with the kit list, at each plant, and build a cartesian product (cross join) of all combinations of product X kit X warehouse, and compare *that* against order history.

It would produce a bunch of aggregate records that have numerous null fields, but there's nothing wrong with that, and it would only do it once, right?

That should avoid running the same search over and over again, yes?  Or am I being wrong-headed?
Question by:deBronkart
    1 Comment
    LVL 42

    Accepted Solution

    >>> It would produce a bunch of aggregate records that have numerous null fields, but there's nothing wrong with that, and it would only do it once, right?

    That sounds fine based on what you've explained.  There's nothing inherently wrong with doing a cartesian product as long as that's what you're expecting and are prepared to deal with it (we get lots of questions from people that don't realize that's what they're doing and thing Crystal is duplicating records).  

    The only thing to be cautious with is Crystal does not handle null-values well in formulas.  Any formula that might encounter a null field you'll need to include an explicit "IsNull()" check - otherwise the results will be unpredictable.

    If you currently have subreports that are executing over and over then I would *absolutely* look at another approach.  



    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now