John,
I did a similar project for a client, which seems to include most of the data you're looking for and some you are not. This will help you get started, and understand the scope of the project. Once you have a good select query from SQL, you can use it to create a view. Then you grant select to the DYNGRP on the view. You can then use SmartList Builder or Designer to turn that view into a usable SmartList.
select
YTD.LPCHKNUM Check_Number,
convert(varchar(10),YTD.LSTPCKDT,101) Check_Date,
(substring(SOCSCNUM,1,3)+'-'+substring(SOCSCNUM,4,2)+'-'+SUBSTRING(SOCSCNUM,6,4)) 'SSN',
(rtrim(LASTNAME)+', '+rtrim(FRSTNAME)+' '+substring(MIDLNAME,1,1)) 'Last, First MI',
convert(varchar(10),BRTHDATE,101) DOB,
CONVERT(varchar(10),STRTDATE,101) DOH,
CASE DEMPINAC When '1900-01-01 00:00:00.000' THEN ''
ELSE CONVERT(varchar(10),DEMPINAC,101)
END DOT,
case b.PAYRCORD when 'HOUR' Then(PAYRTAMT * 2080)
when 'SALY' then (PAYRTAMT)
End 'ANNUAL SALARY',
c.Hours 'HOURS',
(GROSWAGS_1+GROSWAGS_2+GROSWAGS_3+GROSWAGS_4+GROSWAGS_5+GROSWAGS_6+GROSWAGS_7+GROSWAGS_8+GROSWAGS_9+GROSWAGS_10+GROSWAGS_11+GROSWAGS_12) 'TOTAL COMP',
d.[401K] 'EMPLOYEE PRE-TAX CONTRIBUTION',
e.ROTH 'ROTH 401K CONTRIBUTION',
'0.00' 'SAFE HARBOR MATCH',
f.LOAN 'LOAN REPAY',
g.CONTRIBPRETAX_I 'CONTRIBUTION RATE PRE-TAX',
h.CONTRIBAFTERTAX_I 'CONTRIBUTION RATE ROTH',
'' 'EXCLUDED EMPLOYEES',
EMP.DIVISIONCODE_I,
EMA.ADDRESS1,
EMA.ADDRESS2,
EMA.CITY,
EMA.STATE,
EMA.ZIPCODE
from UPR00100 EMP
left join UPR00102 EMA on EMP.EMPLOYID = EMA.EMPLOYID
Left join UPR00900 YTD on EMP.EMPLOYID = YTD.EMPLOYID
Left Join (
select EMPLOYID,PAYRCORD,PAYRTAMT from UPR00400
where PAYRCORD in ('SALY','HOUR'))b
on EMP.EMPLOYID = b.EMPLOYID
Left join (
select EMPLOYID,Sum(UNTSTOPY) 'Hours' from UPR30300
where PAYROLCD in ('HOUR','OT')
Group By EMPLOYID) c
on EMP.EMPLOYID = c.EMPLOYID
Left Join (
select EMPLOYID,SUM(UPRTRXAM) '401K' from UPR30300
where PAYROLCD in ('401K','401K-F')
Group By EMPLOYID) d
on EMP.EMPLOYID = d.EMPLOYID
Left Join (
select EMPLOYID,SUM(UPRTRXAM) 'ROTH' from UPR30300
where PAYROLCD in ('401KR','401K-RF')
Group By EMPLOYID) e
on EMP.EMPLOYID = e.EMPLOYID
Left Join (
select EMPLOYID,SUM(UPRTRXAM) 'LOAN' from UPR30300
where PAYROLCD in ('401KLN')
Group By EMPLOYID) f
on EMP.EMPLOYID = f.EMPLOYID
Left Join(
select distinct EMPID_I,CONTRIBPRETAX_I from HR2BEN10
Where CONTRIBPRETAX_I Is not null
and BENEFIT = '401K') g
on EMP.EMPLOYID = g.EMPID_I
Left Join(
select distinct EMPID_I,CONTRIBAFTERTAX_I from HR2BEN10
Where CONTRIBAFTERTAX_I Is not null
and BENEFIT = '401K-R') h
on EMP.EMPLOYID = h.EMPID_I
