Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

Topic: Annual Payroll Summary Report 

1.  Annual Payroll Summary Report

GOLD CONTRIBUTOR
Posted 15 days ago
I'm looking for a very simple payroll report, and I'm unable to find it.  For our pension audit, calendar year 2017, I need a list of employees and their annual gross payroll.  Literally, that is all. Well, Employee ID, employee name, and Total Salary.  Bonus points if it has number of hours, start date, term date, DOB.  One line per employee.  Super simple.

------------------------------
Lisa Levendusky
Exceptional Persons, Inc.
Waterloo IA
------------------------------


2.  RE: Annual Payroll Summary Report

GPUG ALL STAR
Posted 15 days ago
  |   view attached
Hi,
I feel certain there is a more elegant way to do this, but here's a query that summarizes the EmployeeSummary view along with hours from the PayrollHistoricalTrx view. I know it includes a bunch of fields you don't need, but it's what I had.
I don't know what you mean by 'Total Salary', what are you looking for here?

declare @year1 integer
set @year1 = 2017


SELECT Year
,(SELECT SUM([Units To Pay])
FROM PayrollHistoricalTrx
GROUP BY [Employee ID], Year
HAVING (Year = @year1) and PayrollHistoricalTrx.[Employee ID] = EmployeeSummary.[Employee ID] ) as Units_to_Pay
, [Employee ID]
, [First Name]
, [Middle Name]
, [Last Name]
, [Social Security Number]
, [Birth Date]
, [Start Date]
, [Last Day Worked]
, [Date Employee Inactivated]

, [Gross Wages - April]
+ [Gross Wages - August]
+ [Gross Wages - December]
+ [Gross Wages - February]
+ [Gross Wages - January]
+ [Gross Wages - July]
+ [Gross Wages - June]
+ [Gross Wages - March]
+ [Gross Wages - May]
+ [Gross Wages - November]
+ [Gross Wages - October]
+ [Gross Wages - September] as Gross_Wages

, [Federal Wages - April]
+ [Federal Wages - August]
+ [Federal Wages - December]
+ [Federal Wages - February]
+ [Federal Wages - January]
+ [Federal Wages - July]
+ [Federal Wages - June]
+ [Federal Wages - March]
+ [Federal Wages - May]
+ [Federal Wages - November]
+ [Federal Wages - October]
+ [Federal Wages - September] as 'W-2_Federal_Wages'

, [FICA/Social Security Wages - April]
+ [FICA/Social Security Wages - August]
+ [FICA/Social Security Wages - December]
+ [FICA/Social Security Wages - February]
+ [FICA/Social Security Wages - January]
+ [FICA/Social Security Wages - July]
+ [FICA/Social Security Wages - June]
+ [FICA/Social Security Wages - March]
+ [FICA/Social Security Wages - May]
+ [FICA/Social Security Wages - November]
+ [FICA/Social Security Wages - October]
+ [FICA/Social Security Wages - September] as 'FICA/Social_Security_Wages'

, [FICA/Medicare Wages - April]
+ [FICA/Medicare Wages - August]
+ [FICA/Medicare Wages - December]
+ [FICA/Medicare Wages - February]
+ [FICA/Medicare Wages - January]
+ [FICA/Medicare Wages - July]
+ [FICA/Medicare Wages - June]
+ [FICA/Medicare Wages - March]
+ [FICA/Medicare Wages - May]
+ [FICA/Medicare Wages - November]
+ [FICA/Medicare Wages - October]
+ [FICA/Medicare Wages - September] as 'FICA/Medicare_Wages'

, [Futa Wages - April]
+ [Futa Wages - August]
+ [Futa Wages - December]
+ [Futa Wages - February]
+ [Futa Wages - January]
+ [Futa Wages - July]
+ [Futa Wages - June]
+ [Futa Wages - March]
+ [Futa Wages - May]
+ [Futa Wages - November]
+ [Futa Wages - October]
+ [Futa Wages - September] as FUTA_Wages

, [Suta Wages - April]
+ [Suta Wages - August]
+ [Suta Wages - December]
+ [Suta Wages - February]
+ [Suta Wages - January]
+ [Suta Wages - July]
+ [Suta Wages - June]
+ [Suta Wages - March]
+ [Suta Wages - May]
+ [Suta Wages - November]
+ [Suta Wages - October]
+ [Suta Wages - September] as SUTA_Wages

