Search code examples
crystal-reports

How can I order a group, based on a summary field of a subgroup

I have a report which has essentially

  • Order
    • OrderDetail 1
    • OrderDetail ..
    • OrderDetail n

These details can have parts and/or labour costs associated with them.

Currently, I group based on OrderId and then have the OrderDetail information in the details section of the report. This works perfectly.

However, now I need to group the Orders based on two criteria OrderType and LabourCost of the entire Order. I have put together a quick formula to determine order.

if(Sum({order.Labour}, {order.OrderId})> 0) then
  if({order.type} = "type1") then 1 else 2
else
  if({order.type} = "type1") then 3 else 4

Basically, if it should be sorted based on labour then on type. (the Sum({order.Labour}, {order.OrderId}) sums the labour grouping based on the orderid)

However when I go to the Group Expert and add the group by field to my formula and then preview my report it spins (I cancelled the preview after a minute). If I remove the Sum portion of the formula then it takes less than a second.

Is there a way to order this report?

Solution

  • How I would approach it:

    First, create a sql-expresssion field that calculates the labor total for each order:

    // {%TOTAL_LABOR}
(
SELECT Sum(Labour)
FROM   OrderDetail
WHERE  OrderId=Order.OrderId
)

    Next, create a formula field:

    // {@OrderGroup}
if({%TOTAL_LABOR}> 0) then
  if({order.type} = "type1") then 1 else 2
else
  if({order.type} = "type1") then 3 else 4

    Finally, create a new group, based on the formula field, ensuring that it groups before the order group. You can suppress the group's header and footer if desired.