Summarizing a GetSummary Calc

[Rod Haden]

Is it possible to have a summary field that is the total of a calculation field that uses GetSummary?

I am running a sub-summary report that finds all line items in a date range and sorts them by employee name. The fields are:

num_Hours_Worked

summary_Total_Hours_Worked

calc_num_Overtime_Hours =

If ( (GetSummary ( summary_Total_Hours_Worked; text_Name_Full )- 40 ) > 0 ; GetSummary ( summary_Total_Hours_Worked; text_Name_Full ) - 40 ; "")

I then have a summary field summary_Total_Overtime_Hours.

The sub-summary report, sorted by text_Name_Full correctly totals the overtime hours for all of the line items in the date range for each employee. The problem is the summary_Total_Overtime_Hours in the Grand Trailing Summary at the bottom. It wildly inflates the actual total of overtime hours across all employees.

Am I missing something, or is it just not possible to summarize a calculation based on a summary?

Thanks!

[comment]

You should divide the overtime by the count of records of each employee. That way the grand total will even out.

[Rod Haden]

Aha! Since my report has no body, only a sub-summary part, the calc that uses the GetSummary function only appears once, but it actually exists for each record in the found set, so that if there are 4 line items for an employee, the calc is added 4 times in the grand trailing summary.

There doesn't appear to be a function returning a boolean for whether or not a record is in the current found set. Is there? That would make things much simpler: a count where InFoundSet=1 and fk_EmployeeID=fk_EmployeeID.

Is there an easy way to figure out how many records are in the found set with fk_EmployeeID=fk_EmployeeID?

[comment]

it actually exists for each record in the found set, so that if there are 4 line items for an employee, the calc is added 4 times in the grand trailing summary.

Exactly.

Is there an easy way to figure out how many records are in the found set with fk_EmployeeID=fk_EmployeeID?

GetSummary ( sCount ; fk_EmployeeID )

where sCount is a summary field counting any field that cannot be empty, e.g. the serial ID.

--

BTW, using EmployeeID instead of the name is a smart move.

[Rod Haden]

Beautiful, perfect. Thank you! And just for posterity, I had a discrepancy with the grand summary calculation being off by $0.01 because of the differences in rounding, so I had it operate on Round (hours; 2)/ # of line items so that it comes out to the same as what you get if you actually add the column.

Thanks for the help!