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

Sort:
You are not authorized to post a reply.
Author
Messages
Phil Simon
Veteran Member
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
    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
      Senior Member
      Posts: 3348
      Senior Member
        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
        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
          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
            You are not authorized to post a reply.