Open Forum

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

Annual Payroll Summary Report

  • 1.  Annual Payroll Summary Report

    GOLD CONTRIBUTOR
    Posted 02-09-2018 03:53 PM
    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 02-10-2018 03:51 AM
      |   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 02-10-2018 11:23 PM
    ​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 02-12-2018 05:51 PM
    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 02-12-2018 07:05 PM
    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

    GPUG ALL STAR
    Posted 02-13-2018 09:39 AM

    ​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 02-13-2018 10:51 AM
    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 02-13-2018 03:56 PM
    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 02-14-2018 08:18 AM
    ​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 02-15-2018 09:33 AM
    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

    GPUG ALL STAR
    Posted 02-14-2018 08:08 AM
    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 02-14-2018 09:35 AM
    @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

    GPUG ALL STAR
    Posted 02-14-2018 09:57 AM
    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 02-14-2018 12:43 PM
    @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
    ------------------------------