, [Net Wages - April]
+ [Net Wages - August]
+ [Net Wages - December]
+ [Net Wages - February]
+ [Net Wages - January]
+ [Net Wages - July]
+ [Net Wages - June]
+ [Net Wages - March]
+ [Net Wages - May]
+ [Net Wages - November]
+ [Net Wages - October]
+ [Net Wages - September] as Net_Wages

, [Federal Withholding - April]
+ [Federal Withholding - August]
+ [Federal Withholding - December]
+ [Federal Withholding - February]
+ [Federal Withholding - January]
+ [Federal Withholding - July]
+ [Federal Withholding - June]
+ [Federal Withholding - March]
+ [Federal Withholding - May]
+ [Federal Withholding - November]
+ [Federal Withholding - October]
+ [Federal Withholding - September] as Federal_Withholding

, [FICA/Medicare Withholding - April]
+ [FICA/Medicare Withholding - August]
+ [FICA/Medicare Withholding - December]
+ [FICA/Medicare Withholding - February]
+ [FICA/Medicare Withholding - January]
+ [FICA/Medicare Withholding - July]
+ [FICA/Medicare Withholding - June]
+ [FICA/Medicare Withholding - March]
+ [FICA/Medicare Withholding - May]
+ [FICA/Medicare Withholding - November]
+ [FICA/Medicare Withholding - October]
+ [FICA/Medicare Withholding - September] as 'FICA/Medicare_Withholding'

, [FICA/Social Security Withholding - April]
+ [FICA/Social Security Withholding - August]
+ [FICA/Social Security Withholding - December]
+ [FICA/Social Security Withholding - February]
+ [FICA/Social Security Withholding - January]
+ [FICA/Social Security Withholding - July]
+ [FICA/Social Security Withholding - June]
+ [FICA/Social Security Withholding - March]
+ [FICA/Social Security Withholding - May]
+ [FICA/Social Security Withholding - November]
+ [FICA/Social Security Withholding - October]
+ [FICA/Social Security Withholding - September] as'FICA/Social_Security_Withholding'

, [Allocated Tips - April]
+ [Allocated Tips - August]
+ [Allocated Tips - December]
+ [Allocated Tips - February]
+ [Allocated Tips - January]
+ [Allocated Tips - July]
+ [Allocated Tips - June]
+ [Allocated Tips - March]
+ [Allocated Tips - May]
+ [Allocated Tips - November]
+ [Allocated Tips - October]
+ [Allocated Tips - September] as Allocated_Tips

, [Charged Receipts - April]
+ [Charged Receipts - August]
+ [Charged Receipts - December]
+ [Charged Receipts - February]
+ [Charged Receipts - January]
+ [Charged Receipts - July]
+ [Charged Receipts - June]
+ [Charged Receipts - March]
+ [Charged Receipts - May]
+ [Charged Receipts - November]
+ [Charged Receipts - October]
+ [Charged Receipts - September] as Charged_Receipts

, [Charged Tips - April]
+ [Charged Tips - August]
+ [Charged Tips - December]
+ [Charged Tips - February]
+ [Charged Tips - January]
+ [Charged Tips - July]
+ [Charged Tips - June]
+ [Charged Tips - March]
+ [Charged Tips - May]
+ [Charged Tips - November]
+ [Charged Tips - October]
+ [Charged Tips - September] as Charged_Tips

, [FICA/Med Tax On Tips - April]
+ [FICA/Med Tax On Tips - August]
+ [FICA/Med Tax On Tips - December]
+ [FICA/Med Tax On Tips - February]
+ [FICA/Med Tax On Tips - January]
+ [FICA/Med Tax On Tips - July]
+ [FICA/Med Tax On Tips - June]
+ [FICA/Med Tax On Tips - March]
+ [FICA/Med Tax On Tips - May]
+ [FICA/Med Tax On Tips - November]
+ [FICA/Med Tax On Tips - October]
+ [FICA/Med Tax On Tips - September] as 'FICA/Med_Tax_On_Tips'

, [FICA/Med Tips - April]
+ [FICA/Med Tips - August]
+ [FICA/Med Tips - December]
+ [FICA/Med Tips - February]
+ [FICA/Med Tips - January]
+ [FICA/Med Tips - July]
+ [FICA/Med Tips - June]
+ [FICA/Med Tips - March]
+ [FICA/Med Tips - May]
+ [FICA/Med Tips - November]
+ [FICA/Med Tips - October]
+ [FICA/Med Tips - September] as 'FICA/Med_Tips'

