6 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Combining Reports

    Edmie _

      I have a couple of reports that I need to combine. They have a couple of common fields but there is one field I want to include that one report has, the other one doesn't have. Example:

       

      Report 1:

       

      Equipment VendorNo Station AmtReceived

       

      Report 2:

       

      Equipment VendorNo AmtPaid

       

      The goal is to combine the reports to look as follows:

       

      Equipment VendorNo Station AmReceived AmtPaid

       

      What would be the best way to accomplish this? Can it be done in Monacrh? I have v.7.0.

        • Combining Reports
          Grant Perkins

          Hi Ed,

           

          V7 Standard or V7 Pro?

           

          Are the reports the same layout except for the position of the value field you want to combine into one version of the report?

           

          How are you going to present the results? A data table? A summary? A printed version of the original report but with the additional data added?

           

          Can there be complete LINES that exist on one report but not the other? If so how do you need to deal with that?

           

          Sorry for the questions rather than answers but the additional information will help us to decide which way is the best one to follow.

           

          Grant

          • Combining Reports
            Edmie _

            V7 Standard or V7 Pro?

            V7 Standard

             

            Are the reports the same layout except for the position of the value field you want to combine into one version of the report?

            No, the reports are not the same layout but we are modeling them to have the same layout.

             

            How are you going to present the results? A data table? A summary? A printed version of the original report but with the additional data added?

            I'm not sure what would be the best way to present it. The goal is to take three reports with one common element and combine the amounts and data fields that are unique between them.

             

            Can there be complete LINES that exist on one report but not the other? If so how do you need to deal with that?

             

            No

            • Combining Reports
              Grant Perkins

              Originally posted by ecastill:

              V7 Standard or V7 Pro?

              V7 Standard

               

              OK. If you had Pro I would be suggesting you choose one file as a master and then process the other(s) into a unique key into your first file and the data field(s) you want to add. Two links would then do the job. I think.[/i]

               

              Are the reports the same layout except for the position of the value field you want to combine into one version of the report?

              No, the reports are not the same layout but we are modeling them to have the same layout.

               

              Interesting that you are modelling them that way. I assume you mean that they will have compatible field sizes and keys and so on?[/i]

               

              How are you going to present the results? A data table? A summary? A printed version of the original report but with the additional data added?

              I'm not sure what would be the best way to present it. The goal is to take three reports with one common element and combine the amounts and data fields that are unique between them.

               

              Ok, there are two or three possible approaches which come to mind but the starting point for each is rather dependent on where it all has to end up. I'll try to give it some thought and post some ideas tomorrow.[/i]

               

              Can there be complete LINES that exist on one report but not the other? If so how do you need to deal with that?

              No

               

              Are you sure? You previously mentioned 'Station' which appears in one structure and not the other. Is it really in both? If not I'm not sure how you would make a satisfactory link to a per station line with AmtReceived (presumably multiple stations possible) when there is no station in the AmtPaid report.[/i]

               

              /b[/quote].... thinking ....

               

               

              Grant

              • Combining Reports
                Edmie _

                I am not sure that modeling the reports to the same format is the best approach but is the one I am familiar with so that's the approach we have been using. I keep researching to see if I can find easier ways but not familiar.

                Yes, they all have compatible field sizes, etc.

                 

                =====================

                 

                How are you going to present the results? A data table? A summary? A printed version of the original report but with the additional data added?

                I'm not sure what would be the best way to present it. The goal is to take three reports with one common element and combine the amounts and data fields that are unique between them.

                -

                Ok, there are two or three possible approaches which come to mind but the starting point for each is rather dependent on where it all has to end up. I'll try to give it some thought and post some ideas tomorrow.

                -

                The approach I had originally thought of was to create a model to combine all three reports to then create a summary and bring together the fields in one report that are not in others. The problem I ran into is that because one report has one field and the other doesn’t it just creates multiple lines for each common field. For example

                Equipment Station AmtPaid AmtRec

                A           1      1200

                A                          1400

                 

                The example I am providing has been sinificantly simplified. If you agree I can send you an e-mail with a print screen for you to get an example of the files and what I am trying to accomplish.

                ===========

                 

                Can there be complete LINES that exist on one report but not the other? If so how do you need to deal with that?

                No

                -

                Are you sure? You previously mentioned 'Station' which appears in one structure and not the other. Is it really in both? If not I'm not sure how you would make a satisfactory link to a per station line with AmtReceived (presumably multiple stations possible) when there is no station in the AmtPaid report.

                -

                Ok, now I understand what you mean. Well, based on the example provided above it seems like that is the case. Is there a solution?

                Again, this example has been simplified. I can send you a print screen for you to view it if you think it may help.

                • Combining Reports
                  Grant Perkins

                  Ed,

                   

                  I have something very similar combining 3 files (rather than reports) to produce one file with the various fields for each record plucked from the separate fields and combined in the end product.

                   

                  However the files are Tab delimited and I used Pro to link things in stages because I needed to create some pseudo records for some instances of data in some files. (In the end the number of records and starting from Tab delimited files meant that it was easier to create the base combined records in Access and then use Monarch to read in the Access records and add an extensive array of calculated fields and so on. There are a lot of records ...)

                   

                  I'll happily have a look but would it be possible to send an extract of a few pages from the report file(s) rather than screen dumps? Pages that will link (at least in part) of course would be best.

                   

                  It's rather late (or early depending on your view point) here so I need some sleep. I will send you a private message with my email address. If you can send what you can and an explanation of anything that is not obvious from the reports but certainly what you want linked to what and how you think it would be best to do it, I will have a look at it as soon as possible after receipt.

                   

                   

                  Grant

                  • Combining Reports
                    Grant Perkins

                    Ed,

                     

                    I have the files. An interesting challenge.

                     

                    Your text file is in effect a summary, The values it contains can relate to one or more records in the other file(s).

                     

                    So the report layout you are seeking means that some, but not all, lines would need to display the summary per PRON amount for each detail line. I assume the detail lines are per Station (or similar).

                     

                    We couild turn that around the other way, more like a traditional report perhaps, and use the summary file (the Monarch text file) as the 'master', and attach to it the 'detail' lines from the other files on a 'per station' basis.

                     

                    Obviously that would give you a somewhat different report/excel table layout to the one you are looking for.

                     

                    If the Text file (Monarch) has the Station info in it as well then that could be extracted and attached on a one to one basis.

                     

                    As the other two files are already Excel files the Pro option to extract data from them via direct links would be very simple and quick to implement. However it would still not answer the question above. In that respect, even with Pro, to get the format you seek would seem to require the Monarch Text extraction being fed to an Excel worksheet (or a delimited text file) via export  and then linked back to the other files.

                     

                    Any thoughts?

                     

                     

                    Grant