Counting Unique Values in Sub Summary Report
My table "PAYCHECKS" contains individual payments to people with the following fields:
- c_Fiscal_Quarter (calculates fiscal quarter and year)
- DeptID (of Payee)
In a sub summary report, I want to show the total amount paid by Fiscal Quarter and by Department.
Fiscal Quarter Department Total Headcount Total Paychecks
I'm having trouble getting the Total Headcount. Multiple checks are paid to the same person in any quarter. I want to show the number of unique people. I've tried the "Sum of Reciprocal" method but it's not working for some reason. I've defined the fields below:
- s_TotalCountPayees = Count of fk_ID_Payee
- c_Fraction = 1 / GetSummary (sTotalCountPayees;fk_ID_Payee)
- s_TotalFraction = Total of c_Fraction
I placed s_TotalFraction in a sub summary part when sorted by fk_ID_Payee and sorted the report by Fiscal Quarter, Department and fk_ID_Payee.
Instead of getting one row for each department by quarter, I'm getting a row for each paycheck. Records are not being grouped.
Any help would be appreciated.