, [FICA/SS Tax On Tips - April]
, [FICA/SS Tax On Tips - August]
, [FICA/SS Tax On Tips - December]
, [FICA/SS Tax On Tips - February]
+ [FICA/SS Tax On Tips - January]
+ [FICA/SS Tax On Tips - July]
+ [FICA/SS Tax On Tips - June]
+ [FICA/SS Tax On Tips - March]
+ [FICA/SS Tax On Tips - May]
+ [FICA/SS Tax On Tips - November]
+ [FICA/SS Tax On Tips - October]
+ [FICA/SS Tax On Tips - September] as 'FICA/SS_Tax_On_Tips'

, [FICA/SS Tips - April]
+ [FICA/SS Tips - August]
+ [FICA/SS Tips - December]
+ [FICA/SS Tips - February]
+ [FICA/SS Tips - January]
+ [FICA/SS Tips - July]
+ [FICA/SS Tips - June]
+ [FICA/SS Tips - March]
+ [FICA/SS Tips - May]
+ [FICA/SS Tips - November]
+ [FICA/SS Tips - October]
+ [FICA/SS Tips - September] as 'FICA/SS _Tips'


, [Federal Tax On Tips - April]
+ [Federal Tax On Tips - August]
+ [Federal Tax On Tips - December]
+ [Federal Tax On Tips - February]
+ [Federal Tax On Tips - January]
+ [Federal Tax On Tips - July]
+ [Federal Tax On Tips - June]
+ [Federal Tax On Tips - March]
+ [Federal Tax On Tips - May]
+ [Federal Tax On Tips - November]
+ [Federal Tax On Tips - October]
+ [Federal Tax On Tips - September] as 'Federal_Tax_On_Tips'

, [Federal Tips - April]
+ [Federal Tips - August]
+ [Federal Tips - December]
+ [Federal Tips - February]
+ [Federal Tips - January]
+ [Federal Tips - July]
+ [Federal Tips - June]
+ [Federal Tips - March]
+ [Federal Tips - May]
+ [Federal Tips - November]
+ [Federal Tips - October]
+ [Federal Tips - September] as 'Federal_Tips'


, [Reported Receipts - April]
+ [Reported Receipts - August]
+ [Reported Receipts - December]
+ [Reported Receipts - February]
+ [Reported Receipts - January]
+ [Reported Receipts - July]
+ [Reported Receipts - June]
+ [Reported Receipts - March]
+ [Reported Receipts - May]
+ [Reported Receipts - November]
+ [Reported Receipts - October]
+ [Reported Receipts - September] as Reported_Receipts

, [Reported Tips - April]
+ [Reported Tips - August]
+ [Reported Tips - December]
+ [Reported Tips - February]
+ [Reported Tips - January]
+ [Reported Tips - July]
+ [Reported Tips - June]
+ [Reported Tips - March]
+ [Reported Tips - May]
+ [Reported Tips - November]
+ [Reported Tips - October]
+ [Reported Tips - September] as Reported_Tips

, [Uncollected FICA/Med Tax - April]
+ [Uncollected FICA/Med Tax - August]
+ [Uncollected FICA/Med Tax - December]
+ [Uncollected FICA/Med Tax - February]
+ [Uncollected FICA/Med Tax - January]
+ [Uncollected FICA/Med Tax - July]
+ [Uncollected FICA/Med Tax - June]
+ [Uncollected FICA/Med Tax - March]
+ [Uncollected FICA/Med Tax - May]
+ [Uncollected FICA/Med Tax - November]
+ [Uncollected FICA/Med Tax - October]
+ [Uncollected FICA/Med Tax - September] as 'Uncollected_FICA/Med_Tax'

, [Uncollected FICA/SS Tax - April]
+ [Uncollected FICA/SS Tax - August]
+ [Uncollected FICA/SS Tax - December]
+ [Uncollected FICA/SS Tax - February]
+ [Uncollected FICA/SS Tax - January]
+ [Uncollected FICA/SS Tax - July]
+ [Uncollected FICA/SS Tax - June]
+ [Uncollected FICA/SS Tax - March]
+ [Uncollected FICA/SS Tax - May]
+ [Uncollected FICA/SS Tax - November]
+ [Uncollected FICA/SS Tax - October]
+ [Uncollected FICA/SS Tax - September] as 'Uncollected_FICA/Med_Tax'

, [W2 Box For 942 Employee]
, [W2 Box For Deceased]
, [W2 Box For Deferred Compensation]
, [W2 Box For Legal Representative]
, [W2 Box For Retirement Plan]
, [W2 Box For Statutory Employee]
FROM EmployeeSummary
where Year = @year1


------------------------------
Leslie Vail
Accounting Systems Consulting, Inc.
Dallas TX
leslievail@earthlink.net 972-814-8550
------------------------------

Attachment(s)



3.  RE: Annual Payroll Summary Report

SILVER CONTRIBUTOR
Posted 14 days ago
​Lisa,
Since you have probably closed out your year end recently you could also use the year end table and join it on the employee master.
Here is an example of the script using the year end tables.  There are no hours, but it gives you the basic data.

SELECT        dbo.UPR10101.EMPLOYID, dbo.UPR10101.LASTNAME, dbo.UPR10101.FRSTNAME, dbo.UPR00100.BRTHDATE, dbo.UPR00100.STRTDATE, dbo.UPR00100.DEMPINAC, dbo.UPR10101.WGTPCOMP
FROM            dbo.UPR00100 INNER JOIN
                         dbo.UPR10101 ON dbo.UPR00100.EMPLOYID = dbo.UPR10101.EMPLOYID

------------------------------
Tanya Henderson
Senior Consultant
S2 Technology
Park City UT
------------------------------



4.  RE: Annual Payroll Summary Report

GOLD CONTRIBUTOR
Posted 12 days ago
Thank you for the information.  I sent the script to to our developer to work his magic.  However, therein lies the problem.  Is there really nothing built in to Dynamics as a standard report that will give me this basic information??  I was hoping for something simple.

------------------------------
Lisa Levendusky
Exceptional Persons, Inc.
Waterloo IA
------------------------------



5.  RE: Annual Payroll Summary Report

TOP CONTRIBUTOR
Posted 12 days ago
Lisa,
By using the tables listed by Tanya, you should be able to get this through SmartList Designer (SLD) or SmartList Builder (SLB).  I'm showing SLD since everyone owns it.

Open SmartList, click on Payroll, then click New to open SLD.
Choose the Payroll Year End Wage file and then select the fields you want.



Choose the Payroll Master table and pick applicable files.

Use the middle section to join the tables then the filter to restrict to the right year.
Click Execute Query to see the results in the bottom and see if they are close to what you want.

If you have SmartList Builder then you can use that functionality with these same tables.
Hope that gets you there.
Thanks
Windi

------------------------------
Windi Epperson
President/GP Senior Consultant
Advanced Integrators, Inc.
Norman OK
405-946-1774 Ext 102
------------------------------



6.  RE: Annual Payroll Summary Report

GOLD CONTRIBUTOR
Posted 11 days ago

​Hi Lisa, I think all the Ladies above provided great examples.  There are reports in Microsoft Dynamics GP, but usually they are more than you want, or not in the format you want to export to excel, thus the examples above.

Prior to year end close you can run the detailed employee list should give you a lot of it.
Reports | Payroll | Employee List | Detailed Employee List

If it is for after you create the year end, then the year end wage report
Routines | Year End Wage Report

Another favorite is under Period End Reports the Payroll Summary, you can run it by an employee and date range.

If you just want to see pay history for pay codes, FUTA, SUTA, WC codes, rates and pay, then one would be Reports | Payroll | History | Employee Pay History.

Those are all default reports in GP for you to use, but many on the thread know there downfalls as well!

Thanks, have a great rest of your week.

Terry Heley
Microsoft



------------------------------
Terry Heley
HR/PR expert
Microsoft
West Fargo ND
------------------------------



7.  RE: Annual Payroll Summary Report

GOLD CONTRIBUTOR
Posted 11 days ago
Thank you ALL!!  I find the screen shots especially helpful, Windi, for my brain to wrap around.  And by starting with Payroll Year End Wage, it gives me employees who've terminated (we delete all termed employees at year end). Two more items that I need - total hours worked and total 401k deduction.  If it included these two things, it'd be perfect.

------------------------------
Lisa Levendusky
Exceptional Persons, Inc.
Waterloo IA
------------------------------



8.  RE: Annual Payroll Summary Report

GOLD CONTRIBUTOR
Posted 11 days ago
Ok, one more problem.  This doesn't give me the true gross wages.  It's giving me the W-2 wages.

------------------------------
Lisa Levendusky
Exceptional Persons, Inc.
Waterloo IA
------------------------------



9.  RE: Annual Payroll Summary Report

TOP CONTRIBUTOR
Posted 10 days ago
​Hi,

Two other tables to use to get the payroll check data: UPR30100 and UPR30300

Try this for a start:

select EMPLOYID
 ,EMPLNAME
 ,sum(GRWGPRN) as YYD_Gross
 ,sum(TTLDDTNS) as YTD_Deductions
 ,sum(NTWPYRN) as YTD_Net_Wages
from UPR30100
Where YEAR1 = 2017
group by EMPLOYID, EMPLNAME



------------------------------
Mark Wiley
Senior Consultant
Tribridge
Akron, OH
------------------------------



10.  RE: Annual Payroll Summary Report

SILVER CONTRIBUTOR
Posted 9 days ago
Mark has a very good start!

BUT make sure you check the VOIDED column!
(Just ran an overtime report with some very alarming numbers until I joined UPR30100 and Voided)

select EMPLOYID
 ,EMPLNAME
 ,sum(GRWGPRN) as YYD_Gross
 ,sum(TTLDDTNS) as YTD_Deductions
 ,sum(NTWPYRN) as YTD_Net_Wages
from UPR30100
Where YEAR1 = 2017 AND VOIDED = 0
group by EMPLOYID, EMPLNAME

------------------------------
Rebeccah Grimes
Tech24
A Commercial Foodservice Repair, Inc Company
------------------------------



11.  RE: Annual Payroll Summary Report

TOP CONTRIBUTOR
Posted 10 days ago
Hi @Lisa Levendusky,
Not related to your original question, but I was wondering in your case why you'd delete former/terminated employees in the system ? isn't there a legal binding to keep those records for a least a couple of years of case of an audit or legal claim ?
Here in Canada I believe the law forces you to keep at least 6 or 10 years of employee records on file..
(we delete all termed employees at year end).
Lisa Levendusky,  02-13-2018 10:50 AM



------------------------------
Beat Bucher
Business Analyst, Dynamics GP MVP
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
------------------------------



12.  RE: Annual Payroll Summary Report

GOLD CONTRIBUTOR
Posted 10 days ago
@Beat Bucher, that's a question I asked myself.  We do keep history, but the Payroll Master record is deleted.  We have a large turnover - 65% last year - so it bogs down the system to have these extra records, I'm told.  Although the history is still there, so not sure why that would be the case.  ​

------------------------------
Lisa Levendusky
Exceptional Persons, Inc.
Waterloo IA
------------------------------



13.  RE: Annual Payroll Summary Report

TOP CONTRIBUTOR
Posted 10 days ago
Thanks Lisa,
Though we do have a low turnover, -65% doesn't say much :-) if your company has thousands of employees, that would make a lot of inactive records, but if we're talking about 50-100 employee, it's not that much in the end..

What slows down a GP system in my opinion are transaction records that are not moved ot history and even there, it can become an issue.. I'm not sure a payroll system would be that much slower because there are a few hundres inactive records in the PR tables.. 2/3 of our PR records are inactive in our company (about 360 of 530).. I never felt it would slow down the payroll generation process.. but I might be wrong for larger data sets.

------------------------------
Beat Bucher
Business Analyst, Dynamics GP MVP
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
------------------------------



14.  RE: Annual Payroll Summary Report

GOLD CONTRIBUTOR
Posted 10 days ago
@Beat Bucher, we currently have about 365 employees, and had 220 inactive employees in 2017 (for just 2017) that were removed.  Personally, in my nearly 25+ years of accounting (including working at an accounting firm) , I have never heard of deleting any payroll records until I started here about 18 months ago.  I believe the practice is in place because the option in Dynamics is to delete all inactive records or none.  So in a few years' time, we could easily accumulate 1000 inactive records.

------------------------------
Lisa Levendusky
Exceptional Persons, Inc.
Waterloo IA
------------------------------