12 Replies Latest reply: May 15, 2014 10:09 AM by Olly Bond RSS

    Resorting and creating new page-breaks from an 3rd party report

    drobert _

      Hello fellow Monarch users and gurus.

       

      We receive daily a report which comes from a third-party and is in a format they deem generic for all their clients. If we want any modifications to it, we would need to pay for the customization:eek:.

       

      Knowing that Monarch is very powerful and can do things that was not really intended to do, I though that I would try to modify the report (read it in its original format and output it in the desired format) to suit our needs. The snag that I am finding is that we want to have a new page (page break with header lines) for every section we want to output (see sample below). Our sections are defined by the numeric value that is extracted from the 7th to 9th digits of the PAN. Can this be done?

      1RUN DATE 10/08/2010                           SWITCH SERVICES                                                     REPORT:   EVSCC004

      RUN TIME 15:33                              TRANSACTION DETAIL - CARDHOLDER ACTIVITY                                DATE: 10/08/2010

                                                                                      PAGE:          1

       

      ROUTE:   0819                                 CREDIT UNION                           

      TRANSIT: 00099                              RGGRCUCC                                                                 BID:   C7N1C999

       

      0PAN                 MBR TRAN TYPE        TERM ID                   ACQUIRER ID     RETAILER TYPE                      AMOUNT 1   CUR

         LOCAL DATE/TIME       CARD ACCP ID     TERM LOCATION             ISSUER ID       STAND-IN                           AMOUNT 2

         TRACE NUMBER          ACCOUNT TYPE     TERM CITY      PROV       NETWORK         REASON                      CHIP     IC FEE

      -


      --- -


        -


        -


      -


      -


      -


      50179318911111111   000 DEP TO CHQ       C197N200                  C7N1C999                                             600.00     

         10/07 14:03:39        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5445                  A                WINNIPEG       MB         OnUs            D-070-SYSTEM ERROR             N       .000

       

      50179318911111111   000 DEP TO CHQ       C197N200                  C7N1C999                                             500.00     

         10/07 14:04:20        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5446                  A                WINNIPEG       MB         OnUs                                           N       .000

       

      50179318822222222   000 W/D FR CHQ       C197N200                  C7N1C999                                             200.00-    

         10/07 14:07:35        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5450                  A                WINNIPEG       MB         OnUs                                           N       .000

       

      50179318822222222   000 DEP TO CHQ       C197N200                  C7N1C999                                           4,500.00     

         10/07 14:08:36        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5451                  A                WINNIPEG       MB         OnUs                                           N       .000

       

      50179316711111111   000 INQ FR CHQ       C197N200                  C7N1C999                                                .00     

         10/07 14:23:58        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5452                  A                WINNIPEG       MB         OnUs            D-054-DATA BASE PROBLEM        N       .000

       

      50179316711111111   000 DEP TO CHQ       C197N200                  C7N1C999                                           6,900.00     

         10/07 14:25:30        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5453                  A                WINNIPEG       MB         OnUs            D-054-DATA BASE PROBLEM        N       .000

       

      50179316711111111   000 INQ FR CHQ       C197N200                  C7N1C999                                                .00     

         10/07 14:48:14        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5454                  A                WINNIPEG       MB         OnUs            D-054-DATA BASE PROBLEM        N       .000

       

      50179316711111111   000 DEP TO CHQ       C197N200                  C7N1C999                                           6,000.00     

         10/07 14:56:17        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5455                  A                WINNIPEG       MB         OnUs            D-054-DATA BASE PROBLEM        N       .000

       

      50179316711111111   000 INQ FR CHQ       C197N200                  C7N1C999                                                .00     

         10/07 16:20:09        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5456                  A                WINNIPEG       MB         OnUs            D-054-DATA BASE PROBLEM        N       .000

       

      50179318822222222   000 W/D FR CHQ       C197N200                  C7N1C999                                              60.00-    

         10/07 16:20:41        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5457                  A                WINNIPEG       MB         OnUs                                           N       .000

       

      50179318911111111   000 DEP TO CHQ       C197N200                  C7N1C999                                               6.55     

         10/07 16:23:20        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5460                  A                WINNIPEG       MB         OnUs                                           N       .000

       

       

       

       

      1RUN DATE 10/08/2010                           SWITCH SERVICES                                                     REPORT:   EVSCC004

      RUN TIME 15:33                              TRANSACTION DETAIL - CARDHOLDER ACTIVITY                                DATE: 10/08/2010

                                                                                      PAGE:          2

       

      ROUTE:   0819                                 CREDIT UNION                           

      TRANSIT: 00099                              RGGRCUCC                                                                 BID:   C7N1C999

       

      0PAN                 MBR TRAN TYPE        TERM ID                   ACQUIRER ID     RETAILER TYPE                      AMOUNT 1   CUR

         LOCAL DATE/TIME       CARD ACCP ID     TERM LOCATION             ISSUER ID       STAND-IN                           AMOUNT 2

         TRACE NUMBER          ACCOUNT TYPE     TERM CITY      PROV       NETWORK         REASON                      CHIP     IC FEE

      -


      --- -


        -


        -


      -


      -


      -


      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  19.68-    

         10/07 18:09:05        P12389998999     0000000000000000000       C7N1C999        N                                          

         000007180905          P                MISSISSAUGA    ON         Interac                                        N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  19.68     

         10/07 18:09:05        P12389998999                               C7N1C999        N                                          

         000007180905          P                MISSISSAUGA    ON         Interac         R-01-TIME OUT                  N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  51.62-    

         10/07 18:13:40        P12389998999     0000000000000000000       C7N1C999        N                                          

         000007181340          P                MISSISSAUGA    ON         Interac                                        N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  69.94-    

         10/07 18:13:50        P12389998999     0000000000000000000       C7N1C999        N                                          

         000007181350          P                MISSISSAUGA    ON         Interac                                        N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  41.36-    

         10/07 18:14:12        P12389998999     0000000000000000000       C7N1C999        N                                          

         000007181412          P                MISSISSAUGA    ON         Interac                                        N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  41.36     

         10/07 18:14:12        P12389998999                               C7N1C999        N                                          

         000007181412          P                MISSISSAUGA    ON         Interac         R-01-TIME OUT                  N       .000

       

      -                                            ********** END OF REPORT **********/code

       

      What I want it to look like is something like this (notice the new line in the header to indicate the Institution the PAN belongs to):

      1RUN DATE 10/08/2010                           SWITCH SERVICES                                                     REPORT:   EVSCC004

      RUN TIME 15:33                              TRANSACTION DETAIL - CARDHOLDER ACTIVITY                                DATE: 10/08/2010

                                                                                      PAGE:          1

       

      ROUTE:   0819                                 CREDIT UNION                           

      TRANSIT: 00099                              RGGRCUCC                                                                 BID:   C7N1C999

      INST:     167

       

      0PAN                 MBR TRAN TYPE        TERM ID                   ACQUIRER ID     RETAILER TYPE                      AMOUNT 1   CUR

         LOCAL DATE/TIME       CARD ACCP ID     TERM LOCATION             ISSUER ID       STAND-IN                           AMOUNT 2

         TRACE NUMBER          ACCOUNT TYPE     TERM CITY      PROV       NETWORK         REASON                      CHIP     IC FEE

      -


      --- -


        -


        -


      -


      -


      -


      50179316711111111   000 INQ FR CHQ       C197N200                  C7N1C999                                                .00     

         10/07 14:23:58        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5452                  A                WINNIPEG       MB         OnUs            D-054-DATA BASE PROBLEM        N       .000

       

      50179316711111111   000 DEP TO CHQ       C197N200                  C7N1C999                                           6,900.00     

         10/07 14:25:30        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5453                  A                WINNIPEG       MB         OnUs            D-054-DATA BASE PROBLEM        N       .000

       

      50179316711111111   000 INQ FR CHQ       C197N200                  C7N1C999                                                .00     

         10/07 14:48:14        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5454                  A                WINNIPEG       MB         OnUs            D-054-DATA BASE PROBLEM        N       .000

       

      50179316711111111   000 DEP TO CHQ       C197N200                  C7N1C999                                           6,000.00     

         10/07 14:56:17        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5455                  A                WINNIPEG       MB         OnUs            D-054-DATA BASE PROBLEM        N       .000

       

      50179316711111111   000 INQ FR CHQ       C197N200                  C7N1C999                                                .00     

         10/07 16:20:09        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5456                  A                WINNIPEG       MB         OnUs            D-054-DATA BASE PROBLEM        N       .000

       

      1RUN DATE 10/08/2010                           SWITCH SERVICES                                                     REPORT:   EVSCC004

      RUN TIME 15:33                              TRANSACTION DETAIL - CARDHOLDER ACTIVITY                                DATE: 10/08/2010

                                                                                      PAGE:          2

       

      ROUTE:   0819                                 CREDIT UNION                           

      TRANSIT: 00099                              RGGRCUCC                                                                 BID:   C7N1C999

      INST:     188

       

      0PAN                 MBR TRAN TYPE        TERM ID                   ACQUIRER ID     RETAILER TYPE                      AMOUNT 1   CUR

         LOCAL DATE/TIME       CARD ACCP ID     TERM LOCATION             ISSUER ID       STAND-IN                           AMOUNT 2

         TRACE NUMBER          ACCOUNT TYPE     TERM CITY      PROV       NETWORK         REASON                      CHIP     IC FEE

      -


      --- -


        -


        -


      -


      -


      -


      50179318822222222   000 W/D FR CHQ       C197N200                  C7N1C999                                             200.00-    

         10/07 14:07:35        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5450                  A                WINNIPEG       MB         OnUs                                           N       .000

       

      50179318822222222   000 DEP TO CHQ       C197N200                  C7N1C999                                           4,500.00     

         10/07 14:08:36        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5451                  A                WINNIPEG       MB         OnUs                                           N       .000

       

      50179318822222222   000 W/D FR CHQ       C197N200                  C7N1C999                                              60.00-    

         10/07 16:20:41        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5457                  A                WINNIPEG       MB         OnUs                                           N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  19.68-    

         10/07 18:09:05        P12389998999     0000000000000000000       C7N1C999        N                                          

         000007180905          P                MISSISSAUGA    ON         Interac                                        N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  19.68     

         10/07 18:09:05        P12389998999                               C7N1C999        N                                          

         000007180905          P                MISSISSAUGA    ON         Interac         R-01-TIME OUT                  N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  51.62-    

         10/07 18:13:40        P12389998999     0000000000000000000       C7N1C999        N                                          

         000007181340          P                MISSISSAUGA    ON         Interac                                        N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  69.94-    

         10/07 18:13:50        P12389998999     0000000000000000000       C7N1C999        N                                          

         000007181350          P                MISSISSAUGA    ON         Interac                                        N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  41.36-    

         10/07 18:14:12        P12389998999     0000000000000000000       C7N1C999        N                                          

         000007181412          P                MISSISSAUGA    ON         Interac                                        N       .000

       

      50179318833333333   000 PUR FR CK        A9990009                  IN100401        0001                                  41.36     

         10/07 18:14:12        P12389998999                               C7N1C999        N                                          

         000007181412          P                MISSISSAUGA    ON         Interac         R-01-TIME OUT                  N       .000

       

      1RUN DATE 10/08/2010                           SWITCH SERVICES                                                     REPORT:   EVSCC004

      RUN TIME 15:33                              TRANSACTION DETAIL - CARDHOLDER ACTIVITY                                DATE: 10/08/2010

                                                                                      PAGE:          3

       

      ROUTE:   0819                                 CREDIT UNION                           

      TRANSIT: 00099                              RGGRCUCC                                                                 BID:   C7N1C999

      INST:     189

       

      0PAN                 MBR TRAN TYPE        TERM ID                   ACQUIRER ID     RETAILER TYPE                      AMOUNT 1   CUR

         LOCAL DATE/TIME       CARD ACCP ID     TERM LOCATION             ISSUER ID       STAND-IN                           AMOUNT 2

         TRACE NUMBER          ACCOUNT TYPE     TERM CITY      PROV       NETWORK         REASON                      CHIP     IC FEE

      -


      --- -


        -


        -


      -


      -


      -


      50179318911111111   000 DEP TO CHQ       C197N200                  C7N1C999                                             600.00     

         10/07 14:03:39        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5445                  A                WINNIPEG       MB         OnUs            D-070-SYSTEM ERROR             N       .000

       

      50179318911111111   000 DEP TO CHQ       C197N200                  C7N1C999                                             500.00     

         10/07 14:04:20        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5446                  A                WINNIPEG       MB         OnUs                                           N       .000

       

      50179318911111111   000 DEP TO CHQ       C197N200                  C7N1C999                                               6.55     

         10/07 16:23:20        00000000000      100-123 MAIN STREET       C7N1C999        N                                          

         5460                  A                WINNIPEG       MB         OnUs                                           N       .000

       

      -                                            ********** END OF REPORT **********

      /code

        • Resorting and creating new page-breaks from an 3rd party report
          drobert _

          Oh yeah, I forgot to mention everything I was having trouble with:

          The detail line is on three lines, if I cut the lines into three fields, Monarch naturally assumes (unless I am missing something) that each field is printed next to previous one.  If I define it as multi-line field, it compresses the multi-spaces in a single space (see thread [URL="http://www.monarchforums.com/showthread.php?t=3337"]#3337[/URL] about this undocumented feature), which is a problem as the data does not match the headings, and also it does not wrap the line.

          How do I get to print a new page with the proper institution printed in the heading before the data of that institution?

          /LIST

            • Resorting and creating new page-breaks from an 3rd party report
              Data Kruncher

              This is a particularly challenging need, but I believe that it could be accomplished.

               

              I have a model that gets you a good portion of the way there, but because of the headings, this'll need more than one model I think.

               

              I don't know how I could explain the modeling approach succinctly, so send me a PM with an email address and I'll send the model that I've got so far.

               

              The final process would output the first heading segment, then the INST line, then the next heading segment, followed by the necessary detail records, and repeating for the next INST number.

               

              Looks like a VBA driven process to me at this point. Might be able to be done with a batch file, but (maybe) only if you know in advance the number of INST instances and their values. VBA would be better.

               

              No matter what, as you've eluded, this isn't typical straightforward Monarch work.

                • Resorting and creating new page-breaks from an 3rd party report
                  Grant Perkins

                  Just wondering if an approach that treated things as 4 line blocks for each record and then output an individual 'page' report, with standard header, for each 'Inst' might work.

                   

                  The individual reports could then be concatenated back to a single report.

                   

                  The line formatting should be retained, afaik, using Textline calculated fields rather than a text block.

                   

                  That said it seems a lot of work to undertake to hardly improve the usability of the information in the report at all when it looks like it's possible to extract and present something useful from the original.

                   

                  Am I missing something here?

                   

                   

                  Grant

                    • Resorting and creating new page-breaks from an 3rd party report
                      drobert _

                      Hi DK and Grant,

                       

                      @Grant: what you suggested is similar to what DK PMed me and what I have pretty much done.

                       

                      I thank you both for your suggestions.:)

                       

                       

                       

                       

                       

                       

                      What I did is the following in a DOS:eek: batch file:

                      I have a model which simply gives me a list of all the unique institutions in the report and dumps this in a text file.

                      I then take this text file and read it line by line and supply the other model with the institution as the parameter and output that as a PDF.

                      In this model I took the heading information from the heading (excluding the page number -- I inserted that in the custom page header)

                      I also took the data information as a single field ending at line 3 (I don't really need to keep a blank line as I insert it myself)

                      In the Table I created the custom headings with the first occurrence of the reports headings and inserting the actual page number.

                      I created a calculated field to output the data lines using textline and a bit of fancy work to get it to lineup like it did (see note below).

                      I removed the grid, column names and row numbers and outputted that to a PDF

                      /LIST

                      /LISTIt turned out that they preferred to have separate PDF for each institution and it made my task MUCH:D easier.

                       

                      NOTE: For some reason, if I inserted Chr( 13 ) + Chr( 10 ) at the end of the line, it would do the same as if printing the original detail field -- it would compress all the multiple spaces into one (nice!:rolleyes:) and wrapped the lines one after the other, which buggers up the alignment. If I only use Chr( 13 ), it looks great on screen and in Print Preview but the PDF was showing a little box where the CR was and was just wrapping the lines. If I simply made sure that all lines were 133 characters (like the report is) is seem to work, sort of, since the first line is Ok, but the subsequent lines aligned to the left ignoring the spaces at the beginning (great!:rolleyes:) so those lines didn't line-up. So the only way around this was to replace the first space of the second and third line with a very small and inconspicuous character (I chose Chr( 184 ) of the Courier New font) so that it thinks there is a character at the beginning of these lines and makes it lineup, but the little character is visible. (what a hassle!:().

                       

                      Am I missing something or is this supposed to work like that (I know that this is similar to the issue I raised in thread [URL="http://www.monarchforums.com/showthread.php?t=3337"]#3337[/URL])?

                       

                      Another thing that is a little bizarre is that for some reason, there is a blank line that is mysteriously inserted between the Page Headings and the data.

                        • Resorting and creating new page-breaks from an 3rd party report
                          Data Kruncher

                          Good to hear that you're making progress. i'd wondered what became of this one.

                           

                          As to the [URL="http://www.monarchforums.com/showthread.php?t=3143"]issue of CHR(13) & CHR(10) see post #2[/URL] I did receive confirmation at the time that it's a problem with v10 (and other versions too I think). Sorry, forgot about it completely.

                           

                          Your efforts are representative of the old "where there's a will there's a way" belief.

                           

                          Nice work.

                          • Resorting and creating new page-breaks from an 3rd party report
                            Grant Perkins

                            133 characters seems odd.

                             

                            Back in the days of greenbar reports the 'standards' were nearly always 80 characters (for 'letter' and A4 width continuous paper feeds) and 132 (for 'wide' greenbar paper).

                             

                            Those are for printed characters. Invisible (when printed) formatting characters were additional and could, to some extent, depend on the source system producing the document. Many systems used a line start character that they did not print.

                             

                            I think you may be battling the formatting commands here. I doubt PDF writers will play well with them. Have you tried creating an XPS document for comparison?

                              • Resorting and creating new page-breaks from an 3rd party report
                                drobert _

                                133 characters seems odd.[/quote]

                                 

                                This report is in mainframe printer format which has the first character of every line as the [URL="http://www.felgall.com/asa.htm"]ASA Printer Control Character[/URL] so that is why it came out to 133 (as you suspected). I could have easily ignored the first character of every line and made it look proper, but it was to mimic the old report.

                                 

                                The 133 characters does not pose a problem at all, its when the subsequent lines start with spaces, Monarch decides to ignore the leading spaces of those lines.

                                 

                                Here is what I mean:

                                Input:[code]

                                Field1  Field2  Field3[/FONT]

                                  1111    2222    3333[/FONT]

                                   444     555     666[/FONT]

                                    77      77      77[/FONT][/code]

                                 

                                Output:[code]

                                Field1  Field2  Field3[/FONT]

                                  1111    2222    3333[/FONT]

                                444     555     666[/FONT]

                                77      77      77[/FONT][/code]

                                 

                                This is what I did to fix it (the dot represents the character that I added):[code]

                                Field1  Field2  Field3[/FONT]

                                  1111    2222    3333[/FONT]

                                ∙  444     555     666[/FONT]

                                ∙   77      77      77[/FONT][/code]