8 Replies Latest reply: May 15, 2014 10:04 AM by Olly Bond RSS

    Identify untrapped records in large reports

    Rusch Mauzy

      How can I identify untrapped records in very large reports, without having to page through all the records?

       

      Please help,

       

      Thank you,

       

      Rusch

        • Identify untrapped records in large reports
          Data Kruncher

          Unfortunately that really is the best way.

           

          Apart from that, though, it's often a good idea to use Numeric Or traps (|) instead of the regular numeric trap. Using fewer trap characters loosens the restrictions and tends to pick up additional records, but that isn't always feasible.

           

          One of the best techniques is only available if the source report contains grand totals at the end. Then you can whip up a summary to double-check that you've captured everything and if not, you can search for amounts to help close the gap, but even that can be tedious.

           

          If others have encountered or developed other techniques, I'd love to hear about them.

           

          Good luck with it.

           

          Kruncher

            • Identify untrapped records in large reports
              Olly Bond

              Hello Rusch,

               

              As Kruncher suggests, some reports from "good" ERP systems have totals and checks that can be picked up, which you can compare against summary values from the main data.

               

              When you don't have that, you can minimise the chance of your detail template missing a genuine line of data by trapping using:

               

              - floating traps (in case the data drifts left and right on the line)

              - numeric OR traps (in case data is present in one column but not another)

              - wildcard traps (the N & A characters) instead of literal traps

              - the verify function to check that the fields include all their data

               

              There is one more technique available - line counting - that can help when auditing a strange model against large datasets.

               

              Make a new model, with only one template, that traps all lines and selects one long 254 character text field called Blob (hopefully this will contain most of your meaningful data). No appends, no page headers, just get every line of the report into the table.

               

              Then use calculated fields and filters to replicate the logic of working out which line is a detail (so, if you'd trapped your detail line with an Alpha trap in the 10th column in your original model, in this Check model, you could make a filter where substr(Blob;10;1) was tested to be an alpha character.)

               

              This should give you the same row count in your table as the unfiltered table in your original model. You can use this approach to vary the filter conditions - substr(Blob;10;1) being tested to be nonblank, for example, would pick up rows where the trap character was numeric or punctuation - and would report a different row count.

               

              If you define a calculated field called IsInModelDetail that returns a 1 if the original condition is matched, and another called OughtToBeInModelDetail that returns a 1 when the looser condition is applied, a simple summary showing Page() and Line() values for all the rows where the two test fields don't match will help.

               

              The best help is a second pair of eyes to review your models before you put them into production. Not every organisation can manage this - time and money are too precious and a lot of models too straightforward to warrant the effort - but if someone is relying on the output, or if it's being run automatically, then you should persuade management to invest the extra time up front.

               

              Hope this helps,

               

              Olly

            • Identify untrapped records in large reports
              Rusch Mauzy

              Thank you.

               

              I will try out these ideas.

               

              I am a bit surprised that Monarch can not produce a subset of the total data, "showing" which records have not been chosen by the traps.

               

              I would think Monarch must know which records were chosen by the traps, and therefore, Monarch would also know which records were not.

               

              Thank you,

               

              Rusch

                • Identify untrapped records in large reports
                  Olly Bond

                  Hello Rusch,

                   

                  That's an interesting idea - that Monarch should sort the wheat from the chaff and present you with two neat piles...

                   

                  I guess you could simply copy your model, change the detail template to use NOT traps (assume v9 or above) and use that as the negative of your original data.

                   

                  Of course, a simple NOT of the detail trap may behave oddly with your appends - you might end up with some strange inadverdent guru trap effects, for example.

                   

                  Combine this idea with my suggestion of trapping every line and checking in the table, and you should have a decent handle on how reliable your model is.

                   

                  Remember, input such as XPS or PDF where space scaling may vary between a small sample used to build the model, and real life data of hundreds of pages, will require extremely careful trapping and checking. For this work, I'd suggest that four eyes are essential.

                   

                  Best wishes,

                   

                  Olly

                  • Identify untrapped records in large reports
                    Grant Perkins

                    Thank you.

                     

                    I will try out these ideas.

                     

                    I am a bit surprised that Monarch can not produce a subset of the total data, "showing" which records have not been chosen by the traps.

                     

                    I would think Monarch must know which records were chosen by the traps, and therefore, Monarch would also know which records were not.

                     

                    Thank you,

                     

                    Rusch[/quote]

                     

                    The thing is that the template you define sets the rules for the 'records' you want. The records are not an implicit part of the report that Monarch can automatically 'know' and then sit back and watch to see whether you choose all of them with your trap definitions.

                     

                    V10 may get a little closer to helping with this using the auto trap definition feature to provide a faster visual assessment of the records potentially trappable based on the selection of specific, 'typical' data lines from the report.

                     

                    More often than not anomalous selction of records leads to fields with evidently incorrect data. Once identified as a potential problem the data errors can be used to flag up bad records to provide a checking mechanism.

                     

                    HTH.

                     

                     

                    Grant

                      • Identify untrapped records in large reports
                        Olly Bond

                        Hi Grant,

                         

                        A word of caution about the v10 auto-trapping feature.

                         

                        It's great as a tutorial to help users understand how Monarch parses a report.

                         

                        But take, for example, Classic.prn:

                         

                        05/01/08                  CLASSICAL MUSIC DISTRIBUTORS                   PAGE 01    

                        10:17                       MONTHLY SHIPPING REPORT                                 

                        MSR94                      FROM 04/01/08 TO 04/30/08                                                                               

                        CUSTOMER: Betty's Music Store                                                       

                                  Muscatine Plaza                                                           

                                  200 Lower Muscatine                                                       

                                  Cedar Falls, IA 50613                                                     

                                  USA                                                                               

                        ACCOUNT NUMBER: 11887                                                                               

                        CONTACT: Betty Yoder                                                                               

                        MEDIA QTY  DESCRIPTION                              LABEL/NO.   UNT_PRC   AMOUNT                                                                               

                        ORDER NUMBER:  536017    SHIP DATE: 04/08/08                                                                               

                        CD      4  Bartok, Sonata for Solo Violin           MK-42625       8.99    35.96    

                                7  Mozart, Mass in C, K.427                 420831-2       9.00    63.00    

                                2  Luening, Electronic Music                CD 611        10.19    20.38                                                                               

                        TAPE    9  Scarlatti, Stabat Mater                  SBT 48282      5.99    53.91                                                                               

                        ORDER NUMBER:  536039    SHIP DATE: 04/21/08                                                                               

                        CD     11  Beethoven, Pathetique Sonata, Arau       420153-2       5.99    65.89    

                                8  Mendelssohn, War March of the Priests    SMK 47592      8.99    71.92    

                               10  Pizzetti, Messa di Requiem               CHAN 8964      9.59    95.90                                                                               

                        LP      6  Misc., Modern Trombone Masterpieces      ADA 581087    10.79    64.74                                                                               

                        TAPE    6  Gershwin, An American in Paris           ACS 8034       5.99    35.94    

                        /CODE

                         

                        v10 autotrap produces a detail template with the following conditions:

                         

                                Ñ ßà                                    ßßßØ               .ÑÑ      .ÑÑ 

                        /CODE

                         

                        This will miss any record which fails to meet 13 conditions - there are 13 points of failure.

                         

                        In practice, trapping on the last decimal point of the AMOUNT field is an accurate trap for all the records.

                         

                        Best wishes,

                         

                        Olly

                          • Identify untrapped records in large reports
                            Grant Perkins

                            Hi Grant,

                             

                            A word of caution about the v10 auto-trapping feature.

                             

                            It's great as a tutorial to help users understand how Monarch parses a report.

                             

                            But take, for example, Classic.prn:

                             

                            05/01/08                  CLASSICAL MUSIC DISTRIBUTORS                   PAGE 01    

                            10:17                       MONTHLY SHIPPING REPORT                                 

                            MSR94                      FROM 04/01/08 TO 04/30/08                                

                             

                             

                            CUSTOMER: Betty's Music Store                                                       

                                      Muscatine Plaza                                                           

                                      200 Lower Muscatine                                                       

                                      Cedar Falls, IA 50613                                                     

                                      USA                                                                       

                             

                             

                            ACCOUNT NUMBER: 11887                                                               

                             

                            CONTACT: Betty Yoder                                                                

                             

                             

                            MEDIA QTY  DESCRIPTION                              LABEL/NO.   UNT_PRC   AMOUNT    

                             

                                          ORDER NUMBER:  536017    SHIP DATE: 04/08/08                          

                             

                            CD      4  Bartok, Sonata for Solo Violin           MK-42625       8.99    35.96    

                                    7  Mozart, Mass in C, K.427                 420831-2       9.00    63.00    

                                    2  Luening, Electronic Music                CD 611        10.19    20.38    

                             

                            TAPE    9  Scarlatti, Stabat Mater                  SBT 48282      5.99    53.91    

                             

                                          ORDER NUMBER:  536039    SHIP DATE: 04/21/08                          

                             

                            CD     11  Beethoven, Pathetique Sonata, Arau       420153-2       5.99    65.89    

                                    8  Mendelssohn, War March of the Priests    SMK 47592      8.99    71.92    

                                   10  Pizzetti, Messa di Requiem               CHAN 8964      9.59    95.90    

                             

                            LP      6  Misc., Modern Trombone Masterpieces      ADA 581087    10.79    64.74    

                             

                            TAPE    6  Gershwin, An American in Paris           ACS 8034       5.99    35.94    

                            /code

                             

                            v10 autotrap produces a detail template with the following conditions:

                             

                                    Ñ ßà                                    ßßßØ               .ÑÑ      .ÑÑ 

                            /code

                             

                            This will miss any record which fails to meet 13 conditions - there are 13 points of failure.

                             

                            In practice, trapping on the last decimal point of the AMOUNT field is an accurate trap for all the records.

                             

                            Best wishes,

                             

                            Olly[/quote]

                             

                            Olly,

                             

                            I quite agree with all you say above BUT for the purposes of identifying failing traps (or one sort or another) the viaual display potential (and trap line selection manipulation) of the Auto Trapping process might just prove a useful tool here. I'm thinking not so much in terms of actually setting the trap, more as a tool to see where 'difficulties' may arise.

                             

                            This may then lead to problem area evidence either directly - by showing something up on the screen - or indirectly - by raising awareness of a potential problem area that can then be tested for specifically.

                             

                            Historically greenbar reports created as part of a standard application would usually have some sort of check information that one could use - a record count or totals for fiscal values or whatever. I'm not sio sore that these are included in Report Writer created reports (or various output types) in the modern age. But if they are then they offer a simple check mechanism for the effectiveness of the record selection during the Monarch Model development process. One could also create a batch process that ran both the required extraction AND a test data extraction and compared with the numbers provided on the original report.

                             

                            If selecting directly from a database you get what you get. The only check I can think of that might always be available is for the total number of records in the database assuming that all records were so be selected.

                             

                            My 'instant' thoughts. There may be others once the questions have rattled around the grey matter for a while. 

                             

                             

                             

                            Grant

                              • Identify untrapped records in large reports
                                Olly Bond

                                Hello Grant,

                                 

                                One other helpful trick to check data is to throw in a few filters on Unique values - this can help sanity check the data for business rules that can otherwise be difficult to check.

                                 

                                For example, from Classic, a unique filter on Order Number and Account Number can give you a quick check that the same order number hasn't been posted to two accounts.

                                 

                                I agree with the idea of batching the error-checking with the extraction - I guess this is where DataPump helps a lot with the automated verification.

                                 

                                Best wishes,

                                 

                                Olly