Sorting Groups In Reports

[zr500racer]

I have made a report that gives me information on products we sell (ItemNumber) and what we get for profit in doing so. We have about 300+ individual items that we manufacture, so I group them by ItemNumber. After they are grouped I need to sort these groups by profit, that way it's easier for management to tell which ones we make more money on. I have messed around with my report for a while, but can't seem to be able to make that happen. Below is what the design of my report looks like so far. Any information I could get would be very highly appreciated. Thanks!
_________________________________________________________
ItemNumber Header
---------------------------------------------------------
ItemNumber(Field)
_________________________________________________________
Detail
---------------------------------------------------------
ExtMatCost(field) LaborCost(field) PriceBase(field)
_________________________________________________________
ItemNumber Footer
---------------------------------------------------------
TotalMaterialCost =Sum([ExtMatCost])

PriceBase(field)

LaborCost (field)

TotalCost =([TotalMaterialCost])+[LaborCost])

Profit =[PriceBase]-[TotalCost]
_________________________________________________________

 

[RickSpr]

Make a copy of this report to be used as a subreport.

In the subreport, remove the grouping and associated header and footer.

Create the following query:
SELECT ItemNumber,
Sum(ExtMatCost) As TotalMaterialCost,
Sum(LaborCost) As TotalLaborCost,
Sum(PriceBase) As TotalPriceBase,
Sum(ExtMatCost + LaborCost) As TotalCost,
Sum(PriceBase - ExtMatCost - LaborCost) As Profit
FROM <your original record source>
GROUP BY ItemNumber
ORDER BY Sum(PriceBase - ExtMatCost - LaborCost) Desc
Make this query the Record Source for your main report.

In the main report, delete the current detail fields. Move the ItemNumber field from the header to the detail section. Follow it with the subreport (linked on ItemNumber), then add TotalMaterialCost, TotalPriceBase, TotalLaborCost, TotalCost, and Profit from the column list. Delete the Sorting and Grouping specifications.

Explanation: You need to sort by Profit, but that's group-level data. You can't sort by group-level data, so you have to turn the grouping into details. That's what the query does. Then, in order to get what was formerly the details, you use a subreport.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein