4 Replies Latest reply: May 15, 2014 10:05 AM by Olly Bond RSS

    Template Stumper!

    Grant _

      I have a report that very long that I am trying to get data from.  It should be a fairly simple template except for one piece.  The trap line for the detail match data that exits in the report for division totals as well and I don't want that data.  The report is a payroll register, I am trying to trap all the deduction and earnings code per employee and I can do that, but the report also contains subtotal of all the deductions everytime the department changes and the trap is exactly like that of the detail.  any help would be greatly appreciated....  How do I get it to ignore the total section...  I am using a Multi-Column-Region to get all the codes and I am Trapping with NNNN in the deduction code. 

      Sample below

       

              Doe, Johnny (123456)                                   Batch # 64 Disbursed Pay Run: 84 (Sequence: 1536)         

                   Pay #:  15          Payroll: BIWEEKLY HOURLY      Unit: XXXX-USTW             Work:  NB GEO: AB                    

                  Period:  200915         Catg: REGULAR             Group: PFT                   Home:  NB GEO: AB                   

              Period End:  Jul 18, 2009    Job: 0820-MTN PSN         Dept: xxxx-xxxxxxxxxxxxx    School:                                

              Issue Date:  Jul 24, 2009   Posn: XXXXX0820             Locn: XXXX                 Rev#:                                                                               

      Execution # 6578 run by P2K using RPREGC.rtf-20090427                            Prepared by ePersonality 4.08.00       

              Run: Aug 11, 2009 2:26:38 PM                Current Pay Register                                Page 6 of 754           

              Sorted By: Last Name/First Name                                                                               

      0010 Gross Pay.............1,084.96  3030 Dental EE................11.62 8000 Fed Tax Earnings......1,139.21            

              0020 Total Deductions........315.12  3200 RRSP % EE................54.25 8005 CPP Earnings..........1,139.21            

              0030 Net Pay.................769.84  4000 Life Ins ER.................00 8007 CPP Exemption...........134.62            

              0070 Total Deposit Amount....769.84  4005 AD&D ER.....................75 8010 EI Earnings...........1,139.21            

              0131 Stat Bnk Pay Out Hrs........00  4010 Dep Life ER.................00 8011 EI Hours.................80.00            

              0155 Vacation Pay Tkn Hrs (...80.00  4015 LTD ER......................00 9005 Vacation Accruals Hrs.....6.46            

              1031 Stat Bnk Pay Out $..........00  4020 WI ER.....................6.74 9100 Sick Leave Hrs Balance...80.00            

              1055 Vacation Pay Tkn $ (..1,080.80  4025 Health ER................46.02 9105 Sick Lv Hrs Earned.......80.00            

              2015 Retro Pay $...............4.16  4030 Dental ER................14.20 9110 Vacation Hrs Balance......6.46            

              2997 TOTAL EE BEN COST........55.39  4200 RRSP % ER................54.25 9120 Floater Hrs Balance.......8.00            

              2998 EE BEN PERCENT...........45.00  6500 Fed Tax Deduction.......136.04 9140 Stat Hrs Banked Balanc......00            

              2999 TOTAL ER BEN COST........67.71  6505 CPP Deduction............49.73 9300 Wage rate Last Pay.......13.51            

              3000 Life Ins EE...............4.45  6510 EI Deduction.............19.71 9460 Benefit Eligible............00            

              3005 AD&D EE.....................00  7505 CPP ER...................49.73 9475 Seniority Hours..........80.00            

              3010 Dep Life EE...............1.45  7510 EI ER....................27.59 9477 Worked Hours................00            

              3015 LTD EE...................12.51  7525 WCB Premiums.............12.69                                                

              3025 Health EE................25.36  7530 WCB Earnings..........1,084.96                                                                               

      Totals for Authorization Area 'XXXX-ALL' (13 Pays)                                                                               

      0010 Gross Pay............18,011.97  3015 LTD EE..................200.55 7525 WCB Premiums............170.19            

              0020 Total Deductions......5,620.55  3025 Health EE...............221.66 7530 WCB Earnings.........14,546.47            

              0030 Net Pay..............12,391.42  3030 Dental EE...............128.50 7535 WCB Excess Earnings...3,115.50            

              0070 Total Deposit Amoun..12,391.42  3200 RRSP % EE...............405.07 8000 Fed Tax Earnings.....18,771.41            

              0100 Regular Hrs.............867.50  3215 RRSP Vol EE..............29.00 8005 CPP Earnings.........18,771.39            

              0131 Stat Bnk Pay Out Hrs........00  3320 Computer EE..............68.50 8007 CPP Exemption.........1,615.44            

              0145 Sick Leave Hrs..............00  3500 CSB.....................386.00 8010 EI Earnings..........18,771.39            

              0150 Sick Leave No Pay Hrs.....6.50  4000 Life Ins ER...............5.04 8011 EI Hours..............1,019.00            

              0155 Vacation Pay Tkn Hrs (..142.50  4005 AD&D ER..................10.55 9005 Vacation Accruals Hrs....63.97            

              0215 Prev PP Hrs...............9.00  4010 Dep Life ER...............1.45 9100 Sick Leave Hrs Balance..950.00            

              1000 Reg $................15,567.55  4015 LTD ER......................00 9105 Sick Lv Hrs Earned......950.00            

              1031 Stat Bnk Pay Out $..........00  4020 WI ER....................68.40 9110 Vacation Hrs Balance.....63.97            

              1050 Sick Leave No Pay $......87.17  4025 Health ER...............528.14 9120 Floater Hrs Balance......24.00            

              1055 Vacation Pay Tkn $ (..1,860.13  4030 Dental ER...............165.90 9140 Stat Hrs Banked Balanc......00            

              1065 On Call $...............100.00  4200 RRSP % ER...............753.83 9200 RE Burden Cost........2,912.81            

              1215 Prev PP $...............123.81  5005 Home Auto $..............90.11 9210 RE PR Clearing (Burden..959.18            

              2015 Retro Pay $..............10.48  5035 Social Club EE............2.00 9300 Wage rate Last Pay......225.88            

              2035 Car Allowance $.........350.00  6500 Fed Tax Deduction.....3,061.43 9460 Benefit Eligible............00            

              2997 TOTAL EE BEN COST.......639.51  6501 Add'l Tax Deduction......10.00 9475 Seniority Hours.......1,019.00            

              2998 EE BEN PERCENT..........589.00  6505 CPP Deduction...........667.65 9477 Worked Hours............876.50            

              2999 TOTAL ER BEN COST.......779.48  6510 EI Deduction............261.29 9640 Comp Grant TB Goal..........00            

              3000 Life Ins EE..............74.30  7025 Loan TB $...................55 9645 Comp Loan Goal..........710.48            

              3005 AD&D EE.....................00  7505 CPP ER..................667.65 9650 Comp Grant Pays to YE...396.00            

              3010 Dep Life EE..............14.50  7510 EI ER...................351.70                                                

      /code

        • Template Stumper!
          Grant Perkins

          Hi Grant,

           

          Has the columnar format been slightly corrupted in the cleaning or posting? It doesn't present with equal spaces between columns thus gving a problem for MCR.

           

          I assume the real report is OK but before going too far thought I would check with you.

           

          What fixes the format?

           

          Grant Perkins

          • Template Stumper!
            Data Kruncher

            Hi Grant, how's things eh?

             

            Provided that your employee data is limited to a single page, I think that I have a solution.

             

            I first set the MCR to 3 columns, starting a column 8, 37 chars wide. I set the detail template, trapping using |||| for the pay code number. I painted one big field named A.

             

            I then built an append template using "totals for" as the trap, and turned the multicolumn design mode off for the template. This became field B.

             

            Next, a page header traps the employee info fields.

             

            Now back to the MCR definition, set the boundaries to start at the top of the page, and to end with the append template.

             

            Finally, in the table, filter to display all records where B="".

             

            Not sure how it'll perform with a multipage sample though, but I suspect that it'll be just fine.

             

            Does that work for you?

             

            Kruncher

              • Template Stumper!
                Grant _

                Hey Kruncher, things are going pretty good.  The data in this report does span multiple pages with the pages headers and footers landing conveniently in the middle of the data.  To answer the first question, the MCR is a little off as this file is a PDF file converted so it is out by a space.  The perfect storm...  Any help would be appreciated, I feel I am getting rusty!

                 

                Thanks

                  • Template Stumper!
                    Olly Bond

                    Hello Grant,

                     

                    If your columns from the PDF won't fit neatly into an MCR, then you can use a trick to solve this.

                     

                    Define the columns as starting in position 250 (or anywhere beyond the right hand edge of your data) and having width 1.

                     

                    Then use a sort order on Page() and Line() to get the data in the right order, and Mod(Recno()) identify the column, then lsplit(intrim()) the data on this column value.

                     

                    I'm not in front of Monarch to check but I think v10 introduces the Column() function to make this process easier.

                     

                    Best wishes,

                     

                    Olly