8 Replies Latest reply: May 15, 2014 10:13 AM by elginreigner _ RSS

    Complicated Reports - Trapping & Models

    deanero _

      Hi!

       

      I'm a new user to Monarch, but I have figured out my way around basic reports.

       

      Now I have a complicated payroll report, wherein data is all over the place.

       

      Rows:  The number of lines of data is variable, depending on how many items the individual has.  Also, each individual has QTD and YTD info, which uses the same exact Description Names as the Current period (i.e. "1 Earnings" is used for Current Period, QTD and YTD...I just need the Current).

       

      Columns:  The information I need doesn't line up column wise.  All the pieces of info I need line up all over the place, and they overlap column-wise with other data (i.e. on one line, I'll have an amount, and on the next line will be a an Item Name).

       

      I'm thinking I just may need to create different models to capture "similarly structured data".  In my report, I count five different models I will have to create.

       

      Is there a simpler way to do this?

       

      Is this making any sense?

        • Complicated Reports - Trapping & Models
          Data Kruncher

          Hello, and welcome to the forum.

           

          This will be tricky to assist with without a sample, with names and any other important data changed (to x's or 9's for example) for privacy.

           

          Paste your sample between a [CODE] tag and a /CODE tag[/noparse].

           

          That said, you might get good results quickly by using the floating trap option for your template.

            • Complicated Reports - Trapping & Models
              deanero _

              hours/earns       pc   description             hours          earnings        pc   description             hours          earnings

                 current        1    regular              1,056.35         13,642.07        3    vacation                                    .01-

                                4    sick                     8.00            126.00        5    overtime                43.12            807.25

                                9r   reg bonus                                195.00             total                1,107.47         14,770.31

              pre-tax items

                 current             401k                                     144.89-            hlth                                     693.56-

                                     vis                                        7.73-            total                                    846.18-

              ************************************************************************************************************************************

              hours/earns       pc   description             hours          earnings        pc   description             hours          earnings

                 q-to-d         1    regular              3,036.79         39,283.91        3    vacation                48.00            628.63

                                4    sick                     8.00            126.00        5    overtime                73.69          1,309.77

                                9r   reg bonus                                390.00             total                3,166.48         41,738.31

                 y-to-d         1    regular             16,448.40        213,202.45        2    holiday                 32.00            472.96

                                3    vacation               398.84          5,961.22        4    sick                   120.00          1,513.64

                                5    overtime               254.15          5,009.34        5h   holiday ot              16.00            358.50

                                7    other                    2.00             22.50        9r   reg bonus                              2,600.00

                                                                                              total               17,271.39        229,140.61

                 flx-1          1    regular              3,036.79         39,283.91        3    vacation                48.00            628.63

                                4    sick                     8.00            126.00        5    overtime                73.69          1,309.77

                                9r   reg bonus                                390.00             total                3,166.48         41,738.31

              ************************************************************************************************************************************

              deduction to net

                 current        clife              .39    life              6.91    ltd               1.45    slife              .52

                                401l1            17.94                                                        total            27.21

                 qtd            clife             1.17    life             20.73    ltd               3.95    slife             1.56

                                401l1            53.82                                                        total            81.23

                 ytd            clife             2.73    life            106.84    lostc            25.00    ltd              21.99

                                slife             6.09    401l1           287.04                              total           449.69

                 ftd1           clife             1.17    life             20.73    ltd               3.95    slife             1.56

                                401l1            53.82                                                        total            81.23

              pre-tax items

                 qtd            401k            379.91-   hladj            93.07-   hlth          2,041.77-   vis              23.19-

                                                                                              total         2,537.94-

                 ytd            401k          2,189.97-   hladj           477.88-   hlth         10,879.27-   vis             122.63-

                                                                                              total        13,669.75-

                 ftd1           401k            379.91-   hladj            93.07-   hlth          2,041.77-   vis              23.19-

                                                                                              total         2,537.94-

              memo deduction

                 current        rihrs         1,099.47

                 qtd            rihrs         3,158.48

                 ytd            rihrs        17,125.41

                 ftd1           rihrs         3,158.48

              ded balances

                                hladj           175.00-

              ************************************************************************************************************************************

              gross to net           wages        socsec       federal        state          local         sdi/uc         deduction          net

                                                  medicr

              cur     grpay      13924.13        590.89       1281.12       524.64 tot                    167.09 tot         27.21     11129.17

                       grcomp     14770.31        204.01                      10.43 ma                     167.09 ri

                                                                             514.21 ri

              qtd     grpay      39200.37       1662.37       3565.58      1476.95 tot                    470.41 tot         81.23     31369.92

                       grcomp     41738.31        573.91                      30.77 ma                     470.41 ri

                                                                            1446.18 ri

              ytd     grpay     215470.86       9141.76      19604.84      8115.56 tot                   2585.74 tot        449.69    172417.21

              current tax recap                 2      void/manual checks included

                                                                              

                                                            • liabilities  ************************

              form 8109 federal deposit        type   jurisdiction           taxable wages    limit   wages to limit    rate          amount

              employee ssec          590.89

              employee mdcr          204.01

              employer ssec          872.28                                      14,069.02   110100        14,069.02   6.20 %         872.28 ssec

              employer mdcr          204.00                                      14,069.02    unlim        14,069.02   1.45 %         204.00 mdcr

              employee fit         1,281.12

              total deposit        3,152.30                                                                            7.65 %       1,076.28

                                               futa   standard rate              14,069.02     7000         8,481.29    .60 %          50.89

                                               sui    ri-rhode island            13,924.13    19600        13,924.13   3.20 %         445.57

                                               total                             13,924.13                                            445.57

                                                                              

                                              • state/local taxes withheld  ****************

                                                                             taxable wages    limit   wages to limit    rate          amount

                                               sit    ct-connecticut              1,280.90                                               .00

                                               sit    ma-massachusetts            1,378.22                                             10.43

                                               sit    ri-rhode island            13,924.13                                            514.21

                                               total                             16,583.25                                            524.64

                                                                              

                                                  • sdi/uc taxes withheld  *******************

                                                                             taxable wages    limit   wages to limit                  amount

                                               di     ri-rhode island            13,924.13    60000        13,924.13                  167.09

                                               total                                                                                167.09

              federal 401-k deferral limit                                                    17000

              federal 401-k catch-up deferral limit                                            5500

              employee census    active emps     38                         net pay analysis    # checks        7   check total     1,971.81

                                 inactive emps    1                                             # dep adv      26   efts total      9,157.37

                                 term emps        7                                             # void man      2   v/m total            .01-

                                 total emps      46                                             # emps paid    33   total net      11,129.17 *

                                                                                              total taxes     4,340.49 **

                                                                                              15,469.66 ***

              ************************************************************************************************************************************/code

               

              Here is an example of what I was describing (Just the Grand Totals, to protect the innocent!). 

               

              I am looking to extract just the CURRENT data.  for some people, the Current Data might just be one line item, and others may have three line items (under Earnings, for example).

               

              In addition, there are amounts located to the right of their code, and other amounts located underneath their code.  For example, you'll see column headers "SOCSEC" and "MEDICR" (right underneath each other), with the amounts listed underneath in respective order, whereas you see "Employer SSEC" with the amount directly to the right.

               

              Meh, it's just data vomit!

                • Complicated Reports - Trapping & Models
                  Data Kruncher

                  Oh my. :eek:

                   

                  We haven't seen the likes of this sort of thing around here for some time. I've got to be offline for the next while, but I'll have a look later tonight if I can. In the meantime, I suspect that you'll get assistance from others, perhaps shortly.

                    • Complicated Reports - Trapping & Models
                      deanero _

                      Thank you very much!

                       

                      Yeah, this company is pretty behind (technology wise), and when I came in, they were taking these reports, and keying them in to Excel by hand.

                       

                      They had a copy of Monarch, but weren't using it, so now I'm trying to at least use the tools they have and make them a bit more efficient.  At some point, I'm going to take a look at their Payroll system and see if there is a way to re-write their reports to a more usable format.

                       

                      Thanks, again!  I appreciate it!

                       

                      -d

                        • Complicated Reports - Trapping & Models
                          Grant Perkins

                          I think Kruncher may be understating the case a little - after a quick glance I can't say I can remember seeing anything quite like this before!

                           

                          However if we break it down a little and (for now) look at the 'Current' only as you require a start should be possible. (That said I'm not around for the next week after Monday and am busy this weekend.)

                           

                          Some of the report areas look like they would respond to the use of Multi-Column techniques - but others don't so a one pass solution might be more than tricky.

                           

                          Whatever you do I think you will benefit from using the Advance Field Property for setting a 'preceding string' quite extensively. Be sure to check for the uniqueness of the preceding string.

                           

                          The idea works especially well if each value has a clear and printed 'tag' in the report. If the tag moves around (e.g. can appear in different columns) it makes things a little more complicated but usually is still a viable approach. It also means a field that may or may not exist is only populated when it does exist in a record but will be created in the extract even if it only exists once (if working with as it comes data) or can be created as a field no matter whether an individual example of the report actually has any values to fill it.

                           

                          Your socsec/medicr example suggests a 2 line sample (or maybe a 3 line sample?) for the template BUT only if the report always reports 2 lines for that section.

                           

                          Moreover is all sort of depends on what you want to ID as being 'detail'. If you just want the 'cur' values from the gross-to-net section for now then detail looks fairly straightforward. For now.

                           

                          Plan B, should is be required, could be to create a one line 'field' that has a variable end line and provides a 'block' of text or variable length in terms of the number of lines. You can slice and dice the contents using calcualted fields but we can get to that later shoud we need it.

                           

                          Given the time difference between me and the West Coast I'm going to have to leave you with those snippets of ideas for now and let Kruncher come back woth something more complete later in the day.

                           

                          If all else fails the multi-step process can be partly or completely automated (usually) by scriptiing or programming so sometimes an early decision to keep things simple and seperate after a rapid assessment can be a good way to go. It's well worth spending a while to see what's possible though before makingh a final decision.

                           

                          Good luck!

                           

                           

                          Grant

                          • Complicated Reports - Trapping & Models
                            Data Kruncher

                            Surprisingly perhaps, getting the "current" data isn't too bad of an exercise.

                             

                            The key feature here is activating the multi-column region before you begin defining templates.

                             

                            I defined two columns, beginning at position 19, with a width of 60 for each column.

                             

                            Time to create templates.

                             

                            For the detail template, I set a series of B (blanks) traps between the hours and earnings (just to avoid problems with later rows), and .NN for the end of the earnings values. I painted four fields: PayrollCode, Description, Hours and Earnings.

                             

                            Next I defined an append field for the q-to-d, y-to-d, current labels. I trapped on three Blanks followed by an Alpha (BBBA) in position 1, and painted a field I named Scope.

                             

                            Finally, another append with a single Alpha trap in position 1 and painted the Category field.

                             

                            This generated the following:

                            Scope         Category     PayrollCodeDescription   Hours           Earnings

                            current       hours/earns  1          regular     1,056.35          13,642.07

                            current       hours/earns  4          sick            8.00             126.00

                            current       hours/earns  9r         reg bonus                        195.00

                            current       pre-tax items           401k                            -144.89

                            current       pre-tax items           vis                               -7.73

                            q-to-d        hours/earns  1          regular     3,036.79          39,283.91

                            q-to-d        hours/earns  4          sick            8.00             126.00

                            q-to-d        hours/earns  9r         reg bonus                        390.00

                            y-to-d        hours/earns  1          regular    16,448.40         213,202.45

                            y-to-d        hours/earns  3          vacation      398.84           5,961.22

                            y-to-d        hours/earns  5          overtime      254.15           5,009.34

                            y-to-d        hours/earns  7          other           2.00              22.50

                            flx-1         hours/earns  1          regular     3,036.79          39,283.91

                            flx-1         hours/earns  4          sick            8.00             126.00

                            flx-1         hours/earns  9r         reg bonus                        390.00

                            current       hours/earns  3          vacation                          -0.01

                            current       hours/earns  5          overtime       43.12             807.25

                            current       hours/earns             total       1,107.47          14,770.31

                            current       pre-tax items           hlth                            -693.56

                            current       pre-tax items           total                           -846.18

                            q-to-d        hours/earns  3          vacation       48.00             628.63

                            q-to-d        hours/earns  5          overtime       73.69           1,309.77

                            q-to-d        hours/earns             total       3,166.48          41,738.31

                            y-to-d        hours/earns  2          holiday        32.00             472.96

                            y-to-d        hours/earns  4          sick          120.00           1,513.64

                            y-to-d        hours/earns  5h         holiday ot     16.00             358.50

                            y-to-d        hours/earns  9r         reg bonus                      2,600.00

                            y-to-d        hours/earns             total      17,271.39         229,140.61

                            flx-1         hours/earns  3          vacation       48.00             628.63

                            flx-1         hours/earns  5          overtime       73.69           1,309.77

                            flx-1         hours/earns             total       3,166.48          41,738.31

                            /CODETo that I applied a new custom sort, Scope Ascending, Category Ascending and PayrollCode Ascending.

                             

                            Scope         Category     PayrollCodeDescription   Hours             Earnings

                            current       hours/earns             total       1,107.47            14,770.31

                            current       hours/earns  1          regular     1,056.35            13,642.07

                            current       hours/earns  3          vacation                            -0.01

                            current       hours/earns  4          sick            8.00               126.00

                            current       hours/earns  5          overtime       43.12               807.25

                            current       hours/earns  9r         reg bonus                          195.00

                            current       pre-tax items           401k                              -144.89

                            current       pre-tax items           vis                                 -7.73

                            current       pre-tax items           hlth                              -693.56

                            current       pre-tax items           total                             -846.18

                            flx-1         hours/earns             total       3,166.48            41,738.31

                            flx-1         hours/earns  1          regular     3,036.79            39,283.91

                            flx-1         hours/earns  3          vacation       48.00               628.63

                            flx-1         hours/earns  4          sick            8.00               126.00

                            flx-1         hours/earns  5          overtime       73.69             1,309.77

                            flx-1         hours/earns  9r         reg bonus                          390.00

                            q-to-d        hours/earns             total       3,166.48            41,738.31

                            q-to-d        hours/earns  1          regular     3,036.79            39,283.91

                            q-to-d        hours/earns  3          vacation       48.00               628.63

                            q-to-d        hours/earns  4          sick            8.00               126.00

                            q-to-d        hours/earns  5          overtime       73.69             1,309.77

                            q-to-d        hours/earns  9r         reg bonus                          390.00

                            y-to-d        hours/earns             total      17,271.39           229,140.61

                            y-to-d        hours/earns  1          regular    16,448.40           213,202.45

                            y-to-d        hours/earns  2          holiday        32.00               472.96

                            y-to-d        hours/earns  3          vacation      398.84             5,961.22

                            y-to-d        hours/earns  4          sick          120.00             1,513.64

                            y-to-d        hours/earns  5          overtime      254.15             5,009.34

                            y-to-d        hours/earns  5h         holiday ot     16.00               358.50

                            y-to-d        hours/earns  7          other           2.00                22.50

                            y-to-d        hours/earns  9r         reg bonus                        2,600.00

                            /CODENow it's almost trivial to filter out any unwanted records.

                             

                            We'll have to work through the other issues separately.