Querying Employee Gross Wages By Year From Dynamics GP

There are several ways to calculate gross wages for employees in Dynamics GP.  The most common method for doing this is to use the built-in SmartList reporting tool within the application.  However SmartList reports are somewhat limited in the way they calculate and display the data.  Often times the Accounting group wants to see the data organized in several different formats which would require them to build and run multiple SmartList reports which can be time consuming.  This method also requires them to spend unnecessary time merging the data onto a single spreadsheet once all the reports have been run.

Fortunately there is an easier and faster method for calculating gross wages across the entire company.  The following script pulls wages from the UPR00900 – Payroll Employee Summary table and matches up the employee name against the UPR00100 – Payroll Master table.

What makes this solution superior to the SmartList option is that the script will not only display monthly wages but will also calculate the total gross wages as well as total gross per quarter.  This is typically the 3 ways that Accounting wants to see the data.

The script is designed to return all employees that have wage calculations that are greater than 0.   This is the best way to determine whether an employee was active during the specified year.  Querying based on the employee status would likely result in employees who had been paid during the specified year being omitted from the data as the status would only signify that they were inactive at the time the report was being executed.

The best option for implementing this solution is to create an SSRS report to allow the users to run the report on their own.   Additional modifications can be made to the script to allow the user to pass not only the desired year but also the employee ID should they want to query individual employees.  I have included an additional AND clause, which is currently commented out, to allow you to specify a particular Employee ID.