14 Replies Latest reply: May 15, 2014 9:52 AM by Dieter _ RSS

    Creating Multiple Reports

    BryanT _

      I am using Monarch V6.

       

      I have a text file report which contains what is effectively 3 different reports in 1!  The layout of the 3 sections is not consistent, so I can't (AFAIK) easily do 1 model to extract it.  I can only set up one detail template when I think to do what I want, I'd need 3, as the 3 sections do not relate to each other like normal append templates would be able to sort out.

       

      I need to get the data out of the text file into Excel, ideally as 1 spreadsheet.  However I guess I may need to produce 3 spreadsheets, although I was hoping there might be a better way than writing 3 different models & having to run all 3.  If I do have to create 3 models, I have experience of creating batch files so wondered if I could set up 1 batch file that would run all 3 models & create 3 spreadsheets as this would be a pretty quick way of doing it still (i'll need to do this work every day!).

       

      Many thanks for any thoughts!

        • Creating Multiple Reports
          Grant Perkins

          Bryan,

           

          Firstly the easier bit.

           

          Yes I would think you could create a batch file to run 3 different models against the report and produce the separate outputs. Then combine them in Excel. A solid and safe solution.

           

          You would have some more options open to you on the export side if you had version 8 but the 3 model scenario sounds like something that will not be easy to address in a simple way.

           

          But ...

           

          A year or so ago someomne in the forum had a similar request - 3 different report formats in a single output. Turned out that there were similarities in the formats, except for the last 2 to 4 fields across the page.

           

          They looked very different because the sections had been centered on the pages, but shift everything to the left margin and most of the columns matched.

           

          On that basis it was possible to combine a bit of file 'processing' as preparation and then run a single model that captured the data directly in the matching field and also the data in the extra fields but with conditional processing applied to identify where the data should go (i.e. which field) in the resulting table.

           

          There are some other less than obvious approaches that might also be available to solve this problem.

           

          Of course it does very much depend on the reports you have to work with. Any chance of posting a representative sample of the layout? If so we can give some thought to the options available to tackle it.

           

          It may be a dead end but sound like it is worth a look.

           

           

          Grant

          • Creating Multiple Reports
            BryanT _

            Thanks for that.  I've searched through some old forum posts & think I know how to do a batch file with multiple files - looks quite straightforward.  A sample of the report as pasted below, though I'm not sure how it will show formatting wise.  Basically some of the columns in each of the 3 headings do correspond, but others don't.  I've altered the account numbers simply to avoid posting live data & have altered the titles to 1 2 & 3 rather than the full title too.

             

            It could be that my best solution is to create 3 models & do the batch file as above.  If so, is it possible to put the 3 spreadsheets into 1 file (ie 1 spreadsheet with 3 worksheets) as this would make it a lot more user friendly?

             

            Thanks for help so far

             

              1

              -


             

              Account Number   Stage      B Date   Liab Amount  B Amount    Reason Label      Trans Amount    Current Amount

              -


              -


              -


               -


              -


              -


              -


               -


              4gngn51          ACK         06-SEP-06      1071.02       0.00    Liability Change      -403.10          572.92

              gnn2051          ACK         06-SEP-06       257.63       0.00    Liability Change        -7.49          165.14

             

              2

              -


             

              Account Number   Stage   B Date     B End     Current Stage  Reason  Reason Label     B Amount

              -


              -


              -


                 -


            -


              -


              -


              -


               4nn3631         ACK       06-OCT-06     22-NOV-06  ACKRET          OR      ACK Return        347.43

             

              3

              -


             

              Account Number   Stage   Liab Amount   B  Date       B  Amount   Reason Label    Changes   Liab Now

              -


              -


              -


              -


              -


              -


              -


              -


              4xvcx71          ACK          300.11    06-OCT-06       205.11     ACK Cash        -37.00      0.00

              4xcv758          ACK          336.76    06-SEP-06         0.00     ACK Cash        -60.00    131.76

            • Creating Multiple Reports
              BryanT _

              oh well, looks like the format came out a mess, so not easy to see what I'm getting at at all!

              • Creating Multiple Reports
                BryanT _

                actually the batch files aren't as simple as I thought!  I've tried an experiment with some existing models atch files & although both files work perfect when run on their own, they won't work when combined into 1 batch file or when I set up a 3rd batch file to run them, all that happens is the first batch file runs ok & the 2nd file does not run

                 

                eg

                batchfile1

                "C:Program FilesMonarchProgramMonarch.exe" "H:MonarchDatactp333.txt" "C:Program FilesMonarchModelsctp165mailshotab.mod" "H:MonarchDatactp165.xls" /T

                 

                batchfile2

                "C:Program FilesMonarchProgramMonarch.exe" "H:MonarchDatactp165.txt" "C:Program FilesMonarchModelsctp165b.mod" "H:MonarchDatactp165a.xls" /t

                 

                batchfile3

                H:MonarchDatactp165.bat H:MonarchDatactp165mail.bat

                 

                Thanks

                • Creating Multiple Reports
                  Grant Perkins

                  Bryan,

                   

                  Is this pretty much how each of the sections look?

                   

                  Frustratingly close but column positions swapped and so on?

                   

                  Originally posted by BryanT:[b]

                  [font="courier"]  1

                    -


                   

                    Account Number   Stage      B Date   Liab Amount  B Amount    Reason Label      Trans Amount    Current Amount

                    -


                    -


                    -


                     -


                    -


                    -


                    -


                     -


                    4gngn51          ACK         06-SEP-06      1071.02       0.00    Liability Change      -403.10          572.92

                    gnn2051          ACK         06-SEP-06       257.63       0.00    Liability Change        -7.49          165.14

                   

                    2

                    -


                   

                    Account Number   Stage   B Date     B End     Current Stage  Reason  Reason Label     B Amount

                    -


                    -


                    -


                       -


                  -


                    -


                    -


                    -


                     4nn3631         ACK       06-OCT-06     22-NOV-06  ACKRET          OR      ACK Return        347.43

                   

                    3

                    -


                   

                    Account Number   Stage   Liab Amount   B  Date       B  Amount   Reason Label    Changes   Liab Now

                    -


                    -


                    -


                    -


                    -


                    -


                    -


                    -


                    4xvcx71          ACK          300.11    06-OCT-06       205.11     ACK Cash        -37.00      0.00

                    4xcv758          ACK          336.76    06-SEP-06         0.00     ACK Cash        -60.00    131.76 /font[/quote][/b][/quote]There are ways to approach this as a single model I think, assuming that the real 1,2 and 3 section identifiers give something that can be used to tag where the record came from and, therefore, what the format of the line should be.

                   

                  If the answers to the above questions are both positive I'll have a deeper look at it to see if my thinking can be turned into a model using V6.

                   

                  Let us know.

                   

                   

                  Grant

                  • Creating Multiple Reports
                    BryanT _

                    Hi, yes that is it format wise, & like you say, frustratingly close as some of the columns are identical & under each other, some are identical & in different places, & some are totally different.  There is no way that we can get the report altered to a more friendly format for what we want it for unfortunately.  The real section headers are a line of text where the numbers 1 2 & 3 show in the report, eg "Direct Receipts Whilst At Stage" & "Movements At Stage"

                     

                    Many thanks

                    • Creating Multiple Reports
                      Grant Perkins

                      Hi Bryan,

                       

                      I'll outline my thinking to you so you can get a feeling for a possible approach.

                       

                      Your section header (1,2 and 3) can be used as appends - most likely page headers I would guess - to establish a 'Record Type' where the line layout of each record type is known.

                       

                      Field positions in the detail lines for each section look like they are fixed so you have a known starting point and length for each field on a line. If that is not the case then a relative position for each field should apply. We will be able to work with either I think.

                       

                      If you can confirm that the full report has, and will continue to have in the future so far as you know, a consistent format for each section then we have some useful possibilities.

                       

                      For the detail I would simply create a one line template that grabs every line. The first 2 columns seem to be consistent. There is a slight anomaly of position for the second section data start but I suspect this is an edit issue.

                       

                      So, when defining the fields paint in the "Account Number" and "Stage" fields separately (they seem to be consistent to all sections) and then a single field for the entire maximum possible length of the rest of the line. You can then slice and dice the long field to extract the separate data fields, based on either the known start and end positions of each field in the line for that section OR using a relative position technique.

                       

                      The former method is probably the simplest since you can use the SUBSTR() function in a calculated field. Make this part of an IF() function which uses the Section Identifier to determine the layout expected and it should be relatively easy to extract each field.

                       

                      If you create a simple calculated field to isolate each field for each section along the lines of;

                       

                      if(="xxxxx", SUBSTR(,startposition, length)," ")

                       

                      You will get a field that is populated only if the section matches and there is data in the field. Otherwise it will be blank.

                       

                      Once you have each field separated you can more easily create a nested IF() formula do cover all the options in a single field, where the field is common to more than one sections' line format, and discard the working fields - if you are brave enough to do so with future maintenance in mind!

                       

                      The "B Amount" and "Reason Label" field in sections 1 and 3 look like they may share a line position but in the sample are shift slightly. If they do share than the same formula will work for each section on a fixed position basis. If they don't share a position they will need to be separate but at least the formula can be copied and amended for whichever you define second!

                       

                      The template will probably trap some lines you don't want. I have assumed that it might be tricky to come up with a trap that excludes the field title row and the dashed lines (the latter useful as I suspect they give you field size and position info?). Therefore you will probably need to include these but then filter them out of the table. That does not look too difficult to do.

                       

                       

                      If by any chance the field positions are not fixed (e.g. they can shift across the page according to the size of the data fields being reported) then you will need to process them as RELATIVE positions in the field.

                       

                      The idea will be similar to the fixed position process but rather than using SUBSTR() for the extraction you will need to look at LSPLIT() and RSPLIT(), most probably the LEN() function, almost certainly the TRIM() related  functions together with a few of the other functions which allow conversion of characters to numeric and vice versa whilst manipulating the data.

                       

                      Most of what you would need to achieve should be possible using a simple LSPLIT() function based on using a " " (space) to separate the fields since it seems that all fields are always populated. (If they are not things get a little more interesting!). Since the fields have a variable number of spaces between them the TRIM related functions will come into play as well.

                       

                      However the "Reason Label" field in the middle of the line adds some complication since it clearly has some (all?) descriptions with more than one word. Might the "Current Stage" field also have the same?

                       

                      The extra space, if consistent, can be worked around quite easily but if the descriptions have a variable number of words the use of a relative position SPLIT based on spaces becomes a little more complex.

                       

                      On the basis that at this point I think your solution may be served by the fixed field position format suggested above I will not attempt to offer a full description of a 'relative position' approach. We can get to that later if necessary.

                       

                      Does this help at all?

                       

                      If you are already familiar with the use of the functions mentioned it should make some sense. If not it would be worth checking them out in the Help file - a very useful resource for function information.

                       

                      Let us know how you get on. Send me a Private Message if you have any problems which you would prefer to deal with outside the public forum.

                       

                      Regards,

                       

                       

                      Grant

                      • Creating Multiple Reports
                        BryanT _

                        Hi Grant, what you've written does look incredibly difficult!

                         

                        I wonder whether an easier solution would be to write 3 easy models, & assuming this is possible, write 4 batch files to turn the reports into 1 spreadsheet?  So basically 1 batch file for each model, & a 4th that runs the other 3 - can it be done so that just one spreadsheet is created with 3 worksheets or would it have to be 3 spreadsheets?  This solution might be of some interest to other users too?

                         

                        Thanks

                        • Creating Multiple Reports
                          Grant Perkins

                          Hi Bryan,

                           

                          If the sections of the report each have their own fixed format it is not difficult. If they vary and we need to use the relative positioning it is more of a challenge to set up but that is a one-off task.

                           

                          I'm no Excel expert so I would much prefer to work on Monarch.

                           

                          I think your multiple exports to Excel would be easier with V7 or V8 Monarch and save some effort. But in theory I guess if you can manipulate the various Excel outputs which result you can get everything into a single sheet. The thing is though that if you take all the fields from each section you will need you 'master' sheet to reflect all of the fields and so define how each field in it is updated from the different exports.

                           

                          If the section layouts are fixed (i.e. the fields start and end at the same place every time the report is run) it would not take long to put a model together. Much easier to do than to describe.

                           

                          I would do it using your posted sample and send the result for you to try if you can confirm the fixed nature of the original and whether the slight anomalies in the format you posted are a real anomalies or the effects of editing the report lines to anonymise the data.

                           

                          If you let me know the answer and send me your email address in a Private Message I'll see what I can do for you.

                           

                          HTH.

                           

                           

                          Grant

                           

                          [size="1"][ December 01, 2006, 06:17 PM: Message edited by: Grant Perkins ][/size]

                          • Creating Multiple Reports
                            Dieter _

                            Hi,

                             

                            This might be a little off subject, but I have several models for a folder containing several files.  I have created batches that run the models and export the verified files into excel.  What my ultimate goal is to run the model for the entire folder, which contains nearly 100 files.  I want to do this all at once instead of changing the file name for the batch I created.  Is there anyway that I can run a model for a folder, rather than a file?  That way all of the information is on one excel spreadsheet and I have to do as little manipulation for automation?

                             

                            Thanks

                            • Creating Multiple Reports
                              Nick Osdale-Popa

                              You can do something like the following to process a group of files in a folder (obviously, set your monarch commands/folder appropriately)

                               

                              Note: this will only work in Win2000/XP

                               

                              [font="courier"]@echo off

                              SETLOCAL

                              For %%i in (c:     emp.) Do Call monarch "%%i"

                              ENDLOCAL

                              GOTO :EOF

                               

                               

                              :monarch

                              "C:Program FilesMonarchProgramMonarch.exe" /rpt:%1 /mod:"C:Program FilesMonarchModelsyourmodel.mod" /exp:"yourexport.xls" /T

                              GOTO :EOF[/font][/quote]I don't do any scripting/batch files for monarch, so I don't have the appropriate Monarch string to export, but this should give you a base idea to work with.

                              • Creating Multiple Reports
                                Dieter _

                                Nick,  thanjs for your quick response.  I am still confused on the methodology of running a model for en entire folder.  I have the batch listed as you do, but the "echo" and "SETLOCAL" terms are not familiar.  Plus when I go to the run:command line, my script is too long to place all of the parameters, unless I am doing something wrong.  Here is an example of the current script I have for a sinfle file.  I need to run a model for the entire folder that contains the files for one time. 

                                 

                                "C:Program FilesMonarchProgramMonarch.exe" /rpt:"M:SSIAPPPer Se payer rejectsHARDCOPY 1500 (OHHCB)OCHOHHCB.305" /mod:"C:Program FilesMonarchModelsCommercial Hard Copy Direct.mod" /exp:"G:CBSREI2006DAVE     esting monarchHARD COPY.xls" /T

                                 

                                How do I run a model for multiple files in that folder or how do i run a model just for a folder?

                                 

                                Thanks

                                • Creating Multiple Reports
                                  Nick Osdale-Popa

                                  The code I provided is for a dos batch file.

                                   

                                  @echo off

                                   

                                  Supresses the batch lines from appearing on screen

                                   

                                  SETLOCAL/ENDLOCAL

                                   

                                  Are used if you are setting any environment variables. They'll be used in their own "memory space" if you will and automatically be removed with the ENDLOCAL statement.  This was introduced to the command interpreter in Windows 2000 and above.  I use them as standard practice even if I'm not setting variables, "just in case".

                                   

                                  Also introduced were the calling of labels in batch files and using GOTO :EOF (End Of File).  The above code would actually be set up as two batch files with the monarch portion being in a separate batch file.

                                   

                                  You can set up your batch file as such:

                                   

                                  [font="courier"]@echo off

                                  SETLOCAL

                                  For %%i in ("M:SSIAPPPer Se payer rejectsHARDCOPY 1500 (OHHCB).") Do Call monarch "%%i"

                                  ENDLOCAL

                                  GOTO :EOF

                                   

                                   

                                  :monarch

                                  "C:Program FilesMonarchProgramMonarch.exe" /rpt:%1 /mod:"C:Program FilesMonarchModelsCommercial Hard Copy Direct.mod" /exp:"G:CBSREI2006DAVE     esting monarchHARD COPY.xls" /T

                                  GOTO :EOF[/font][/quote]This will read ALL files in the provided directory passing the pathname and file (%%i) to the monarch routine.

                                   

                                  The monarch routine reads it's first parameter (%1) and passes it to the monarch program as a report (/rpt:%1).

                                   

                                  GOTO :EOF is not needed here, but is good practice to use at the end of subroutines as this.

                                   

                                  Once the FOR command is done processing, a GOTO :EOF is issued so that it does not try to run the monarch routine.

                                   

                                  There are many new commands in Win2K/XP that you can set up some really elaborate batch files. You should see some of the ones I have that have nothing to do with Monarch!    (FWIW, I don't use batch files for Monarch, I tend to stick with VB/VBA).

                                   

                                   

                                  P.S. If you're not comfortable with creating Batch files, I guess I probably should have prefaced my previous post that this requires some "advance batch command" knowledge.

                                  • Creating Multiple Reports
                                    Dieter _

                                    Nick,

                                     

                                    I used the batch that you created for my file, pasted it into the command prompt, and im not exactly sure what happened.  You are correct to assume that I don't have that much experience with batch commands.  I have taught myself how to do this, but at a basic level.  Am I doing this right by copying and pasting at the command prompt?  Should I do it step-wise as you have listed it?  What do i need to do to make this process work correctly?  What do you think is the error that I made, when i went ot search for the files that were run through the Monarch files and did not get them? I apologize if this is remedial, but that is why I am here seeking your advice.

                                     

                                    Thanks