Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Summing a field you can't sum 1

Status
Not open for further replies.

travelerII

Technical User
Jun 4, 2009
66
US
I am using Crystal 10. I have created a report the calculate inventory devaluation and now I need to split the totals by FG, RM intercompany, and RM third party.
I have used the following formula to get the value for FG and placed it in group footer #1

whileprintingrecords;
numbervar sumx := If {ITMMASTER.ACCCOD_0} = "FG" then {@Final Dev Amt}
it is retuning the correct amounts for FG. I then need to total these amounts so I placed the following formula in the report footer.

whileprintingrecords;
numbervar sumx;

However it is returning 0.00 not the total of FG.

Any thoughts on what I have done wrong or on a different approach?


 
Please show the content of {@Final Dev Amt} and also explain what fields you are grouping on.

-LB
 
It is a complex calculation, basically what I am doing is taking a devaualtion amount calculation and comparing to an expiration amount calculation and then choosing the higher to arrive at the {@Final Dev Amt}

Formula for {@Final Dev Amt}
if IsNull (Sum ({@Expiration Amt}, {ITMMASTER.ITMREF_0})) then {@Devaluation Amount} else
if Sum ({@Expiration Amt}, {ITMMASTER.ITMREF_0}) > {@Devaluation Amount} then Sum ({@Expiration Amt}, {ITMMASTER.ITMREF_0}) else {@Devaluation Amount}

I am grouping on {ITMMASTER.ITMREF_0}

 
The reason I asked about your grouping, is that I am concerned about your conditional formula:

If {ITMMASTER.ACCCOD_0} = "FG" then
{@Final Dev Amt}

This formula says: If the current record = "FG" then show {@Final Dev Amt} for all records referenced in {@Final Dev Amt}. And if you were grouping on ACCCOD-0, that might be okay, but you don't appear to be. I think you need to add a group on{ITMMASTER.ACCCOD_0}, but I can't tell whether it should be group #1 or Group #2, but I'm guessing it should be the outer group #1. Once you have the results segregated by acccod group, you can use formulas like this:

//{@reset} for the acccod_0 group #1 header:
whileprintingrecords;
numbervar sumx;
if not inrepeatedgroupheader then
sumx := 0;

//{@accum} to be placed in the {ITMMASTER.ITMREF_0} group #2 section:
whileprintingrecords;
numbervar sumx := sumx + {@Final Dev Amt};

//{@display} to be placed in the acccod_0 group #1 footer:
whileprintingrecords;
numbervar sumx;

-LB
 
Thanks for your help. I am a little confused though.

I want the information grouped on {ITMMASTER.ITMREF_0}. And my formula
If {ITMMASTER.ACCCOD_0} = "FG" then
{@Final Dev Amt}
is returning the {@Final Dev Amt} for FG only on each line of the group footer #1.
Essentialy all I need to do is now total that amount.

Are you saying that even though I have the correct information I can't total it in the report footer without regrouping on the {ITMMASTER.ACCCOD_0}? If I did that it would change the presentation wouldn't it?



 
I thought you wanted results for some other values, too, which was why I suggested the group. I am saying I am not convinced that you are getting the correct result for "FG" as I tried to explain earlier. Just as a test, place the acccod_0 field and your formula {@Final Dev Amt} in the detail section and see if the result is unique to FG.

You should be able to adapt the method I showed above to your current situation. If your approach is accurate, then just change the {@accum} formula to:

//{@accum} to be placed in GF#1:
whileprintingrecords;
numbervar sumx := sumx +
(
if {ITMMASTER.ACCCOD_0} = "FG" then
{@Final Dev Amt}
);

//{@display} to be placed report footer:
whileprintingrecords;
numbervar sumx;

It appears you are trying to get a grand total, so you don't need the reset formula.

-LB
 
Thanks for your help. That seems to be giving me what I need, though I will need to run some more checks.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top