DynDeveloper.com

DynDeveloper.com

Sign Up Now!Log In
missieh 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())

Version: All
Section: Dynamics GP,SQL Scripts