9 Replies Latest reply: May 15, 2014 10:02 AM by Nick Osdale-Popa RSS

    A detail line that's missing details

    Nick Osdale-Popa

      OK, I've struggled with this for two days, and can't quite pinpoint the result with either the trap or filters.

      Here's the report.

       

      RUN DATE 10/08/08 14:56:21                             PAYROLL MODULE                                         PAGE      1

      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX           GROSS PAYROLL REGISTER BY JOB REPORT              USER PR SECURITY LEVEL:         0

      09/05/08 TO                                     FROM PAY PERIOD ENDING 09/05/08                 USER EMP SECURITY TYPE:       all

      10/03/08                                         TO PAY PERIOD ENDING 10/03/08

       

       

      JOB: 1234, DESCRIPTION

                                                                      -


        EMPLOYER PAID -


      EMPLOYEE                    -


      HOURS -


             PAY                                                      COMP &

                                  REGULAR   OT/DT      RATE    GROSS  FRNGES  FICA 1  FICA 2   FUTA   SUTA   DBL   LIAB  REIMBUR     TOTAL

                                  -


      PHASE: 12 DESCR

      COST CODE: 12345678 DESC

       

      12345    HOWARD, MOE  10/03   16.00    0.00O    33.29   532.64  311.04   35.98    8.41   4.64  31.92  0.00   0.00     0.00    924.63

      23456    HOWARD, CURL 10/03   16.00    0.00O    33.29   532.64  311.04   35.98    8.41   0.00   0.00  0.00   0.00     0.00    888.07

                                   -


                 -


      COST CODE TOTAL             32.00                     1065.28           71.96           4.64         0.00            0.00

                                             0.00O                    622.08           16.82         31.92         0.00            1812.70

                                             0.00D

                                   ==============           ==============================================================================

      PHASE TOTAL                 32.00                     1065.28           71.96           4.64         0.00            0.00

                                             0.00O                    622.08           16.82         31.92         0.00            1812.70

                                             0.00D

      PHASE: 13 DESCR

      COST CODE: 23456789 DESC

       

      34567    FINE, LARRY  09/12   40.00    0.00O    41.61  1664.40  798.00  111.85   26.16   0.00   0.00  0.00   0.00     0.00   2600.41

      34567    FINE, LARRY  09/19    8.00    0.00O    41.61   332.88  159.60   22.37    5.23   0.00   0.00  0.00   0.00     0.00    520.08

      34567    FINE, LARRY  09/26    8.00    0.00O    41.61   332.88  159.60   22.37    5.23   0.00   0.00  0.00   0.00     0.00    520.08

      34567    FINE, LARRY  10/03   20.00    0.00O    41.61   832.20  399.00   55.92   13.08   0.00   0.00  0.00   0.00     0.00   1300.20

                                   -


                 -


      COST CODE TOTAL             76.00                     3162.36          212.51           0.00         0.00            0.00

                                             0.00O                   1516.20           49.70          0.00         0.00            4940.77

                                             0.00D

                                   ==============           ==============================================================================

      PHASE TOTAL                 76.00                     3162.36          212.51           0.00         0.00            0.00

                                             0.00O                   1516.20           49.70          0.00         0.00            4940.77

                                             0.00D

      PHASE: 14 DESCR

      COST CODE: 34567890 DESC

       

      45678    MARX, GROUCH 09/19   36.00    0.00O    33.29  1198.44  699.84   80.95   18.93  10.45  71.81  0.00   0.00     0.00   2080.42

      45678    MARX, GROUCH 09/26   40.00    0.00O    33.29  1331.60  777.60   89.95   21.04  11.61  79.79  0.00   0.00     0.00   2311.59

      45678    MARX, GROUCH 10/03   40.00    0.00O    33.29  1331.60  777.60   89.95   21.04  11.61  79.79  0.00   0.00     0.00   2311.59

      56789    MARX, GUMMO  09/05    4.00    0.00O    38.28   153.12   79.00   10.31    2.41   0.00   0.00  0.00   0.00     0.00    244.84

      56789    MARX, GUMMO  09/12    6.00    1.00O    38.28   287.10  139.90   19.33    4.52   0.00   0.00  0.00   0.00     0.00    450.85

      56789    MARX, GUMMO  09/19   16.00    4.00O    38.28   842.16  401.58   56.70   13.26   0.00   0.00  0.00   0.00     0.00   1313.70

      56789    MARX, GUMMO  09/26    4.00    0.00O    38.28   153.12   79.00   10.31    2.41   0.00   0.00  0.00   0.00     0.00    244.84

      56789    MARX, GUMMO  10/03    3.00    0.00O    38.28   114.84   59.25    7.73    1.81   0.00   0.00  0.00   0.00     0.00    183.63

                                   -


                 -


      COST CODE TOTAL            149.00                     5411.98          365.23          33.67         0.00            0.00

                                             5.00O                   3013.77           85.42        231.39         0.00            9141.46

                                             0.00D

       

      RUN DATE 10/08/08 14:56:21                             PAYROLL MODULE                                         PAGE      2

      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX           GROSS PAYROLL REGISTER BY JOB REPORT              USER PR SECURITY LEVEL:         0

      09/05/08 TO                                     FROM PAY PERIOD ENDING 09/05/08                 USER EMP SECURITY TYPE:       all

      10/03/08                                         TO PAY PERIOD ENDING 10/03/08

       

       

      JOB: 1234, DESCRIPTION

                                                                      -


        EMPLOYER PAID -


      EMPLOYEE                    -


      HOURS -


             PAY                                                      COMP &

                                  REGULAR   OT/DT      RATE    GROSS  FRNGES  FICA 1  FICA 2   FUTA   SUTA   DBL   LIAB  REIMBUR     TOTAL

                                  -


                                   ==============           ==============================================================================

      PHASE TOTAL                149.00                     5411.98          365.23          33.67         0.00            0.00

                                             5.00O                   3013.77           85.42        231.39         0.00            9141.46

                                             0.00D

      PHASE: 15 DESCR

      COST CODE: 45678901 DESC

       

      67890    MARX, ZEPPO  09/12   40.00    4.00O    38.28  1760.88  875.58  118.56   27.73   0.00   0.00  0.00   0.00     0.00   2782.75

      67890    MARX, ZEPPO  09/19   40.00    5.50O    38.28  1847.01  907.69  124.36   29.08   0.00   0.00  0.00   0.00     0.00   2908.14

      78901    MARX, HARPO  09/05   18.00    4.00O    44.35  1419.20  532.66   94.48   22.10   0.00   0.00  0.00   0.00     0.00   2068.44

                                             4.00D

      78901    MARX, HARPO  09/12   24.00    6.00O    44.35  1463.55  608.97   97.88   22.89   0.00   0.00  0.00   0.00     0.00   2193.29

      78901    MARX, HARPO  09/19   24.00    6.00O    44.35  1463.55  608.97   97.88   22.89   0.00   0.00  0.00   0.00     0.00   2193.29

      78901    MARX, HARPO  09/26   24.00    6.00O    44.35  1463.55  608.97   97.88   22.89   0.00   0.00  0.00   0.00     0.00   2193.29

      89012    MARX, CHICO  09/26   36.00    0.00O    33.29  1198.44  699.84   80.95   18.93  10.45  71.81  0.00   0.00     0.00   2080.42

      89012    MARX, CHICO  10/03   40.00    0.00O    33.29  1331.60  777.60   89.95   21.04  11.61  79.79  0.00   0.00     0.00   2311.59

      90123    HOWARD, SHEM 09/26   40.00    5.00O    41.61  1976.48  906.48  132.82   31.06   0.00   0.00  0.00   0.00     0.00   3046.84

      90123    HOWARD, SHEM 10/03   40.00   12.00O    41.61  2413.38 1058.34  162.18   37.93   0.00   0.00  0.00   0.00     0.00   3671.83

                                   -


                 -


      COST CODE TOTAL             326.00                   16337.64         1096.94          22.06         0.00            0.00

                                            48.50O                   7585.10          256.54        151.60         0.00           25449.88

                                             4.00D

                                   ==============           ==============================================================================

      PHASE TOTAL                 326.00                   16337.64         1096.94          22.06         0.00            0.00

                                            48.50O                   7585.10          256.54        151.60         0.00           25449.88

                                             4.00D

       

      JOB TOTAL                   583.00                   25977.26         1746.64          60.37         0.00            0.00

                                            53.50O                  12737.15          408.48        414.91         0.00           41344.81

                                             4.00D

       

      REPORT TOTAL                583.00                   25977.26         1746.64          60.37         0.00            0.00

                                            53.50O                  12737.15          408.48        414.91         0.00           41344.81

                                             4.00D

       

      /code

       

      From the report, I need all the details of each employee.

      Well, everything looks dandy, except for that rascally Harpo Marx.  Seems he got a little too rambunctious with tooting his horn and found himself working an extra 4 hours doubletime. (Hey, he brings in the laughs, what can I say?)

       

      Basically, I need that line trapped like the other details with just the Employee Number, Name and Date repeated from the previous line.

       

      Have at it, folks!

        • A detail line that's missing details
          Data Kruncher

          HI Nick,

           

          Yet Another Guru Trap Solution.

           

          I trapped your detail with the / in the date. A one line detail template.

           

          Then I created a one line append template with the same / trap character in the same position.

           

          On the advanced tab of the DT field, I specified a / anywhere in the previous line.

           

          For the field itself, I only painted the numbers, and set the field to be numeric.

           

          This generates some null values in the table so you'll probably want to have a calculated field to convert to zero when null, otherwise using the captured value.

           

          OK?

            • A detail line that's missing details
              Nick Osdale-Popa

              I tried that too, but that puts the "extra" line on the line preceding it.

               

              I need it as a row by itself with the employee info. with the result like this:

              MARX, HARPO   09/05   18.00    4.00 ~

              MARX, HARPO   09/05            4.00

              MARX, HARPO   09/12   24.00    6.00 ~

              MARX, HARPO   09/19   24.00    6.00 ~

              MARX, HARPO   09/26   24.00    6.00 ~

              /CODE

               

              Or am I still missing something?

                • A detail line that's missing details
                  Data Kruncher

                  Ah, that's a different animal. I was adding a DT Hours field to the previous row.

                   

                  Hmm. Off to the ol' drawing board then.

                   

                  I suspect that changes the game a bit then, and only the hours data becomes the detail and the name and date becomes an append.

                   

                  Your thoughts?

                    • A detail line that's missing details
                      Nick Osdale-Popa

                      Sounds like plan.

                       

                      I'll work on it here.. let me know if you come up with anything too.

                        • A detail line that's missing details
                          Data Kruncher

                          Computer's tied up for a bit running automated reporting. Will advise later on today if I come up with anything.

                           

                          I've used that approach on a couple of my own reports, so I think that it'll work out OK.

                            • A detail line that's missing details
                              GailStevenson _

                              I had time and messed around and came up with this, is this what you are looking for (some columns omitted)?  If so, I just used the row with Harpo's overtime and highlighted the columns with amounts for the detail.  I then pulled the rest of the information in an append template.  

                              67890     MARX, ZEPPO     09/12     40.00      4.00O      38.28

                              67890     MARX, ZEPPO     09/19     40.00      5.50O      38.28

                              78901     MARX, HARPO     09/05     18.00      4.00O      44.35

                              78901     MARX, HARPO     09/05           4.00D     

                              78901     MARX, HARPO     09/12     24.00      6.00O      44.35

                              78901     MARX, HARPO     09/19     24.00      6.00O      44.35

                              78901     MARX, HARPO     09/26     24.00      6.00O      44.35

                              89012     MARX, CHICO     09/26     36.00      0.00O      33.29

                              89012     MARX, CHICO     10/03     40.00      0.00O      33.29

                              90123     HOWARD, SHEM     09/26     40.00      5.00O      41.61

                              90123     HOWARD, SHEM     10/03     40.00     12.00O      41.61

                              /CODE

                                • A detail line that's missing details
                                  Nick Osdale-Popa

                                  Hmm... how'd you go about filtering out the totals?

                                    • A detail line that's missing details
                                      Data Kruncher

                                      OK Nick, another approach.

                                       

                                      Two models, two projects, one batch file.

                                       

                                      The first model/project captures and exports to an xls file every row with an employee number and every field on the line. Set the project export to overwrite existing files.

                                       

                                      Then the next model/project captures uses the guru trap approach to capture the employee number, name and date in the detail template, using the NNNNN trap for the employee number. Ignore the other numeric values.

                                       

                                      Next, the append template uses the same trap and captures only the DT hours. Filter out all the DT fields that are "-----" or blank ("") and you'll be left with a single record.

                                       

                                      Now add calculated fields with zero values for every field that is defined in the first model that you created, each field named exactly the same and in the same order as the layout of the first model. Export the table, appending to the file exported in the first project.

                                       

                                      I believe that should do it. Comments?