0 Replies Latest reply: Feb 8, 2010 10:47 AM by tfarr _ RSS

    Multiple Column Detail Section - New User [V10.01 Pro]

    tfarr _

      Hello! 

       

      I've been digging around the forums a bit, but I apologize if there is a thread answering my question and I missed it.  I am new to Monarch, but am impressed with the software so far.  In the past, I've written my own VBA scripts to extract report information, but lately a couple of the reports were complex enough that it merited investigating Monarch's capabilities.  However, I must warn you - this is my first exposure to the software, and although I read the entire manual (excellent weekend reading by the way sigh), I am still limited in my conceptual understanding of the program.  I think I may have picked a tough report (the report is a PDF) though to begin learning on. 

       

      Here is a sample section of the report I am working on, with names/details changed of course:

       

      Company Name                                                           Payroll Register                           Report # 0348.01                    ProBusiness       

                 1 Page          Date 11/01/2007  Paygroup: 1    Run(s):574                                           Check Date 11/01/2007    Period 10/16/2007 - 10/31/2007                                                                               

      Chk Date   Check #   

      Emp #  Div   Dept   Exmp                                                                                Gross      Chk Type  

      S/H   Rate    Cyc M  Grp  <------ Earnings -


      >  <--


      Deductions -
      >  <--


      TAXES  -


      > Deduct     Deposit   

             SSN       Hire   Dist    Code   Hours  Amount       Code    Amount      Code    Amount      Code    Amount                                      Net Amt    Chk Amt

      Employee Name             REGULAR   78.67   4886.92  HLTHCARE     33.33  PTX LIFE     20.37  AD&D          6.79  FIT     522.87 UT-SIT     252.81  11/15/07  DD457847   

      8888   1     55555   10/10BEREAVE    8.00    496.95  125C MED    642.00  DEP LIFE     12.00  GRP LGL       6.74  FICA      0.00 ERDY TER    58.83    5974.97  DIRECT    

      H     62.1192   S M       FLEXRSRC           548.35  LTD POST      6.57  4K LOAN2    260.61  401K        215.35  FMED     76.45                      2157.47   3817.50  

      555-55-5555 02/13/99 PCD  50KLIFE             42.75  GTL OFF      42.75                                                                              3817.50      0.00                                                                               

      Employee Name 2           REGULAR   86.67   3350.58  PTX LIFE      3.63  AD&D          1.21  125C MED    696.00  FIT     254.25 MI-SIT      74.13  11/15/07  DD327014   

      88888  1     34759   12/12FLEXRSRC           542.93  LTD POST      4.09  GTL OFF       5.00  DOM OFF     211.48  FICA    211.37                      4109.99  DIRECT    

      H     38.6591   S S       50KLIFE              5.00                                                              FMED     49.43                      1510.59   2599.40  

      111-11-1111 10/15/94 HDQ  DOMPRTNR           211.48                                                                                2599.40      0.00                                                                               

      /code

       

       

      I really could have two levels of detail from this report:

      Each Employee is the Detail with: Net Pay, Net Taxes, etc...

      Each Earnings, Deduction, and Tax Code is a detail item

      /LIST

       

      With the first option, I would really only need a header template and the detail template getting info from the left-most and right-most sections of each record.  With the second option, those left and right areas would become append templates - duplicating their information for each individual earnings or deduction code.  However, I'm a little confused at how I can get the detail when it would really be two fields (the code desc and the amount) that repeat through the multiple column section in the middle.  Especially since the column widths change from Earnings to Deductions to Taxes.  And would it be possible to also append each code with which section it came from - Earnings/Deductions/Taxes. 

       

      A couple reminders:

      The Earnings, Deductions, Taxes headers are on every page just once, so I am not sure how that would work with my question about treating them as an appendage to each code. 

      There are about ten employees per page.  Headers don't repeat for each new employee payment as you can see from the second employee I showed in the example.

      Every Employee has a different number of earnings and deductions and taxes.  And sometimes, the codes are very different.  This is why, if I want the detail this deep, I need to treat each deduction/earnings entry as the detail level. 

      Also - there are some employees that have so many earnings/deduction codes that their "section" is a bit longer than others.  Usually only by one or two lines.  For the most part, each section is 4 lines with 2 lines spaced in between each employee.  When an employee does have that many deductions it will go onto a 5th or 6th line (still staying in the same column region) but the append level (employee info) doesn't change and is still just the four lines.  And you will still have two blank lines before the next employee.

      /LIST

       

      Anyway, that should get someone started.  I apologize if I have left out any important information.  Please let me know what info you need and I will get it to you as soon as possible.  Thank you in advance for your help!

       

      Cheers!

        • Multiple Column Detail Section - New User [V10.01 Pro]
          Olly Bond

          Hello tfarr and welcome,

           

          If it's a PDF report, chances are the columns won't line up perfectly as you scroll down the pages. You may need to use a trick with a multi column region to get a fix - define the columns as being 1 character wide, and starting in the right hand margin, then use the lsplit function on the Column() system variable that's available in v10.

           

          Got to dash out now, sorry, but I can happily build you a model for this tomorrow night if you haven't cracked it yourself by then. If you search the forums/newsletter archive for "blob" I think you might find a relevant post that goes into it in more detail...

           

          Best wishes,

           

          Olly

            • Multiple Column Detail Section - New User [V10.01 Pro]
              tfarr _

              Thanks Olly!  I appreciate your quick response.

               

              The columns actually seem to be rather uniform all the way down and don't vary as far as I can tell.  Other than some being left or right aligned, they all seem to be exactly within predefined columns. 

               

              I will search for the 'blob' you mentioned and see where it leads me. 

               

              Currently I have the Page Header Template and Append Templates (far-left and far-right employee info / check info) done.  Just trying to figure out the code-amount detail section and then I will need to conceptually understand how I link detail templates to append templates and such.  I guess I am still trying to figure out how it will know that the detail section applies to the append section which repeats with the exact same frequency.  Not sure if I am making sense to you on how I explained that. 

               

              Thanks again though.

                • Multiple Column Detail Section - New User [V10.01 Pro]
                  Data Kruncher

                  Hi tfarr,

                   

                  You new folks really know how to dive into the deep end right away lately.

                   

                  I've got a working solution for your challenge, but I suspect that someone will come around with a more efficient/elegant solution before long. Nonetheless, mine may work for you, so here goes.

                   

                  I ended up breaking this down into more manageable pieces, so that means that I've got no less than 5 model/project combinations, and I didn't even tackle the last section with the check amount.

                   

                  My divide and conquer works like this:

                   

                  Model #1 to extract the basic employee information (columns 1 to 26). A conventional detail template, 4 lines. Trapped on the word REGULAR, assuming (I know, I know) that everyone gets at least regular pay every time. Find a better way if necessary. Export this table window to an Access db to its own table, EmployeeInfo.

                  Model #2 picks up the earnings info, trapped on the decimal point in the amount field. Append the employee name. Build an Earnings Amounts summary using the employee name as a key field (down), the Earnings Title (across) and use the hours and amounts as measure fields. Set this summary to show SUM(EarningsAmount), and duplicate the summary as Earnings Hours, showing that field as the measure. Build a project export to add each summary as its own table in the database (add to file, overwrite the table).

                  Model #3 handles the deductions, and uses an MCR template to get the detail data. Build a summary using the employee name as a down key, and the deduction code as an across key. Project export to a Deductions table in the database.

                  Model #4 picks up the taxes with another MCR template. I set it up with 2 columns, starting in column 112, 17 characters wide. Set your Monarch options to automatically trim leading spaces (Options -> Input). Build a Taxes summary, again with employee name as a down key, TaxCode across, and TaxAmount as a measure. Project export this to a Taxes table in the database.

                  Model #5 brings it all together. Connect to your database (Open database), and open the EmployeeInfo table. Now add external lookups to the same database for each of the earnings amounts, earnings hours, deductions and taxes tables individually, using the employee name as the common key field.

                  /LIST

                  Whew!

                   

                  In the end, you get two records with every field amount and appropriate field names.

                   

                  You can then add one more project to pick up the final check details in the same manner, and tie it all together with a batch file to make it easier to produce the end result.

                   

                  HTH,

                  Kruncher

                    • Multiple Column Detail Section - New User [V10.01 Pro]
                      tfarr _

                      First of all, thank you so much for your help and advice.  I truly appreciate the time you are taking to help someone you don't know. 

                       

                      I have a couple of questions and clarifications.  We'll start with some clarifying points:

                       

                      Kruncher: On using your model, I think it should work just fine.  However, I think the end result might not be exactly what I am looking for (when is it ever, right?).  My point is that I am going to run this model on dozens to hundreds of reports and then aggregate the data.  My goal is to get a single record for each and every earning/deduction/tax transaction for each employee over several months.  I understand that this will cause mass data redundancy as I will be repeating many append level data items which in a database setting would be blasphemous.  And if I can't get what I am trying to get, that may be the route I go anyway - creating a database with a few tables such as employee, earnings, deductions, taxes, etc...

                      Most people actually don't get REGULAR pay every time (well - not most - but enough that it won't work).  But I think I already found a different trap that will work just fine.

                      I've been reading up on this "Blob" idea, and think that if I can't get an elegant MCR solution, that is the route I would need to go in order to get a full view of the detail.  I may come back and ask more later, but I think there is enough in the archives to get me started.

                      /LIST

                       

                      Questions:

                       

                      MCR Issues:  This is driving me nuts!  Why can't I (or if I can, how can I) have a Multi Column Region defined inbetween two append templates horizontally.  For example:  Right now I have an append template capturing the employee information on the left of the report I showed you and a 'Check Information' append template getting the information on the far right.  I have successfully grabbed a lot of the information in the middle using MCRs - however, when the MCR is active, it is negating my append regions?  I even have it so that the MCR will stop and start correctly and adjust if there are 4 or 5 or 6 levels of information.  But it always kills my append templates on the sides.  Perhaps this is the reason that you chose other solutions and I am missing something conceptually about how it works. 

                      I am still a bit new to the UDF available in Monarch.  Can I only define them when in the Table view by using fields being pulled in from the report?  If so, could I hide fields being pulled in and only show the calculated fields which are built off the mis-formatted ones?  Yell at me if you don't understand my question - it may be poorly worded. 

                      /LIST

                       

                      Again, thanks so much for your help.  It is very much appreciated!

                        • Multiple Column Detail Section - New User [V10.01 Pro]
                          Data Kruncher

                          I'd feared that Olly and I had scared you off! Glad to see that you're up to the challenge!

                           

                          Wow, lots to comment on here. To your points:

                           

                          You've got the best possible approach in mind, and I'd envision just that sort of approach when I tackled it initially, but most of the forum members don't want to go in that direction, so that shaped my response.

                          Again, I feared as much, about "regular" not appearing, well, regularly. That's why I was sure to point it out. Glad that you've found an alternate.

                          The best approach to learn how Monarch reacts is to invest some time playing and experimenting. I know that it can be tough to get that time, but it does pay off.

                          /LIST

                          Questions:

                           

                          In the interest of time, I'll ask other members for input here please.

                          Yes, UDFs must be created in the Table, but the inputs need not be limited to fields captured from the report. Any data that's in the table will do as an input, regardless of where it came from or what you've already done with it. And yes, by all means, hide the temporary fields that exist to make final calculations easier/possible. Use the Field List to quickly hide/show a number of fields.

                          /LIST

                          Must run, so that's all for now,

                          Kruncher

                            • Multiple Column Detail Section - New User [V10.01 Pro]
                              Olly Bond

                              Hello tfarr,

                               

                              You're on the right track - to echo Kruncher - and presuming you've got Monarch Pro, there's no reason you won't be able to get the data to give you the answers you need.

                               

                              From your query about MCRs in between Appends - it sounds to me that you're likely to need to start to think about a two-pass model. Extract that area of the report trapping as a detail template, using MCR, and export to an Access format (MDB). Then call on that data again when you come back and trap your real detail.

                               

                              MCR is turned on by one template and turned off by another - in general it only makes sense for MCR to be used as the detail (there are useful exceptions). Imagine you had n columns in an append, but weren't using MCR for the detail - you'd have no way to tell Monarch which column from the append to use, as every detail record would have a column value of 1.

                               

                              HTH,

                               

                              Olly

                    • Multiple Column Detail Section - New User [V10.01 Pro]
                      Nick Osdale-Popa

                      tfarr - on a side note, you may want to download the latest 10.5 patch. There is a glitch with 10.01.

                        • Multiple Column Detail Section - New User [V10.01 Pro]
                          tfarr _

                          Hello all!  Thanks again for all of your help. 

                           

                          I am going to go ahead and download the 10.5 patch and see what's new.  So I appreciate the heads up.

                           

                          Regarding my MCR and Append question:[/B]

                          My point was not that I was trying to use an MCR inside of an append template.  Rather I wanted to use it in the detail template, which had two append templates as bookends (on the left and the right).  Like this:

                           

                          [FONT="Tahoma"][SIZE="2"]- - - - - - - -  H E A D E R  .  T E M P L A T E   .  - - - - - - - - -

                           

                          Employee Info - - - - - - - - - Earnings/Deduction Info - - - - - - - - - - Check Info

                          APPEND TEMPLATE - - - - - - - DETAIL TEMPLATE - - - - - - - APPEND TEMPLATE

                          - - - - - - - - - - - - - - - - - - {MCR Area within Detail} - - - - - - - - - - - - - - - - - /SIZE[/FONT]

                           

                           

                          The problem is that whenever I try to have the MCR area between the two separate append templates (one on the left and one on the right), it negates my append templates  - they just disappear.

                           

                          I completely understand Olly's point about not having a MCR section within an Append Template - but that is not what I am trying to do.  I guess if 10.5 doesn't solve anything and I can't figure this out, I may just end up going the multiple model extraction to database with an common index followed by merging the tables....  Oh joy 

                           

                          I really do appreciate everyone's help though.  If you have any ideas, let me know! 

                           

                          On a side note:  Anyone know anything about ACL vs. Monarch for data extraction / data mapping like this?  Someone asked me why I wasn't using ACL for this.  And I use ACL a lot[/I] for data analysis and trending, but as far as I know, it can't do anything too elegant as far as data extraction/mining.  Am I off?

                            • Multiple Column Detail Section - New User [V10.01 Pro]
                              Olly Bond

                              Hello tfarr,

                               

                              Apologies for misunderstanding. If you have two Append fields on the same line you only need one template for these.

                               

                              I'd suggest trapping that, then on the same line, make a trap that selects your detail as one big field, then define the columns over in the right hand margin and use lsplit(column()).

                               

                              If you would like me to have a bash at this for you, please send the report to olly@greenbar.info[/email] and I'll do it over the weekend.

                               

                              Best wishes,

                               

                              Olly

                                • Multiple Column Detail Section - New User [V10.01 Pro]
                                  Grant Perkins

                                  FWIW there have, historically, been a few interesting situations dealing with append type data lines/fields that sit within (or cross a vertical boundary into) the MCR region.

                                   

                                  I have not thought to check V10.5 but in earlier versions the logic was tricky. If you have a column header in the MCR and there are NO INTRUSION FIELD strings from the left of the left boundary of the MCR region then a column heading set as an append would work fine - but not if text intruded onto the MCR on a 'space' line.

                                   

                                  The importance,  or otherwise, of that will depend to some extent upon whether the append is a variable or a fixed value.

                                   

                                  "Voodoo" Monarch processing with with an offset MCR area might pay dividends?

                                   

                                   

                                   

                                  Grant

                                  • Multiple Column Detail Section - New User [V10.01 Pro]
                                    tfarr _

                                    Hey there!  I think we are getting closer to understanding the issue at hand.  So this is good...

                                     

                                    Grant Perkins[/B]

                                    First of all, Grant - I think you are (among others) a well-known Monarch demi-god.... I just want to remind everyone that I am a mere mortal and that when trying to talk to me, we are going to need to dumb it down a notch.    In other words, I'm not familiar with Voodoo, Guru Traps, Blobs, and other treats enjoyed by the Pantheon of Monarch.     I welcome all tips, but I may not understand certain topics or ideas and may require some additional coaching. 

                                     

                                    Olly[/B]

                                    Olly - I originally had just one Append Template for both of those areas.  But then when I had the MCR idea, I noticed that I could "Begin" and "End" an MCR region based on other Templates.  So I thought that if I split them up, I could 'sandwhich' the MCR inbetween and it wouldn't cancel out my Appends like it was when I was having the MCR begin at the top of the page and end at the bottom of the page.  So I get your point. 

                                     

                                    Then I realized (I still don't understand why though), that you can only break up the MCR on Vertical Boundaries, not Horizontal.  So then I thought of a new idea which was to have a record spacer Append Template that wouldn't have any detail in it, but just be a vertical boundary marker I could use for the MCR.  It worked perfectly - almost - okay, well not quite.  It would get all of the detail out of the inner columns (from one section at a time - Deductions or Taxes) and even adjust for those records which had 5, 6, 7 +  rows correctly.  And it would even stop between record lines.  It looked perfect.  But the Append templates still disappeared. 

                                     

                                    Kruncher[/B]

                                    So I guess my question now is for Kruncher, who originally had a model that used an MCR with an Append Template (second post on this thread).  How did you have an MCR Detail Template and still Append into each line even just one field such as the Employee Name or Employee Number?  It seems like anytime I try to have an MCR on the same horizontal line as an Append Template (even though the MC-Region isn't touching the Append Area), it cancels my Append. 

                                     

                                    Suggestions?

                                     

                                    Blob Limitations[/B]

                                    Olly - I still am looking into the Blob technique... This is my concern:  If I do that, won't I end up with one /Irecord for each check? - and a bunch of fields in that one record for each earnings, deduction, tax, etc...  My goal is to get one record for each actual "transaction", meaning each earnings code or deduction.  I think I could even get all of the information into one record without using the Blob too, since the data is so uniform.  So, could I use the Blob to actually create multiple detail lines, not just fields?

                                     

                                    Let me know what you guys/gals think.  Again - it is all very much appreciated!

                                      • Multiple Column Detail Section - New User [V10.01 Pro]
                                        Data Kruncher

                                        If you can send me a Private Message with your email address, I'll forward a few Portable Report Files. Monarch creates PRF files to combine both your sample report with the specific model that I'd built into a single file for easy distribution.

                                         

                                        Then you'll be able to see, and interact with, what I'd done.

                                        • Multiple Column Detail Section - New User [V10.01 Pro]
                                          Grant Perkins

                                          Hey there!  I think we are getting closer to understanding the issue at hand.  So this is good...

                                           

                                          Grant Perkins[/B]

                                          First of all, Grant - I think you are (among others) a well-known Monarch demi-god.... /quote

                                           

                                          I suspect that is going a little over the top.

                                           

                                          Actually a lot over the top - I had a lucky streak a few years back and  .... well the rest is history. 

                                           

                                          Anyway, who would want to be a demi-god? Much better a full God or nothing![/SIZE]:D

                                           

                                          I just want to remind everyone that I am a mere mortal and that when trying to talk to me, we are going to need to dumb it down a notch.    In other words, I'm not familiar with Voodoo, Guru Traps, Blobs, and other treats enjoyed by the Pantheon of Monarch.     I welcome all tips, but I may not understand certain topics or ideas and may require some additional coaching. 

                                           

                                          /quote

                                           

                                          Fear not, the path to enlightenment is quite short, after initiation. :eek:

                                           

                                          We understand the challenges of getting up to speed. I should have made it clear that my final sentence in the previous post was mostly aimed at Olly whose neat method of dealing with specific types of MCR challenges may offer a useful path to investigate.

                                           

                                          When you see the term Voodoo Monarch it merely means a way of doing things that is not obvious and intuitive  - until you have seen it and  grasped it. After which it becomes a very powerful tool for you and an even more powerful idea generator when you need one. It's a 'Wow I never thought of trying that' conceptual thing for the most part.

                                           

                                          A Guru trap is but one example of some relatively Voodoo application of Monarch's functionality. Olly's Blob is too - in both cases use here is more as a search term to find the posts where the ideas are discussed applied to different requirements.

                                           

                                          If you seek an external Forum search engine try [URL="http://www.google.com/cse/home?cx=017979669301925061066:c4v8ksw-llg"]here[/URL].

                                           

                                          Right, back to your challenge - rather a severe one so early in a Monarch career!

                                           

                                           

                                          Grant

                                            • Multiple Column Detail Section - New User [V10.01 Pro]
                                              Data Kruncher

                                              Regarding Grant's mention of the "Guru trap":[URL is no longer valid]

                                                • Multiple Column Detail Section - New User [V10.01 Pro]
                                                  tfarr _

                                                  Just a response to a couple of invitations:

                                                   

                                                  Unfortunately, I wouldn't be able to send an actual sample of the report since it is a Payroll file and contains rather sensitive information for a moderate size company.  In my example (in the first post of this thread), I actually spent time editing all the information but trying to make sure it followed the exact same pattern of the real data. 

                                                   

                                                  So - unfortunately, I can't really send more than a two page example to anyone.  I could though spend some time editing a two page example and then we could use a model going forward. 

                                                   

                                                  Ideally though - I would love to figure this out and not just use a model made by someone else.  Although I guess I could if I really took the time to learn the model.  I just want to learn /Ithis - it's that whole teach a man to fish vs. give a fish thing...

                                                   

                                                  Although -  I can't thank you all enough for the advice given so far!  I love that there are smart people out there willing to take their time to freely provide advice to the rather mindless public (me). 

                                                   

                                                  Cheers,

                                                    • Multiple Column Detail Section - New User [V10.01 Pro]
                                                      Data Kruncher

                                                      You'll get there!

                                                       

                                                      As you're discovering, learning the mechanics of Monarch is just the first phase of the journey. It's during the next phase when things get interesting: "What happens when..." , and "How would I ... ". That sort of thing.

                                                       

                                                      Patience, grasshopper... :D;)

                                                       

                                                      Hey, if it makes you feel any better, we [B]all[/B][/I] went through this!

                                                       

                                                      Heck, as was pointed out to me earlier today, I'm still going through it. :o

                                                        • Multiple Column Detail Section - New User [V10.01 Pro]
                                                          Grant Perkins

                                                          You'll get there!

                                                           

                                                          As you're discovering, learning the mechanics of Monarch is just the first phase of the journey. It's during the next phase when things get interesting: "What happens when..." , and "How would I ... ". That sort of thing.

                                                           

                                                          Patience, grasshopper... :D;)

                                                           

                                                          Hey, if it makes you feel any better, we [B]all[/B][/I] went through this!

                                                           

                                                          Heck, as was pointed out to me earlier today, I'm still going through it. :o[/quote]

                                                           

                                                          Like most things in life the learning curve actually seems to be cyclical.

                                                           

                                                           

                                                          Grant

                                                        • Multiple Column Detail Section - New User [V10.01 Pro]
                                                          Grant Perkins

                                                           

                                                          Ideally though - I would love to figure this out and not just use a model made by someone else. Although I guess I could if I really took the time to learn the model. I just want to learn /Ithis - it's that whole teach a man to fish vs. give a fish thing...

                                                           

                                                          /quote

                                                           

                                                          FWIW my personal 'Eureka' moment came when sitting down for a day or two working through a CD of presentations from a conference that I had not been able to attend.

                                                           

                                                          The content was, of course, noting to do with any analysis I was doing. So to me it was an acedemic exercise with no preconceptions (and in fact little feel for what was being attempted).

                                                           

                                                          Up to that point I could model and report and run an extraction and had managed to work through a few tricky data conversion projects. I thought I knew a lot.

                                                           

                                                          A couple of the presentations on the disk just seemed totally unintelligible. Then I found one that was sort of related to the others that I understood - and suddenly the lights came on and the deeper possibilities if what was possible (even back then) started to strike home.

                                                           

                                                          Actually I still don't really feel comfortable with the most off the wall of the examples  - a truly remarkable piece of out of the box thinking - but at least I understand where it was coming from and where it went!

                                                           

                                                          So my experience leads me to suggest that working with examples provided by others is, in the case of Monarch, more beneficial than you might think and certainly NOT a bar to learning. Indeed the essence of successful working with a tool like Monarch, IMHO, is in large part a desire to experiment and apply the tool set as you see and understand its capabilities. The greater satisfaction comes from being somewhat imiginative in the application of the tools provided rather than a primarily prescriptive approach. It probably would not work as well any other way.

                                                           

                                                          Ideas don't always quite work out - but even the failures are useful for speeding up assessment of future projects. And it doesn't take long to try an idea.

                                                           

                                                          HTH.

                                                           

                                                           

                                                           

                                                          Grant