Use of F0902 to create year over year financials

  • Thread starter Robert Robinson
  • Start date
Robert Robinson

Robert Robinson

Reputable Poster
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,

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
 
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.
 
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,

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}+{ACCOUNT_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,
 
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.
 
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)?
 
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
smile.gif


Cheers,
 
Back
Top