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())