Question Re: Sum at Group Level in Technology Q&A

Sum at Group Level

mdgarner57 — Mon, 19 Apr 2021 13:22:35 GMT

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?

Re: Sum at Group Level

DellSC — Mon, 19 Apr 2021 13:46:18 GMT

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

Re: Sum at Group Level

mdgarner57 — Mon, 19 Apr 2021 13:54:25 GMT

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.

Re: Sum at Group Level

DellSC — Mon, 19 Apr 2021 14:03:31 GMT

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

Re: Sum at Group Level

mdgarner57 — Mon, 19 Apr 2021 20:22:28 GMT

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

Re: Sum at Group Level

DellSC — Mon, 19 Apr 2021 21:29:40 GMT

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