15 Replies Latest reply: May 15, 2014 9:53 AM by Chickenman _ RSS

    Extracting data from a report leaving formatting as is

    kmpirish _

      Is it possible to use Monarch to extract data from a report, but keep the report formatting intact?

       

      I work at a hospital and my project involves a daily report from our patient information system.  A Long Time Ago, this report was meant to be printed on a line printer.  Now, of course, the report must be handled electronically. 

       

      My mission is to take the original report and divide it up into two files.  The division will be based on a number that's included in the header of each page. It's simple enough to use Monarch to find the header, but somehow I need to output each page as is.

       

      I'm used to using Monarch to turn yucky reports into pretty ones - this is the first time I've been asked to keep the yuckiness

       

      Thanks in advance,

      Kate

        • Extracting data from a report leaving formatting as is
          Grant Perkins

          Originally posted by kmpirish:

           

          I'm used to using Monarch to turn yucky reports into pretty ones - this is the first time I've been asked to keep the yuckiness

           

          Thanks in advance,

          Kate /b[/quote]Hi Kate,

           

          You have my sympathies ...

           

          How about just extracting every complete line as a single field? With luck you could add the identifier to each line as an Append field (hidden for output) and use it for filtering just the lines, and therefore the pages, you need.

           

          You may need to ensure you use a fixed rather than variable font. If the report has presentation attributes like bold or italic characters they will be lost.

           

          The print or export the resulting table. Page numbering would likely be wrong of course, but in line with the original 'for auditing purposes'!    

           

          Does that suggest anything useful?

           

          Grant

          • Extracting data from a report leaving formatting as is
            Chickenman _

            Hi Grant,

             

            First of all, forgive or chastise me if I am violating a protocol by piggy-backing this question/reply with a related question of my own, but I have been beating my head against the wall trying to do this one.

             

            The user requests that a nightly report have its output converted to PDF and emailed to him. We use MDP pretty extensively so that was going to be the approach rather than custom programming.

             

            Follows is the text file input. I can "print" as PDF but have no way to invoke that action in Monarch. What I've been trying without success is extract the data to a table for export, but am unable to retain the original look of the report.

             

            Any help would be most welcome.

             

            [font="courier"]    30825                                                   XXX, Inc.                                    Page      -        4     

                                                                  Standard Cost Simulation                               Date      -  9/10/07                                                                               

            Branch . . . . .           Plant  Cost Center                                                                               

            Cost Method. . . 07 Standard                                                                               

            Cst  Cost   . . Frozen Cost . .      . .Simulated Cost. .          . . . Variance . . .             

                Item Number        Item Descr Typ  Desc  Net Added      Total      Net Added      Total      Net Added       Total     Percent    

              -


            -


            --- -


            -


            -


            -


            -


            -


            -


            -


                 

              20325002             ASSY CHART A1  Materi                    50.77                     50.77                               .003    

                                              B1  Direct        3.74         3.74         3.74         3.74                                       

                                              C4  Labor        17.85        17.85        17.85        17.85                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  20325002                             21.60        72.37        21.60        72.38                               .002                                                                               

            20325003             ###ASSY CH A1  Materi                    74.98                     74.98                               .002    

                                              B1  Direct        3.74         3.74         3.74         3.74                                       

                                              C4  Labor        17.85        17.85        17.85        17.85                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  20325003                             21.60        96.58        21.60        96.59                               .002                                                                               

            20325004             ###ASSY CH A1  Materi                    61.68                     61.68                               .003    

                                              B1  Direct        3.74         3.74         3.74         3.74                                       

                                              C4  Labor        17.85        17.85        17.85        17.85                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  20325004                             21.60        83.28        21.60        83.28                               .002                                                                               

            20325005             ASSY CHART A1  Materi                    88.14                     88.14                               .002    

                                              B1  Direct        3.74         6.24         3.74         6.24                                       

                                              C4  Labor        17.85        29.76        17.85        29.76                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  20325005                             21.60       124.14        21.60       124.14                               .001                                                                               

            20325006             ASSY CHART A1  Materi                    40.66                     40.66                               .004    

                                              B1  Direct        4.36         4.36         4.36         4.36                                       

                                              C4  Labor        20.83        20.83        20.83        20.83                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  20325006                             25.20        65.86        25.20        65.86                               .003                                                                               

            20559059             ASSY CHART A1  Materi                    21.46                     21.46                               .009    

                                              B1  Direct        1.12         1.41         1.12         1.41                                       

                                              C4  Labor         5.35         6.72         5.35         6.72                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  20559059                              6.48        29.60         6.48        29.60                               .006                                                                               

            20644221             ASSY HARN  A1  Materi                    21.28                     21.63                       .35    1.644    

                                              B1  Direct        1.49        11.45         1.49        11.45                                       

                                              C4  Labor         7.14        54.65         7.14        54.65                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  20644221                              8.64        87.39         8.64        87.74                       .35     .400                                                                               

            20644456             ASSY HARN  A1  Materi                    26.77                     27.12                       .35    1.307    

                                              B1  Direct        1.24         9.07         1.24         9.07                                       

                                              C4  Labor         5.95        43.30         5.95        43.30                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  20644456                              7.20        79.15         7.20        79.50                       .35     .442                                                                               

            21120001             ###ASSY CH A1  Materi                    34.50                     34.51                               .005                                                                               

            30825                                                   XXX, Inc.                                    Page      -        5     

                                                                  Standard Cost Simulation                               Date      -  9/10/07                                                                               

            Branch . . . . .           Plant  Cost Center                                                                               

            Cost Method. . . 07 Standard                                                                               

            Cst  Cost   . . Frozen Cost . .      . .Simulated Cost. .          . . . Variance . . .             

                Item Number        Item Descr Typ  Desc  Net Added      Total      Net Added      Total      Net Added       Total     Percent    

              -


            -


            --- -


            -


            -


            -


            -


            -


            -


            -


                 

                                              B1  Direct        1.87         1.87         1.87         1.87                                       

                                              C4  Labor         8.92         8.92         8.92         8.92                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  21120001                             10.80        45.31        10.80        45.31                               .004                                                                               

            21120003             ASSY CHART A1  Materi                    21.57                     21.58                               .009    

                                              B1  Direct        1.87         1.87         1.87         1.87                                       

                                              C4  Labor         8.92         8.92         8.92         8.92                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  21120003                             10.80        32.38        10.80        32.38                               .006                                                                               

            21673823             ASSY HARN  A1  Materi                     5.32                      5.67                       .35    6.575    

                                              B1  Direct        4.11         4.11         4.11         4.11                                       

                                              C4  Labor        19.64        19.64        19.64        19.64                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  21673823                             23.76        29.08        23.76        29.43                       .35    1.203                                                                               

            22308000             KIT P/H CN A1  Materi                   374.98                    375.11                       .13     .034    

                                              B1  Direct        4.07        33.83         4.07        33.83                                       

                                              C4  Labor        19.41       161.39        19.41       161.39                                                                               

            -


            -


            -


            -


            -


            -


            -


                 

                 UOM. EA  22308000                             23.48       570.22        23.48       570.35                       .13     .022     /font[/quote]

             

            [size="1"][ September 11, 2007, 10:56 AM: Message edited by: Chickenman ][/size]

            • Extracting data from a report leaving formatting as is
              Grant Perkins

              Hi Chickenman,

               

              You're taking me slightly outside the things I typically have to do with Monarch so to some extent these thoughts are untested in recent times (or even at all!) but ...

               

              Firstly Monarch V9 now offers the option to export the Report window to PDF or fixed text outputs. If using PDF there are some additional features related to using the Monarch Tree to create PDF Bookmarks.

               

              That's the good news. The less good news is that you will have compatibility issues with DP 8.5 if you use Monarch 9.

               

              I am unsure whether you can directly export from a command line BUT it should be possible to set up a Project Export and export your PDF file through that. The Project Export can be initiated through a command line batch file with V9 until you see the DP V9 upgrade.

               

              Plan B (for V9 or V8) would be treat each and every complete line in the report window as a field, retaining page breaks and so on. (But you would already have lost any fancy formatting  - bold, italic, etc. of course.) Make the resulting table the basis for your output and use a fixed font NOT a proportional font. This should retain the character spacing, though it may look different to the original. (The same comment applies to the report window as well - for consistency.)

               

              You should then be able to export the Table specifying PDF as the output file type.

               

              I may have missed a couple of points here  - this is a quick unchecked response. The theory should work but I never underestimate the potential for information systems to come up with new tricks to make life more difficult than it should be!

               

              Hopefully it would only take you a couple of minutes and perhaps a quick glance at the What's New section and links from it in the Help system to discover whether the ideas hold water. If they don't we may need to think again.

               

              I'm not overly familiar with DP 8.5 but I presume that it would allow you the control to run the table export to a PDF file and then define the subsequent distribution.

               

              HTH.

               

               

              Grant

              • Extracting data from a report leaving formatting as is
                Chickenman _

                Hi Grant,

                 

                I appreciate the fast response.

                 

                My V9 is running locally and MDP on Terminal Server, but it's not clear to me how it helps if they are co-located. Am anxiously awaiting MDP 9 in any event.

                 

                As regards extracting each and every line as text and arranging in the table WYSIWYG (more or less, fonts etc unimportant)is something I have been trying since I saw your reply to the original post. Sadly without success. Will try again.

                 

                Best regards,

                CM

                • Extracting data from a report leaving formatting as is
                  Grant Perkins

                  Hi,

                   

                  I don't think the co-location would be of benefit. Any V9 solution would have to stay a V9 solution for now and run as a batch process of some sort. You might be able to generate a faux task in MDP to pick up the V9 batch file generated PDF file, do nothing much to it and then distribute the source file.

                   

                  The concept of picking entire lines and exporting them is one that I know can be used successfully with fixed width text output providing all the settings are OK to retain the format. I've never tried it direct to PDF. What anomalies are you finding?

                   

                   

                  Grant

                   

                   

                  Originally posted by Chickenman:

                  Hi Grant,

                   

                  I appreciate the fast response.

                   

                  My V9 is running locally and MDP on Terminal Server, but it's not clear to me how it helps if they are co-located. Am anxiously awaiting MDP 9 in any event.

                   

                  As regards extracting each and every line as text and arranging in the table WYSIWYG (more or less, fonts etc unimportant)is something I have been trying since I saw your reply to the original post. Sadly without success. Will try again.

                   

                  Best regards,

                  CM /b[/quote]

                  • Extracting data from a report leaving formatting as is
                    Chickenman _

                    Hey,

                     

                    OK, I see what you mean by the batch process now.

                     

                    As regards picking entire lines, I simply am not familiar with this concept is my problem. If not too much trouble, point me to a primer on making up the necessary templates and I'll start with that - my original post included a representation of the actual report output complete with the dreaded page-break...

                     

                    Thanx, CM

                     

                    Originally posted by Grant Perkins:

                    Hi,

                     

                    I don't think the co-location would be of benefit. Any V9 solution would have to stay a V9 solution for now and run as a batch process of some sort. You might be able to generate a faux task in MDP to pick up the V9 batch file generated PDF file, do nothing much to it and then distribute the source file.

                     

                    The concept of picking entire lines and exporting them is one that I know can be used successfully with fixed width text output providing all the settings are OK to retain the format. I've never tried it direct to PDF. What anomalies are you finding?

                     

                     

                    Grant

                     

                     

                      /size[quote]quote:[/size]Originally posted by Chickenman:

                    Hi Grant,

                     

                    I appreciate the fast response.

                     

                    My V9 is running locally and MDP on Terminal Server, but it's not clear to me how it helps if they are co-located. Am anxiously awaiting MDP 9 in any event.

                     

                    As regards extracting each and every line as text and arranging in the table WYSIWYG (more or less, fonts etc unimportant)is something I have been trying since I saw your reply to the original post. Sadly without success. Will try again.

                     

                    Best regards,

                    CM /b[/quote][/b][/size][/QUOTE]

                    • Extracting data from a report leaving formatting as is
                      Grant Perkins

                      Hi CM,

                       

                      Set up a template, one line data sample - any line will do, no trap defined so Monarch will select everything.

                       

                      Paint a single field for the entire maximum width of the report. Set the font to be a fixed type - like 'System' so spacing is consistent.

                       

                      That's about it.

                       

                      Appends are a bit difficult since there is not much to work with but you could possibly add an extra field (but not 'printed' maybe) for Page Number which may have some sort of use.

                       

                      However the previously mentioned V9 functionality to export the report window, when or if you are able to make use of it, would certainly seem to offer greater potential for PDF output and would not require the 'full line field for every line' approach. It would also give the option to add value to the report to the benefit of the recipient.

                       

                      Does that help at all?

                       

                      Grant

                      • Extracting data from a report leaving formatting as is
                        Chickenman _

                        Hi Grant,

                         

                        "No trap defined" is where I was blind - duh. Let me run with this suggestion and see if I cannot tackle this successfully now.

                         

                        Regards,

                        CM

                        • Extracting data from a report leaving formatting as is
                          Chickenman _

                          Got it! The PDF out put is funky because it left-registers everything but that's minor.

                           

                          I appreciate your help!!

                           

                          Thx,

                          CM

                          • Extracting data from a report leaving formatting as is
                            Grant Perkins

                            Originally posted by Chickenman:

                            The PDF out put is funky because it left-registers everything but that's minor.

                             

                            /b[/quote]Hmm, that's interesting.

                             

                            If it is a problem it makes me wonder how to fix it. Does it shift only on PDF output and looks OK before that?

                             

                            I'm assuming you went with the 'trap everything' solution for now. Did you use V8 or V9?

                             

                             

                            Grant

                            • Extracting data from a report leaving formatting as is
                              Chickenman _

                              Oh, I should have mentioned this was in MDP 8.5 so the Monarch version is 8.02.

                               

                              Both the report window and resultant table window were set to fixed font and everything looks WYSISYG - just the PDF output is completely left-justified.

                              • Extracting data from a report leaving formatting as is
                                Grant Perkins

                                Originally posted by Chickenman:

                                Both the report window and resultant table window were set to fixed font and everything looks WYSISYG - just the PDF output is completely left-justified. /b[/quote]Out of interest if you run the same export process manually in V9 do you get the same result?

                                 

                                 

                                Grant

                                • Extracting data from a report leaving formatting as is
                                  Chickenman _

                                  I'm just now getting back to this and interestingly the PDF output from the table is BLANK!

                                  • Extracting data from a report leaving formatting as is
                                    Grant Perkins

                                    Originally posted by Chickenman:

                                    I'm just now getting back to this and interestingly the PDF output from the table is BLANK! [/b][/quote]Hi CM,

                                     

                                    So you are seeing a reasonably fully populated table on Monarch but the Export to a PDF file results in blank? Everything blank?

                                     

                                    Can you get any other exports to PDF files to work? Either manually or through a project export?

                                     

                                     

                                    Grant

                                    • Extracting data from a report leaving formatting as is
                                      Chickenman _

                                      Everything blank.

                                       

                                      this is a manual export. No project exists for this extraction.

                                       

                                      I'd say this is as far as we need to go here because the user is getting the MDP output albeit left-justified and is satisfied.

                                       

                                      Best regards and thanx for the help!

                                       

                                      CM