11 Replies Latest reply: May 15, 2014 10:01 AM by Grant Perkins RSS

    Varying Multiple Line Report

    blimbert _

      Good afternoon - I have a report whose detail records are sometimes one line long and sometimes 2 lines long.  For example, in the following image, the first 4 records are 1 line, and the rest are 2. 

       

      I'm sure I'm missing an obvious way to create the template such that I pick up every first line, and the second line if it exists (starting with the Ref.no. text).  I've tried append and footer traps, but these aren't quite what I need or I'm using them improperly.

       

      There are actually two fields on the second line.  For example, as bracketed below:

      Ref.no.:  [EXPENSES  ]

       

       

      Any help would be greatly appreciated.

      Brian

       

      0016 220000     20011804

      |                           PAUL SMITH                                                                               


      021408 ZP 2000016136 021408      001 25   T 021408 2000016136                          CAD          1,845.48

      022808 ZP 2000016178 022808      001 25   T 022808 2000016178                          CAD            922.74

      030608 ZP 2000016214 030608      001 25   T 030608 2000016214                          CAD          2,413.04

      032708 ZP 2000016314 032708      001 25   T 032708 2000016314                          CAD            922.74

      0108 EXPENSES      030608 KR 1900022061 013108      001 31     030608 2000016214                          CAD            539.56-

      Ref.no.:  0108 EXPENSES     EXPENSES

      0208 EXPENSES      030608 KR 1900022063 022908      001 31     030608 2000016214                          CAD          1,798.34-

      Ref.no.:  0208 EXPENSES     EXPENSES

      0308 EXPENSES      041008 KR 1900022312 033108      001 31     041008 2000016380                          CAD            943.42-

      Ref.no.:  0308 EXPENSES     EXPENSES

      FT/D 0218-0222     030608 KR 1900022023 022208      001 31     030608 2000016214                          CAD             75.14-

      Ref.no.:  FT/D 0218-0222    FT/D

      RUNZ/COMP ADJ      020808 KR 1900021907 020808      001 31     021408 2000016136                          CAD          1,845.48-

      Ref.no.:  RUNZ/COMP ADJ     RUNZ/COMPUTER ADJ-JAN/FEB

      RUNZ/COMPUTER 03   022808 KR 1900022000 022708      001 31     022808 2000016178                          CAD            922.74-

      Ref.no.:  RUNZ/COMPUTER 03  RUNZ/COMPUTER

      RUNZ/COMPUTER 04   032708 KR 1900022171 032608      001 31     032708 2000016314                          CAD            922.74-

      Ref.no.:  RUNZ/COMPUTER 04  RUNZ/COMPUTER

      /code

        • Varying Multiple Line Report
          Grant Perkins

          Hi Brian and welcome to the forum.

           

          Looks like your first field is the same as the first column in the detail line.

           

          The easiest way to deal with your need as outlined by the sample data is to create an APPEND template with exactly the same trap line as your detail record. Paint a field or fields on the line where you expect to find the occasional fields and then in the advanced properties for each field set the 'Start Field On' value to 'Preceding string' Ref.no.: 'on the same line.

           

          That should pick what you want AND ensure that the Append fields are reset for every new detail line found as Monarch parses the report rather than being retained for a number of detail lines as would be a more typical situation in a structured report.

           

          HTH.

           

           

          Grant Perkins

            • Varying Multiple Line Report
              Nick Osdale-Popa

              Oh, I was soooo close to providing the solution! My mistake was that I was creating a two-line append, instead of a duplicate of the detail. Once I followed your suggestion, it did fall into place.  Hope it worked out for you Brian!

                • Varying Multiple Line Report
                  Data Kruncher

                  We ought to have a proper name for this technique, wherein the append trap is [I]exactly /I[/B]the same as the detail trap.

                   

                  I vote for the tradition of naming it after its discoverer: "The Guru Trap".

                   

                  As in: "I solved my modeling challenge with a Guru Trap."

                   

                  All in favour?

                    • Varying Multiple Line Report
                      blimbert _

                      It worked, thanks gang!

                       

                      "I solved my modeling challenge with a Guru Trap"

                       

                      Brian

                        • Varying Multiple Line Report
                          Grant Perkins

                          Glad it worked for you Brian.

                           

                          I think this concept is probably the one that comes up most often as a solution on the forum, outside of coding and automation related solutions.

                           

                          I seem to remember that the combination of ideas - the overlapping trap followed by a suitable 'Start field on' option  ("after last field in the template" is another that works well) is now covered in the one of the more advanced training courses, in fact has been for a while.

                           

                          The great thing about Monarch is that you can just try stuff quickly to see if it works. If not, little time is lost. If it does work you likely have a solution or have moved closer to a solution and gained a new concept for further application in the future.

                           

                          Must add some tags to this ...

                           

                           

                           

                           

                           

                          Grant

                            • Varying Multiple Line Report
                              KEVIN KENNEDY

                              This thread saved my bacon.  I have to analyze 30 months of reports (1,511 of them) and desperately needed templates with these features. I was stumped until I read this.  Thank you, Grant, for the instruction!

                                • Varying Multiple Line Report
                                  Grant Perkins

                                  This thread saved my bacon. I have to analyze 30 months of reports (1,511 of them) and desperately needed templates with these features. I was stumped until I read this. Thank you, Grant, for the instruction![/quote]

                                   

                                  :cool:

                                   

                                  It's great that this functionality seems to answer challenges set by so many reports for so many people.

                                   

                                  Thanks for taking the time to post. It's good to get the feedback and very likely useful for helping to generate ideas for future enhancements to Monarch.

                                   

                                  Now a challenge: Can you come up with similar concepts or variations that help with your huge project? If you can I'm sure you will find a grateful audience for the outcomes here on the forum.

                                   

                                  Have fun and good luck with the task.

                                   

                                   

                                  Grant

                                   

                                   

                                   

                                  PS: If you are likely to be creating a number of similar models have you looked at the Linked Objects functionality?  Also User defined Functions. They may help to speed the process or assist with maintenance in the even of changes over time.

                                    • Varying Multiple Line Report
                                      KEVIN KENNEDY

                                      (I spoke too soon in my previous post.)

                                      Creating an APPEND template with the same trap as the DETAIL template worked for one report with varying multiple lines, but not with another.  The latter report can have 1 or 2 lines per record. If it has two lines, there is a blank line preceding the next record if the 2nd line of data appears on the left side of the report. Here's a sample of 1-line and two types of 2-line (I am trapping on RECORD I.D. and using that field and all the DATA1 fields):

                                            

                                       

                                            

                                       

                                      In the first & third instance, there's no blank line after the record. In the second instance, there IS a blank line.

                                      I repeated my successful multi-line model from another report with this one, with no use of the "Advanced" features.  It didn't work--every other single-line record was missed.  I tried a few of the "start field on" features, but they didn't work either.  What can I do to keep the model from treating every record as a 2-liner?

                                        • Varying Multiple Line Report
                                          Grant Perkins

                                          Is the third example something that should really be on one line but has wrapped in the report? (In fact, is this a fixed format report or a CSV type file?)

                                           

                                          If your smallest record has only one line then your model has also to work with the one line sample rather than 2 lines.

                                           

                                          So, no problem with the first example line working with a 1 line sample.

                                           

                                          The second example is trickier but easy enough IF the occasional second line has anything we can use as a preceding string - the word "Description" would do it for example. If not we need to find an alternative.

                                           

                                          Example 3 is a challenge that may require a different approach.

                                           

                                          In outline the options seem to be something like;

                                           

                                          1. Pre-process the report to make everything single line records and go from there ...

                                           

                                          2. Extract everything as multi-row records in a single large field and then slice and dice to get what you need using conditional processing where necessary.

                                           

                                          3. Any other suggestions that may come up!

                                           

                                          I'm not sure which way to go yet but answers to the questions above may offer guidance.

                                           

                                           

                                           

                                          Grant

                                            • Varying Multiple Line Report
                                              KEVIN KENNEDY

                                              Well, I struggled with a few options, and I'm not too familiar with pre-processing.  When a second line has data I want to trap, there are only blanks to the left of the fields.  I ran into a time crunch with this and created separate models: one for records that use one line, one for records that use two lines. Crude, but it works. When I have time, I will try again to tame this monster.

                                                • Varying Multiple Line Report
                                                  Grant Perkins

                                                  Pre-processing is not too difficult using the Monarch Utility or the stand alone MSRP.EXE program (or other string replacement options) but does demand working with an original file to be sure that the way the file is created is understood.

                                                   

                                                  If you can spot the descriptive record versus the 'wrapped' records (as they appear to be above in examples 2 and 3) then you could simply capture the fields as multi-line and use the TextLine function in calculated field to see if a second line exists.

                                                   

                                                  If it does you need a pattern of some sort that tells you whether it is a Description line or additional data.

                                                   

                                                  Slicing and dicing the data probably relies on knowing how each line is structured - not too hard but working with format samples may make things more difficult to explain.

                                                   

                                                  If you can provide a 2 or 3 page sample of actual representative data in a file I would be happy to have a look at it and send you a model as guidance.

                                                   

                                                  Send me a PM with your email address if that is an option you can use.

                                                   

                                                   

                                                  Grant