Results 1 to 6 of 6

Thread: Join F0902 to F0911

  1. #1
    New Member
    Join Date
    Sep 2008
    Location
    Australia
    Posts
    3

    Join F0902 to F0911

    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?


  2. #2
    Senior Member
    Join Date
    Jun 2002
    Location
    Lancaster, PA
    Posts
    117

    Re: Join F0902 to F0911

    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.
    Curtis Heller
    XE Update 3 / SP17.1 / SQL / Win2000 / Citrix

  3. #3
    New Member
    Join Date
    Sep 2008
    Location
    Australia
    Posts
    3

    Re: Join F0902 to F0911

    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.

  4. #4
    Senior Member
    Join Date
    Jun 2002
    Location
    Lancaster, PA
    Posts
    117

    Re: Join F0902 to F0911

    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.
    Curtis Heller
    XE Update 3 / SP17.1 / SQL / Win2000 / Citrix

  5. #5
    New Member
    Join Date
    Sep 2008
    Location
    Australia
    Posts
    3

    Re: Join F0902 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.

  6. #6
    New Member
    Join Date
    Nov 2007
    Location
    France
    Posts
    2

    Re: Join F0902 to F0911

    It seems you forgot to join also on GBSBL, GBSBLT, GBCRCD that are part of the primary key of F0902.
    OneWorld XE Update2 SP23V1
    Windows 2003
    MS-SQL 2005
    Citrix
    2500 users

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.