deBronkart
asked on
Do I *need* a subreport?
Man, the subreports I described in https://www.experts-exchange.com/questions/21848908/Subreport-formulas-are-blank-but-text-boxes-aren't-if-no-records-found.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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.