Sum at Group Level

[#1]

I am new to Crystal Reports so this might be a rookie question but I've developed a report that uses two tables - the first contains record header information and the second contains related line item information for the header records. My first Group is based on the header information and the details are the line item information. One of my formula fields is a simple calculation of # of days late (Desired Date - Actual Date) that is calculated at both the Group and Detail level. I am trying to sum the # of days late at the first Group Level rather than at the detail level.

Example: Group 1 Days Late = -1

Detail 1a = -1

Detail 1b = -1

Sum = -2. vs Desired Answer = -1

I've tried placing the Sum Function in the Group 1 Footer, but it still sums the detailed records. What am I missing?

[DellSC · Accepted solution]

You'll need to use a running total to get the grand total. Create the running total like this:

Field to Summarize: Select the formula you created
Type of Summary: Sum
Evaluate: On Change of Group
Reset: Never

Assuming that you want to average over the number of groups and that the running total is called {#DaysLate}, your average formula would be something like this:

If DistinctCount({field you've grouped on}) > 0 then
  {#DaysLate} / DistinctCount({field you've grouped on})
else
 0

-Dell

[DellSC]

Is the number of days late available at the group level, or do you need to calculate it for the group?

If all of the details have the same number of days late within a given group, then just put the days late formula in the group footer. If they don't have the same days late, then you might want to do a Maximum instead of a Sum.

-Dell

[mdgarner57]

Number of days late is calculated at both the group and detail level. I have tried to sum on the group formula but it gives me the sum of the detail. Can not find a way to isolate the sum to the group level only.

[DellSC]

Are you trying to sum the groups to show a grand total?

[mdgarner57]

Yes. Trying to sum for a grand total so I can use that sum amount to calculate and average days late for the group.