Join F0902 to F0911

QuintexServices

Member
I am only relatively new to using JDE but have the task of generating a series of reports using Crystal Reports XI. The report I am stuck on uses data from F0902 and F0911. I am using F0902 to create an opening balance field and F0911 to create Invoiced, Payments and Adjustments fields. I am joining using the GBAID, GBLT and GBFY fields. The problem I am encountering is that when F0902 finds (for example) two records that meets my criteria, it then produces all the records from F0911 twice. Does anyone have suggestions regarding my join or any other advice regarding reporting from these two tables?
 
There are other ways to skin this cat, but the way I've handled similar issues is to not join the F0902, let the F0911 drive the report. For each account group, fetch your opening balances as a subreport to the F0902, add up the one or two or five records for that account's beginning balance total, and return the value to the main report via a shared variable.
 
You know what they say - 'birds of a feather.....' Thanks for your suggestion, we think alike as that is exactly what I initially did with the report, with accurate results. The down-side, and this is why I'm now trying to link directly to both tables, is that the report took around 2.5 hrs to run. Now the report takes only a couple of minutes to run. Eventually, this will become an un-linked subreport on a much bigger report that will present an overall view of the financial status of the company. This is what is leading me to not use the method you have suggested, but thanks though for replying.
 
2.5 hours? Holy moly, batman!

Well, back to your join that creates multiple logical records... It's more cumbersome to build, but you can hide the detail, group on AID, show the 0911 info in that group footer. It gets messy showing any 0911 totals in higher level group footers, because you have to use RT running total formulas for each field and group level, set to calculate on change of AID instead of for each record. But that shouldn't slow it down from its current couple of minutes.

A more reusable solution (which I should explore for myself as well) would be if you can get your db guy to build a SQL view of the 0902 that is pre-summarized on the partial key you specified, so that you don't get multiple logical records when linking to F0911.
 
Thanks heaps, your suggestion sounds excellent - I'm not back onsite for a few days, but will definitely give your suggestion a go and report back here with the results.
 
It seems you forgot to join also on GBSBL, GBSBLT, GBCRCD that are part of the primary key of F0902.
 
Back
Top