5 Replies Latest reply: May 15, 2014 9:55 AM by stembe _ RSS

    How to combine/gather data from multiple reports & generate a single output?

    stembe _

      Hello,

       

      We need to extract specific data from certain reports & generate only one output. Let's say, we want to extract the Customer Name & address from one report; the shipment date from another; and the unit price & qty from the third.

       

      I know that external lookups is one way of doing it based on a key (ex. Customer Number). Is that the only way?

       

      Thanks for any suggestions.

       

      Thanks,

      Suhas

      Monarch Pro 7.01

        • How to combine/gather data from multiple reports & generate a single output?
          Darren _

          You can link multiple files using Monarch and its Join function. Just create a model for each file you want to join. Then in the Table View, use the Create Join function to join multiple files together. It will then ask you for a file to join to.

           

          You can join up to 9 files in Monarch 7 and 7 files in Version 6. The only constant that you need is a key field that is unique in each report, but is the same globally throughout all the reports you are linking together. Hope this helps out!

          • How to combine/gather data from multiple reports & generate a single output?
            stembe _

            Darren,

             

            Thanks for the reply. When you say "use the Create Join function in the Table view", do you mean External lookups or is it something else that you are referring to? I am a little confused.

             

            Thanks,

            Suhas

            • How to combine/gather data from multiple reports & generate a single output?
              Darren _

              Hi Suhas.

               

              "When I talk about "use the Create Join function in the Table view", do you mean External lookups or is it something else that you are referring to? I am a little confused."

               

              To answer your questions simply (I hope!), yes! When you have created your model in the Report View and moved onto the Table View, using the Join function will trigger Monarch to open a window and ask you for an external file to link or join to.

               

              Here's where it got a little tricky for me the first time I was joining files. The first file I create does not join to anything (makes sense). Then the second file I create joins to the first one, the third file I join ALSO links to the first one, with the first file becoming the master file with all the linked fields in it.

              Again, you need one key field that is constant throughout all the reports you are linking together.

               

              Hope I cleared up some confusion...  :cool:

              • How to combine/gather data from multiple reports & generate a single output?
                Grant Perkins

                One point that is worth making about V7 Pro is that the lookups to external sources can be made without all of the sources having the same common key.

                 

                The quickest way I can think of to play with this is to set up an excel spreadsheet with 3 worksheets which will stand in for separate sources.

                 

                Lets say the first sheet has 3 columns like so;

                [font="courier"]1A    1B    1C

                 

                AAA   BBB   CCC[/font][/quote]The second has;

                [font="courier"]2A    2B    2C

                BBB   XXX   YYY[/font][/quote]The third has;

                [font="courier"]3A    3B    3C

                YYY   ggg   hhh[/font][/quote]You can link the second worksheet to the first by specifying that 2A links to the original's 1B. (BBB matches BBB)  and then 'add' the 2B and 2C columns to the table.

                 

                So your table will have

                 

                1A    1B    1C   2B   2C

                AAA   BBB   CCC  XXX  YYY

                 

                Now you can add another link to attach the third spreadsheet to you current table.

                 

                Connect 2C and 3A (YYY to YYY) and then add the fields 3B and 3C to the table to give:

                 

                1A    1B    1C   2B   2C   3B    3C

                AAA   BBB   CCC  XXX  YYY  ggg   hhh

                 

                Now, it could get more complicate than that and require linking keys made up from multiple fields or even Monarch calculated fields ...

                 

                I will leave you to play!

                 

                Grant

                • How to combine/gather data from multiple reports & generate a single output?
                  stembe _

                  Darren & Grant,

                   

                  Thanks for all your help.

                   

                  Suhas