Sum Distinct vs Sum on Gross Earnings

[RptSm]

We have recently switched to PCPW 5.2 & ReportSmith 4.0. I am having an issue with summing gross earnings in a derived field. On one employee the Sum Distinct works great but on a another is doesn't. The following is the SQL that is not working properly. thanks...

SELECT
SUM(DISTINCT "REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWGROSSPAYA") , "REPORTS"."V_EMPLOYEE"."ANNUALSALARY", "REPORTS"."V_EMPLOYEE"."CUSTAREA1", "REPORTS"."V_EMPLOYEE"."CUSTAREA4", "REPORTS"."V_EMPLOYEE"."EEOCJOBCLASS", "REPORTS"."V_EMPLOYEE"."EMPLOYEETYPE", "REPORTS"."V_EMPLOYEE"."JOBTITLEDESCR", "REPORTS"."V_EMPLOYEE"."LOCATIONCODE", "REPORTS"."V_EMPLOYEE"."NAME", "REPORTS"."V_EMPLOYEE"."PAYFRQ", "REPORTS"."V_EMPLOYEE"."RATE1AMT", "REPORTS"."V_EMPLOYEE"."RATETYPE", "REPORTS"."V_EMPLOYEE"."REHIREDATE", "REPORTS"."V_EMPLOYEE"."STATE", "REPORTS"."V_EMPLOYEE"."STATUS", "REPORTS"."V_EMPLOYEE"."TERMINATIONDATE", "REPORTS"."V_EMPLOYEE"."TERMINATIONREASON"
FROM
"REPORTS"."V_CHK_VW_EARNINGS", "REPORTS"."V_EMPLOYEE"
WHERE
((("REPORTS"."V_EMPLOYEE"."COMPANYCODE" NOT IN ( 'XYZ', 'Z8V')) AND
("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWYEAR#" = '2005') AND
("REPORTS"."V_EMPLOYEE"."EEOCJOBCLASS" IN( 'GI', 'GK', 'GL', 'JH', 'JI', 'JJ', 'JK', 'JL', 'KI', 'KK', 'KL')) AND
("REPORTS"."V_CHK_VW_EARNINGS"."COMPANYCODE" = "REPORTS"."V_EMPLOYEE"."COMPANYCODE" ) AND ("REPORTS"."V_CHK_VW_EARNINGS"."FILE#" = "REPORTS"."V_EMPLOYEE"."FILE#" ) AND ("REPORTS"."V_CHK_VW_EARNINGS"."SOCIALSECURITY#" = "REPORTS"."V_EMPLOYEE"."SOCIALSECURITY#" )

 

[CharlesCook]

This should help:

http://charlescook.com/PPS/ReportSmithandADPPCPayrollforWindows-DatabaseGrouping.pps

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[CharlesCook]

Did that power point help?

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[RptSm]

Yes & No. Basically, in order to get accurate numbers I had to add a footer and sum all the records. The derived fields did not work correctly. According to ReportSmith tech support, they indicated this was the way to do it. However, I never had this issue before.

Thank you for your help though.