Crystal Report question - how to get check, month, quarter, and yearly totals

 4 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
Phil Simon
Veteran Member Send Private Message
Posts: 135
Veteran Member

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

 

Process level

200

 

Deduction

 

Federal – T000

 

YTD 2,000,000

QTD 577,777

MTD 180,000

9/21/07 40,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

 

YTD 1,000,000

QTD 277,777

MTD 80,000

 

1/1/07 45000

1/8/07 47,000

all the way down to…

9/21/07 40,000

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!

Attachments
Phil Simon http://philsimonsystems.com/ phil@philsimonsystems.com
Phil Simon
Veteran Member Send Private Message
Posts: 135
Veteran Member
I see that I can suppress check_date in the formula editor. I just have to figure out how to suppress the check total.

I am very close. I found this link as well:

http://diamond.businessobjects.com/node/793
Phil Simon http://philsimonsystems.com/ phil@philsimonsystems.com
John Henley
Send Private Message
Posts: 3351
I actually do it differently. Put everything in the details section, suppress the details but accumulate into formulas which I display in the group footers.
Thanks for using the LawsonGuru.com forums!
John
Chris Martin
Veteran Member Send Private Message
Posts: 277
Veteran Member
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...

Phil Simon
Veteran Member Send Private Message
Posts: 135
Veteran Member

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. 

Phil Simon http://philsimonsystems.com/ phil@philsimonsystems.com