Totaling/Subtotaling a Calculated Field

[jawilson]

Good Morning all.

I have a formula field that I would like to subtotal by group. The problem is that one element of the formula is a summarized field (IE. {@UOP FCTR} * Sum ({PORECLINE.ENT_REC_QTY}, {ITEMMAST.ITEM}).

I would like to then subtotal the output of this formula by group, but from what I understand you cannot subtotal a summarized/calculated field.

Any ideas?
Thanks in advance,
Jamie Wilson

 

[Nilsen]

You will want to use variables to total it by group.

Have a variable in the group header to reset the value to 0 at the start of each group, such as:

WhilePrintingRecords;
NumberVar GroupTotal:=0;

Have a variable in the details (you can suppress this formula) to accumulate your totals:

WhilePrintingRecords;
NumberVar GroupTotal:=GroupTotal + {@formulafield};

Then have a variable to display your total:

WhilePrintingRecords;
NumberVar GroupTotal;
GroupTotal

There is an FAQ written by MalcolmW that goes over this very well also.

 

[jawilson]

Thanks so much. It worked great as far as I can tell. I will be sure to check out the FAQ for further explaination.

One more question...Is there a way to make the groups sort by this total from highest to lowest. Obviously the report automatically sorts by the group name....I would like to resort these groups by $totals.

Thanks again!

 

[kenhamady]

Not with the approach you are taking.
A better approach is to use the formula:

{@UOP FCTR} * {PORECLINE.ENT_REC_QTY}

Place this on the detail band and then you can create a subtotal of it, and a grand total of it. (Insert subtotal/Grand total)

Once you have the subtotal you can use the TopN feature to order the groups by the subtotal. I don't know what it is in the FCTR formula, so I can't guarantee that this will work, but it should. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)