3 Replies Latest reply: May 15, 2014 10:02 AM by Grant Perkins RSS

    2 Section report help

    t1machado _

      Hi Everyone,

       

      I've just started working with Mornarch/Datawatch and am having a problem creating a model for a report. I have this 11,000+ page report that I need to strip out 4 or so pages towards the top of the report and then the last 20 or so pages (length is variable so I can't trap any fixed amount of pages, etc). So far I've been able to get all the data I need, but the problem is I can't get rid of the data I DONT want. Currently I break out the reports by type of report (which i'll explain below), but the problem is that I get all the junk from the top of the report to the sections I want, then the data I want, then the bulk of the report I dont want, and finally the last 20 pages broken out correctly.

       

      For us visual learners:

       

      Report1: Top of report data I dont want + first correct report

      Report2: second correct report

      Report3: third correct report

      Report4: fourth correct report + 1100 pages of data I dont want

      Report5: 5th correct report (start of bottom 20 pages I want)

      Report6: 6th correct report

      Report7: 7th correct report

      Report8: 8th correct report

       

       

      So it looks like I need to find a better way to break the pages out to eliminate the data I dont want.

       

      Below is an example of the first page of the report (data I dont want) (numbers and text changed for confidentality):

       

       

      1 UGAP110                                                        UNIVERSITY OF xxxx                                                  PAGE:     1 

        RUN ON:  10/04/08 AT 04:51:42                                         xxxx GENERAL LEDGER                                                            SEPTEMBER 

                                                                          xxxx CURRENT MONTH TRANSACTIONS                                                                               

      FOR THE PERIOD 09/01/08 - 09/30/08                                                              

        ACCOUNT:   555555     INTERFUND CASH ACCOUNT                                                               Assets - Cash                                                                               

      DOCUMENT    TRANSACTION   DOCUMENT   RULE/                                                                      

        FUND      DESCRIPTION                          REFERENCE      DATE        NUMBER    TYPE          DEBIT               CREDIT              BALANCE              

        -


      -


         -


      -


      -


        -


                

        55555Z    BALANCE FORWARD                                                                                .00            5,555.55                            

        55555Z    CURRENT MONTHS ACTIVITY                                                                           .00              555.55                                                                               

      -


        -


        -


                

                  TOTAL 110000-55555Z                                                                               .00            5,555.55            5,555.55CR                                                                               

      55555Z    BALANCE FORWARD                                                                                .00          555,555.55                            

        55555Z    CURRENT MONTHS ACTIVITY                                                                     55,555.55          555,555.55                                                                               

      -


        -


        -


                

                  TOTAL 110000-55555Z                                                                         55,555.55          555,555.55          555,555.55CR                                                                               

      55555Z    BALANCE FORWARD                                                                             55,555.55                 .00                            

        55555Z    CURRENT MONTHS ACTIVITY                                                                      5,555.55                 .00                                                                               

      -


        -


        -


                

                  TOTAL 110000-55555Z                                                                         55,555.55                 .00           55,555.55                                                                               

      55555Z    BALANCE FORWARD                                                                            555,555.55                 .00                            

        55555Z    CURRENT MONTHS ACTIVITY                                                                     55,555.55            5,555.55                                                                               

      -


        -


        -


                

                  TOTAL 110000-55555Z                                                                        555,555.55            5,555.55          555,555.55                                                                               

      -


        -


        -


                

                  TOTAL 110000                                                                               555,555.55          555,555.55          555,555.55CR      

      /CODE

       

       

      Here is the first section of desired data:

       

       

      1 UGAP110                                                        UNIVERSITY OF xxxx                                                 PAGE:    39 

        RUN ON:  10/04/08 AT 04:51:42                                         xxxx GENERAL LEDGER                                                            SEPTEMBER 

                                                                              zzzz ACCOUNT SUMMARY                                                                               

      FOR THE PERIOD 09/01/08 - 09/30/08                                                              

                  ACCSUM                                                                               

      Assets                                                                               

      CURRENT              FISCAL                                            

        ACCOUNT   TITLE                                                                         MONTH              YEAR TO DATE                                                                               

      -


        -


                                            

        555555    INTERFUND CASH ACCOUNT                                                              55,555.55CR        555,555.55CR                                  

        555555    PETTY CASH FUND                                                                           .00              555.55                                                                               

      -


        -


                                            

                  TOTAL Assets                                                                        55,555.55CR        555,555.55CR                                  

       

      /CODE

       

      And the second piece of data I dont want is identical in structure to the first piece as well as the bottom 20 pages are identical in structure to the first piece of data I do want. The only difference from the first piece of wanted data to the second is that "zzzz ACCOUNT SUMMARY" (in the header) is "xxxx ACCOUNT SUMMARY", but other than that the header is exactly the same in both desired data pieces.

       

      Some traps i've employed:

       

      • trap for "ACCOUNT SUMMARY" and capture the "xxxx" or "zzzz" part (used for the report title)

       

      • trap for "ACCSUM" in header

       

      • trap for "Assets" (well really the trap is any alpha character where the "A" is in assets are the reports I want only are the ones that have text in that position) - this is what I use for my report breaks.

       

      • my details view consists of capturing the account, title, and both decimal points (notice how they are differently aligned in the two examples).

       

      Again I capture all data correctly and MY TABLE VIEW IS EXACLY HOW I WANT IT but the report does not come out as I would like it.

       

      Any help would be greatly appreciated. Let me know if you need more information.

       

      Thank you,

       

      Tom

        • 2 Section report help
          Data Kruncher

          Hi Tom,

           

          Am I correct in thinking that you're only interested in including the records for the group totals, like "Assets", and you don't want the records with the account number level detail, like "110000-55555Z" in the table?

           

          You can filter out the numbered accounts, assuming that they all begin with a digit in the first position, with this expression:

          IsAlpha(Left(ActDesc,1))[/CODE]

           

          Numbers will cause the IsAlpha function to return a FALSE boolean value (0), so they won't be returned by the filter.

           

          Sorry if I'm oversimplifying. Not feeling so good today, and the cold medicine might be playing with me.

            • 2 Section report help
              t1machado _

              Thank you for your response.

               

              Really what I am looking for are specific pages in the report. I dont want of the information in the first example at all but want everything in the second example, including the "TOTAL Assets" row.

               

              As I mentioned i'm capturing all the right data in my table view, but trying to view it through Datawatch all of the rows that aren't in my table still appear in the reports as described in my first post.

               

              Sorry if i'm being confusing. I think this is probably more of a Datawatch problem with setting up my document type then a monarch modeling question...

                • 2 Section report help
                  Grant Perkins

                  Tom,

                   

                  You write that the TABLE is exactly how you want it but that the REPORT does not drop the pages you do not want. And you also mention potential problems differentiated between Monarch and 'Datawatch'.

                   

                  Are you by any chance using Datawatch ES? (Or RMS)

                   

                  Or are you Printing/Exporting the REPORT rather than the Table? If you are exporting a Table that looks correct on screen when 'filtered' by a Trap mechanism and get the additional unwanted pages back I would be very puzzled. On the other hand if you are extracting all pages to the table but filtering the table to include only the desired pages then it sort of sounds like your export (whatever method) is not applying the filter. (By which I am suggesting a genuine FILTER using the pre-extracted fields rather than a 'trap filter' effect.

                   

                  Now, if you are using ES then, in the absence of any alternative processing before adding to the archive, the original report will be the original report but the data presentations for the prepared analyses will be the tables/summaries, etc., that have been pre-prepared using the Monarch component.

                   

                  If you need to pre-process the report to remove some pages (assuming the page numebrs and the number of pages to be removed are not constants for all report runs) then one option I have used would be to extract every line of the report as a complete line (easily done) and then find a way to filter out the pages/lines that you don't want, displaying the result in a table with very wide fields. If you then export or print that table to a new file you will get a report that can be processed directly to a table or summaries having had all the unwanted pages removed.

                   

                  A basic manual version of the pre-process might involve assessing the report visually to decide which pages to reject. Enter the page numbers in a calculated field that requests User data input and use that input in conjunction with another calculated field created using the PAGE() function to filter the pages to be included in the table.

                   

                   

                  My apologies if I am reading too much into the information in your posts but you seems to have good success in extracting the information you want from the report to the table and that leaves me a little puzzled about the problems you have with the output for the Report.

                   

                  HTH.

                   

                  Grant