• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

Do I *need* a subreport?

Man, the subreports I described in http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21848908.html 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?
0
deBronkart
Asked:
deBronkart
1 Solution
 
frodomanCommented:
>>> 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.  

HTH

frodoman
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now