I need to create a payroll report that pulls current, month to date, QTD, and YTD deduction totals from PAYDEDUCTN, grouped by process level and deduction code.
Ideally, this report would look like this
Process level
100
Deduction
Federal – T000
YTD 1,000,000
QTD 277,777
MTD 80,000
9/21/07 40,000
200
YTD 2,000,000
QTD 577,777
MTD 180,000
I am very close to this report using groupings and SUM(FLD, CONDFLD). I built a parameter for PAYROLL-YEAR and have left the check date and quarter fields blank. Entering them as part of my selection criteria make YTD=MTD=QTD=current pay period.
My problem is that, because I left them blank, I am getting all of the detail for each check_date as well as quarter. I can post this
1/1/07 45000
1/8/07 47,000
all the way down to…
I have attached screen shots of the design view and the output. My question is this: how do I basically suppress all non-current values while, at the same time, using their detail to drive YTD and MTD calculations?
Thanks!
how do I basically suppress all non-current values while, at the same time, using their detail to drive YTD and MTD calculations
How I would do it involves a combination of sql logic (nested select to limit the records returned to only "current" ded codes) and manual running total formulas to calculate the MTD/YTD amounts (although Crystal running totals will probably be sufficient in this case). Check out my reply to your Lawson Community post for the sql...
I got it by suppressing sections. It works like a charm.
Thanks, all! This is one of the 5 coolest reports that I've written... Maybe the bar is low, though.