Summary of Group Totals

[adam7]

Hi there, I'm looking for help with a problem I have. using SQL and Crystal 8.5. I have a report that is basically a quote. I have grouped the report by the product type and currently it works fine and looks a little like this....

Hardware (Group 1)

Item Item Name Unit Price Qty Total
1 Big Spring 1000 2 2000
2 Bigger Spring 2000 1 2000

Total £4000

Software (Group 1)

Item Item Name Unit Price Qty Total
1 Bug riddled 100 1 100
2 Bug Riddled 2 2000 1 2000

Total £2100

Ok so the grandtotal would be £6100. However, in the report footer they want a summary....

Hardware = £4000
Software = £2100

Total = £6100

Apart from doing a cross tab I can't see how I would do this. However, I cannot do a cross tab as I have a seperate field I need to display and add into the total, so I cannot use the Grand total sum for the Total = £6100, it would have to be a collection of formulas.

I can't think how to do this, as I'd like

Formula1 (hardware)
Formula2 (Software)
Formula3 (Other field I need adding)
Grand Total (formula1+Formula2+other field I need adding)

I'm sure it's probably done via variables but haven't a clue. Any help would be very much appreciated.

 

[satinsilhouette]

Insert subtotals into group footers. Then go to section expert and set underlay to be on, which will give the effect of the subtotals being in the report footer.

Thanks so much!
satinsilhouette

 

[lbass]

Please show how the "Separate field" fits into your sample data.

-LB

 

[adam7]

Ok well you asked for it!, This is very difficult to explain. Against each item under hardware there is a support cost, which when tallyed make the support total. However they wanted all support costs in one section, so I created a sub report which filters for the same items in the hardware/software lists, but only shows where the items where there is a support cost entered. So currently it looks something like this...

Hardware

Item Item Name Unit Price Qty Total
1 Big Spring 1000 2 2000
2 Bigger Spring 2000 1 2000

Total £4000

Software (Group 1)

Item Item Name Unit Price Qty Total
1 Bug riddled 100 1 100
2 Bug Riddled 2 2000 1 2000

Total £2100

**SECTION BELOW IS A SUBREPORT**

Support Item Support cost Qty Total

1 Big Spring 100 1 100
2 Bigger Spring 200 1 200

Total £300


So all looks fine, but I need a summary at the bottom, so it says
Hardware=4000
Software=2100
Support=300

Total=6400

Ok so I've already grouped on "Hardware/Software" group, so couldn't have a "Support" group as well as the support group info comes from the data that's already in the "hardware/software" info.

The support figure I can get as a formula easily as the info is contained against each item so a simple sum. Just having trouble in working out how I can say

Hardware=4000
Software=2100

And have this added to
Support=300

and then adding those fields together for the overall price.

Sorry if it isn't clear.

 

[lbass]

You can use running totals for the first two figures where you summarize {@total} and use an evaluation formula like:

{table.groupfield} = "Hardware"//or "Software"

Then in the subreport, create a formula to be placed in the subreport footer:

whileprintingrecords;
shared numbervar subrpttot := sum({@total});

Then place the running totals in the report footer, and create a formula to display the subreport total:

whileprintingrecords;
shared numbervar subrpttot;

Add this to the report footer. Use text boxes to identify each value.

-LB