Results 1 to 8 of 8

Thread: Use of F0902 to create year over year financials

  1. #1
    Senior Member Robert Robinson's Avatar
    Join Date
    Oct 2000
    Location
    Toledo, OH
    Posts
    471

    Use of F0902 to create year over year financials

    I am creating an income statement that compares "year over year data" using Crystal (for example, I want to display Q1 2008 and Q1 2007 on the same report. Thanks to Larry Jones' hints, I am close to my goal).

    My final issue is regarding the use of the F0902 table to place data from 2 years side by side. The F0902 stores period end balances by month by year in separate records. My overall selection criteria include "8" and "7" for 2008 and 2007, and I have created template field objects to summarize the monthly data. My problem is that each set of template field objects include both 2007 and 2008 data, and I can not see how to filter the data in the template field objects so that 1 column displays the 2008 data, and the other displays the 2007 data only. If someone can show me what (obviously simple!) step I am missing, I would be extremely grateful. Thanks.
    Robert Robinson
    JD Edwards Business Systems Manager
    Health Care REIT, Inc.
    E1 900, Tools 9.1.3.3
    Oracle 11g, WebLogic 10.3.6.0
    Quest Business Intelligence SIG
    Toledo, Ohio, USA
    Viva la Devolution!

  2. #2
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    2,747

    Re: Use of F0902 to create year over year financials

    Robert,

    in the detail section have different conditional formula's for the Amount fields - one that assigns value for current year, one for prior year.
    Then sum those values to a group (Object Account, BU, etc) and only show the group - suppress the detail.

    Good Luck
    Larry Jones
    E1 9.0 - TR 9.1 on Win 2008 64. Oracle DB 11gR2
    WebLogic 11g, Wintel
    BI Publisher

  3. #3
    Senior Member Robert Robinson's Avatar
    Join Date
    Oct 2000
    Location
    Toledo, OH
    Posts
    471

    Re: Use of F0902 to create year over year financials

    Thanks again.

    That is the direction that I was heading (I have grouped by object account); I was just having issues with my conditional formatting. I may have an issue with the initial grouping/sorting. Thanks for confirming the direction.
    Robert Robinson
    JD Edwards Business Systems Manager
    Health Care REIT, Inc.
    E1 900, Tools 9.1.3.3
    Oracle 11g, WebLogic 10.3.6.0
    Quest Business Intelligence SIG
    Toledo, Ohio, USA
    Viva la Devolution!

  4. #4
    Senior Member Robert Robinson's Avatar
    Join Date
    Oct 2000
    Location
    Toledo, OH
    Posts
    471

    Re: Use of F0902 to create year over year financials

    Larry -

    I am still struggling with this issue. Since I am creating quarter to date financials, I have created a template field object for the 2008 and 2007 columns (these sum up the GBAN01-GBAN03 columns for a quarterly total). Where I am encountering difficulty is in getting the 2008 column to display 2008 data and the 2007 column to display 2007 data. I have tried to use the formula workshop to set GBFY=8 and 7 in the two columns at the group and template object level, with no success. When I "RTFM", I do not see reference to a hierarchy at the Record Selection, Group Selection and Template Object levels.

    Right now, if I select GBFY = 8 at the Record Selection level, I get the Q1 2008 data in both columns; if I select GBFY = 7 at the Record Selection level, I get Q1 2007 in both; If I select GBFY = 7 ,8 I get both years in each column. If I try to use the "GBFY = " selection at the Group or Object Selection levels, I get gobbledygook. I know I am missing something fundamental, but I just do not see what.
    Robert Robinson
    JD Edwards Business Systems Manager
    Health Care REIT, Inc.
    E1 900, Tools 9.1.3.3
    Oracle 11g, WebLogic 10.3.6.0
    Quest Business Intelligence SIG
    Toledo, Ohio, USA
    Viva la Devolution!

  5. #5
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    2,747

    Re: Use of F0902 to create year over year financials

    Robert,

    not sure why you're using template fields - but then, I've never used them myself.

    What I was referring to was creating a formula field. In the formula definition for the Q1 of Prev year field you'd say something like:
    ---------------------------------------------------
    if {ACCOUNT_BALANCES_F0902.FISCAL_YEAR_GBFY} = ({?Current Plan Year}-1) and ... then {ACCOUNT_BALANCES_F0902.AMT_PERIOD_01_GBAN01}+{ACC OUNT_BALANCES_F0902.AMT_PERIOD_02_GBAN02}+{ACCOUNT _BALANCES_F0902.AMT_PERIOD_03_GBAN03}
    else 0
    ---------------------------------------------------

    Place that formula in the detail section, then place a summay of the field in a group header/footer section. Do the same for current year. That way you can see Prior year and current year on the same line (remember to suppress the detail).

    Record data selection should include both years 7 & 8.

    I also would not use the formula workshop - just use the formula editor directly.

    Good luck,
    Larry Jones
    E1 9.0 - TR 9.1 on Win 2008 64. Oracle DB 11gR2
    WebLogic 11g, Wintel
    BI Publisher

  6. #6
    Senior Member Robert Robinson's Avatar
    Join Date
    Oct 2000
    Location
    Toledo, OH
    Posts
    471

    Re: Use of F0902 to create year over year financials

    Why template fields? 'Cause I was making it harder than it had to be...

    I finally got the thing to work. My resulting conditional statements are:
    If {V0902C.GBFY} = 7 Then
    ({V0902C.GBAN01} + {V0902C.GBAN02} + {V0902C.GBAN03})/1000
    Else
    0
    If {V0902C.GBFY} = 8 Then
    ({V0902C.GBAN01} + {V0902C.GBAN02} + {V0902C.GBAN03})/1000
    Else
    0

    I think I like your notion of no template fields (may provide flexibility later on). Thanks for confirming the approach.
    Robert Robinson
    JD Edwards Business Systems Manager
    Health Care REIT, Inc.
    E1 900, Tools 9.1.3.3
    Oracle 11g, WebLogic 10.3.6.0
    Quest Business Intelligence SIG
    Toledo, Ohio, USA
    Viva la Devolution!

  7. #7
    Senior Member Robert Robinson's Avatar
    Join Date
    Oct 2000
    Location
    Toledo, OH
    Posts
    471

    Re: Use of F0902 to create year over year financials

    Larry -

    Thank you for your assistance. The financials (and supplemental information) are looking great.

    An overall question (actually, two): are your end users satisfied with what you have created in Crystal? Also, is the "Business Objects to SAP" issue going to change your report creation and delivery processes (i.e. will you stay with Crystal or move to BI Publisher)?
    Robert Robinson
    JD Edwards Business Systems Manager
    Health Care REIT, Inc.
    E1 900, Tools 9.1.3.3
    Oracle 11g, WebLogic 10.3.6.0
    Quest Business Intelligence SIG
    Toledo, Ohio, USA
    Viva la Devolution!

  8. #8
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    2,747

    Re: Use of F0902 to create year over year financials

    You're welcome Robert.

    1. Accountants (and other users) are extremely happy with Crystal. Only exception I can think of is when the Crystal reports designer attempts to use it as the only tool in his toolbox. Quick queries are best answered using a JDE Application or some other tool in my opinion.

    2. Regarding SAP Ownership. What I've read is that SAP is supposed to be taking a "hands-off" approach to the product. On the other hand their web support looks like its being subsumed into the SAP web support . . . We lived through BO eating Crystal Decisions. We'll live through this. The big question is what they'll do to licensing. Our total cost of ownership is so low ( <$10,000) the functionality cannot be touched by anything with over 10 times that cost. When we move to web licensing, thats a different story

    Cheers,
    Larry Jones
    E1 9.0 - TR 9.1 on Win 2008 64. Oracle DB 11gR2
    WebLogic 11g, Wintel
    BI Publisher

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Incorrect WTD balance in F0902 for a Closed year
    By Mohammed Zubairuddin Khan in forum JD Edwards® EnterpriseOne Applications
    Replies: 0
    Last Post: 05-05-2010, 09:19 AM
  2. Fiscal Year - Consolidation - F0902 - Financial UBE'S.
    By fmansur in forum JD Edwards® EnterpriseOne Applications
    Replies: 1
    Last Post: 03-05-2009, 08:52 AM
  3. Changing Fiscal Year / Date Pattern - Short Year?
    By dgoboff in forum JD Edwards® EnterpriseOne Applications
    Replies: 3
    Last Post: 10-01-2008, 11:41 AM
  4. Financials - Fiscal year Change
    By sriviruth in forum JD Edwards® EnterpriseOne
    Replies: 1
    Last Post: 06-26-2008, 01:50 PM
  5. Replies: 1
    Last Post: 08-22-2005, 04:18 PM

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, Oracle or Peoplesoft.