AnsweredAssumed Answered

Counting Unique Values in Sub Summary Report

Question asked by sccardais on Aug 10, 2015
Latest reply on Aug 11, 2015 by philmodjunk

Title

Counting Unique Values in Sub Summary Report

Post

 

My table "PAYCHECKS" contains individual payments to people with the following fields:

         
  • Payee
  •      
  • fk_ID_payee
  •      
  • Amount
  •      
  • Date
  •      
  • 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. 

Outcomes