missieh
4Penny.net
Points: 10700

11/1/2018 10:26:54 AM

You need to learn pivot tables in Excel and here's why

* This article, and all our great Dynamics GP documentation, Is available on the Dynamics GP menu

This very simple, yet very useful pivot table in Excel puts all the paycodes from Dynamics GP in a spreadsheet for a payroll expert to review.

We will take data directly from the UPR30300 paycheck detail table like this...

And turn it into this, one line per employee, showing all their aggregated payroll code data. Note that the pay codes are grouped, in the screen shot below you can see Benefits and Deductions

 

Start with copying this select statement into a SQL query window and run it.

Select
      d.EMPLOYID as 'Employee_ID',
      PAYROLCD as Payroll_Code,
      case when PYRLRTYP = '1' then 'RegularPay'
              when PYRLRTYP = '2' then 'Deductions'
              when PYRLRTYP = '3' then 'Benefits'
              when PYRLRTYP = '4' then 'Taxes'
              when PYRLRTYP = '5' then 'LocalTax'
              else '' end as Payroll_Type,
      UPRTRXAM as Amount,
      CHEKDATE as Check_Date
              from upr30300 d
                  where year(d.CHEKDATE) = year(GETDATE())

4Penny.net
Version: All
Section: Dynamics GP,SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3