1 Reply Latest reply on Aug 11, 2015 7:52 AM by philmodjunk

    Counting Unique Values in Sub Summary Report

    sccardais

      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. 

        • 1. Re: Counting Unique Values in Sub Summary Report
          philmodjunk

          Instead of getting one row for each department by quarter, I'm getting a row for each paycheck. Records are not being grouped.

          By what you describe, I would expect a row for each employee that received a paycheck for that department and quarter since you put it in a sub summary part "when sorted by fk_ID_Payee".

          If you want a single row for each department, use a sub summary part "when sorted by" your department field for this summary field.