Group Formula

[Sukh007]

Hi Guys

Hope someone can help as this is driving me crazy.

I have data for person sales and I have grouped this as below.

Joe Bloggs - Total Sales = £210.00 (This is group section)
sale 1 = £100.00
sale 2 = £210.00

Jon Jones Total Sales = £70.00
sale 1 = £70.00

If I add a summary field in the report at the end to get the sum of the total sales it display £280.00 which is correct as it is overall value of sales.

I have added a formula to the group section which will only display where a users total sales is over £200.

Sum ({@Value}, {CONTACT1.CONTACT}) > 200.00

The formula above only displays Joe Bloggs sales as Jon Jones sales are less than £200. However the overall sales still shows as £280.00 as it still calculates Jon Jones sales and not as £200. Jon Jones still shows in the group tree but not in the main report.

How do I get the sum to calculate from the criteria I have select in the group section formula. Thanks for the help.

 

[lbass]

Is sale2 for Joe Bloggs a typo? Should it be 110?

When you use group selection, you have to use running totals for summaries, since non-group selected records are still IN the report though not displayed and they contribute to the more usual inserted summaries. In this case, insert a running total that does a sum of {table.sales}, evaluate for each record, reset never. Place this in the report footer (only accurate in a footer section).

-LB

 

[Sukh007]

Thanks for the Reply ibass.

Yes Sale 2 for Joe Bloggs should be £110.00, was a typo.

I have never used Running Totals before so will research that and give it a go. Would I have to create a new formula for running totals?

Thanks for the feedback.

 

[Sukh007]

I created a formula call 'aa' and this is the code

if Sum ({@Value},{CONTACT1.CONTACT} ) > 200 then Sum ({@Value},{CONTACT1.CONTACT} )
else
0

So basically if the overall sales of a contact is over 200 then it will display their overall value else it will display 0.

I then want to calculate the overall value of 'aa' and I have created another formula with running total as below

NumberVar Running_Sum;
WhilePrintingRecords;
Running_Sum := Running_Sum + {@aa}


This seems to work only if I put it in the group footer and not report footer?

 

[lbass]

To insert a running total, go to the field explorer->running total->new and then follow the directions in my earlier post.

-LB

 

[Sukh007]

Oh I did it the long/hard way.

Thanks for the info LBass. i have managed to figure it out thanks to your info.

Much appreciated.