14 Replies Latest reply: May 15, 2014 10:05 AM by Grant Perkins RSS

    Novice Question

    Terry007 _

      I have a report that is in the following format (simplified)  where A represents some text and the x's, y's, z's and t's represent numbers....

       

      Being a novice, I selected the entire "area" as a detailed type and then selected each of the groups of numbers individually.  The output form is not what I need (data extracted is fed into another app).  I've shown the output desired at the bottom...can someone suggest how to achieve this?

       

      Thanks

       

       

       

             A

       

      x1          y1

      x2          y2

      x3          y3

      .           .

      .           .

      .           .

       

       

           B

       

      z1          t1

      Z2          t2

      z3          t3

      .           .

      .           .

      .           .

       

       

      Output table desired

       

      A x1  x2  X3  .  .  .

      A Y1  y2  y3  .  .  .

      B Z1  z2  z3  .  .  .

      B t1  t2  t3  .  .  .

        • Novice Question
          Olly Bond

          Hello Terry,

           

          That looks like a classic multi-column region. Define an append template for A & B. Then turn on Multicolumn region (Report window, template menu). Define 2 columns to fit. Define a detail template and select one and only one of x1 or y1.

           

          There are other tricks to cope if the data doesn't line up in columns nicely, or has other anomalies. And to get your output, you might need some tricks with a sort order and then a summary, possibly needing a two stage process.

           

          But yes, it is possible.

           

          Best wishes,

           

          Olly

            • Novice Question
              Terry007 _

              I was thinking about the multicolumn but I was concerned about selecting only one of the sets of data.....not sure how to deal with the other set?  Would I create another template?  How then to run both templates against the data and get the one set output with multiple rows as desired?

                • Novice Question
                  Terry007 _

                  I need a strategy for getting the desired row based output in one output file.  Not sure how to make this happen.....never done multiple templates and had them work together to get a combined result...not sure that even works....help

                   

                  thanks

                   

                  Terry

                    • Novice Question
                      Olly Bond

                      Hello Terry,

                       

                      Email me at olly@greenbar.info[/email] with the report file and I'll send you back an example model.

                       

                      Best wishes,

                       

                      Olly

                        • Novice Question
                          Grant Perkins

                          Terry,

                           

                          I would go with Olly's offer but in the meantime can you tell us whether the columns have any headings and the rows have any descriptions,

                           

                          As it stands it looks a bit bare. If it is bare with no further helpful information for grouping purposes I think I would be tempted to avoid MCR and just extract each column as a block of text into a single field and then present that at an output or, if that is too 'raw', use a calculated field and probably the Textline function to create a new representation of the data via concatentation of each line. If the data are for distinct column (perhaps a name tag to the left of the row?) there would be no need to concatenate.

                           

                          The thing is that the total report usually provides some help for this where as the data fields of interest, standing alone, often do not.

                           

                          Now, these ideas probably move you well beyond the realms of 'novice' rather quickly but that is not unusual we find! Indeed it is almost normal if you look back through forum posts ....

                           

                          Fear not, the individual parts that go to make up the whole solution are not difficult and having someone walk you through the steps will provide a rapid learning exercise that should illuminate a lot that is most likely currently looking a little dark to you. Life then gets a lot easier and a deal more interesting, or so many people remark.

                           

                           

                          HTH.

                           

                           

                           

                          Grant

                            • Novice Question
                              Data Kruncher

                              Greetings all,

                               

                              As Grant suggested, I was able to develop a solution, albeit moderately inelegant but workable, without using MCR. If you had v10 available with its Column() function I suspect that this would be much easier, but alas such does not appear to be the case.

                               

                              Here's the sample report I used:

                              A                         

                                     1               4  

                                     2               5  

                                     3               6  

                               

                              B                         

                                     7              10  

                                     8              11  

                                     9              12  

                              /code

                               

                               

                              Here are the steps:

                              Build a one line detail template, using Advanced End field on none of the above. I creatively named the fields Col1 and Col2. Make sure that they're Character fields.

                              Append your A/B Labels field.

                              /LISTThis should net you 2 rows in the Table, based on your limited sample.

                               

                               

                              Now in the Table:

                              Create a new calculated Numeric field, Field1 with this formula:

                              [code][SIZE=2]val(textline(Col1,1))[/SIZE][/code]

                              Duplicate Field1 to create fields Field2 and Field3 changing the ",1" in the formula to ",2" and ",3" respectively.

                              Hide the Col1 and Col2 fields.

                              Define a project export which will export the table to an Excel file if you have Monarch Pro, or a text file if you have Standard. Elect to overwrite the file when the export is performed.

                              Save the model and the project files.

                              Run the project export.

                              /LISTNext up: handle the other column. Repeat the steps above, substituting Col2 for Col1 in the calculated fields. Replace the existing project export with one that appends data to the first export. Again, save this as a new model and a new project, and run the project export.

                               

                              Now in a third Monarch model, open the text file and model it as you would normally, or better, open the Excel file as a database source (Pro version). Now, if necessary, go to the Table window and sort it based on the Label, and you're done; ready to export to its final destination. Save the third model and the third project.

                               

                              My final Table window looks like this:

                              Label Field1 Field2 Field3[/FONT]

                              A          1      2      3[/FONT]

                              A          4      5      6[/FONT]

                              B          7      8      9[/FONT]

                              B         10     11     12[/FONT]

                              /code

                               

                              Of course in the real world, you'll have more than 3 fields to define to build it out horizontally. Keep in mind that Monarch only allows 255 fields in the Table, so after all this, it might not be a useful approach for you, and in that case it's back to the drawing board.

                               

                              But if it works for your data, great. Make your life easy and automate the process with a little batch file which runs all three Monarch processes for you.

                                • Novice Question
                                  Terry007 _

                                  Wow, Olly, Grant and Kruncher......thanks for all the help and suggestions.  I'll spend some time this weekend with all of your suggestions and let you know how it goes.....thanks a lot for the suggestions and offers of help...you guys are the greatest!!

                                   

                                  Terry

                                    • Novice Question
                                      Terry007 _

                                      Kruncher

                                       

                                      I tried your suggested approach.  It worked....however I have a couple of questions:

                                       

                                      : after I exported the first col1, I saved the project and model as Test1 and opened it up again but modified the fields as you suggested and made an Export2 and ran it.  After running it, I saved it as Test2.....

                                       

                                      THEN TO TEST IT AGAIN:

                                      I  opened up Test1 project again and did the associated Export1, closed it and opened up Test2 and did the associated Export2...closed it and reviewed the file which was perfect.

                                      1) IS THIS HOW YOU ENVISIONED Running it by having two projects each with a model and running one after another? or was there a simplier process where there is actually one project with two models?

                                       

                                      Terry

                                        • Novice Question
                                          Data Kruncher

                                          Yes, this is a multiple pass solution, meaning that you'll need to run the projects one after another to get the desired end result.

                                           

                                          A small batch file will let you execute this easily, something like:

                                          "C:\Program Files\Monarch\Monarch.exe /prj:"C:\Projects\Test1.xprj" /pxall

                                          "C:\Program Files\Monarch\Monarch.exe /prj:"C:\Projects\Test2.xprj" /pxall

                                          "C:\Program Files\Monarch\Monarch.exe /prj:"C:\Projects\Test3.xprj" /pxall

                                          /code

                                           

                                          A Monarch project file can only employ one model file. Thus the need for multiple projects.

                                            • Novice Question
                                              Olly Bond

                                              Hello Terry, hello Kruncher,

                                               

                                              Forgive me for butting in, but I'm a bit perplexed. This works OK if you have a known (small) maximum to the number of rows in your detail trap, as you only have to build val(textline(Colx,y)) for y values from 1 to n.

                                               

                                              Surely using a one line detail for each row and MCR would be a more flexible approach, in case the number of rows varies next time the report is run?

                                               

                                              Best wishes,

                                               

                                              Olly

                                              • Novice Question
                                                Terry007 _

                                                Kruncher/and others

                                                 

                                                Yes...I set up a batch file and it works great....thanks for all your all and thanks to all others that had suggestions and input here.

                                                 

                                                Terry

                                                  • Novice Question
                                                    Terry007 _

                                                    Olly/Kruncher

                                                     

                                                    Ok....I'll look into the more stable approach as well.....thanks for the feedback...

                                                     

                                                    Terry

                                                      • Novice Question
                                                        Grant Perkins

                                                        Olly/Kruncher

                                                         

                                                        Ok....I'll look into the more stable approach as well.....thanks for the feedback...

                                                         

                                                        Terry[/quote]

                                                         

                                                        Terry,

                                                         

                                                        If you have something to the LEFT of the columnar rows that describes what they are (most reports would) that the MCR can be a much more elegant solution (as Olly points out) for reports with variability.

                                                         

                                                        On the other hand if there is nothing much presented to help ID the row (or indeed the columns) and the number of rows has a know limit and order (if appropriate), both of which can be 'hard coded' into a monarch model then making it visual and simple may suffice for your purposes.

                                                         

                                                        Both approaches are valid and useful to have in the Monrach User Toolkit. Whichever way you go you are winning by learning bit do test for the likelihood of change or divergence nullifying the strategy you adopt. Inputs can be very capricious things.

                                                         

                                                         

                                                        Grant