7 Replies Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    Multi-Column Help

    Greg_Goodman _

      I had occassion to need to use the multi-column feature and am running into something I need help with...

       

      Say a report is 13 columns - 1st column is 'code' and 12 columns each is data. Each column has a header "Date" (ie, col 2 header = Jan-06, col 3 header = Feb-06, etc).

       

      When I try various ways to setup traps, the append to pickup the Header dates is doing some flaky things - most of the dates are returning Dec-06 (like 95% of 60k records).

       

      What am I doing wrong? Examples in the manual don't address append considerations using multi-column traps

       

      Thanks

        • Multi-Column Help
          Grant Perkins

          Greg,

           

          MCR (Multi Column Region)is an interesting feature but sometimes has limitations - it depends how the report has been structured.

          Sound like you have 12 'month' columns which should be in the MCR. Your first 'column' does not count as a column in MCR terms BUT the field for the 'code' does In fact it might make a very good trap for the detail records (the values of which will be contained in the columns.)

           

          Once you have set the column values for start position and width you need to think of the columns as being 12 thin pages which happen to have been been printed side by side.

           

          The column headers need to be included in the MCR BUT IDENTIFIED as part of an Append Template. Just pick the Column Header from column one (the first month column) as the field for the template (it would be unusual to have more than one field in that template)

           

          So your detail template would be the value in the first column for a genuine 'value' field plus the value in the 'code' field.

           

          The template for the column header (column one) would trap only the column header row, which must be within the MCR for this idea to work, and requires only the header for the first column to be selected.

           

          Note that you can control the start and end points of the MCR (which will then repeat within the report) and most reports are likely to require this sort of control to eliminate spurious inputs for other lines in page headers or whatever.

           

          There can be anomalies.

           

          I have seen examples of reports where the 'detail' columns were not evenly spaced, making the definition of standard same-width columns rather difficult.(!)

           

          Sometimes column header lines conflict with other data positions.

           

          If you get very stuck I will be happy to take a look at it for you if you can provide a sample layout. Send me a PM if you are unable to resolve the problem and do not feel that it is appropriate to publish a sample layout in the forum. I will send you in return my email address if you can provide a copy sample off line.

           

          It is possible that there is something about the report which makes it unworkable for MCR but mainly report layouts I have come across can be made to work!

           

          HTH.

           

          Grant

          • Multi-Column Help
            Greg_Goodman _

            Grant thanks for the quick response.

             

            The report design is currently self inflicted and can be changed if needed. I'm a long time Monarch user and designed the layout (CSV to Excel to Fixed space prn) thinking the multi-column would be a good thing to use. There's no issue with column spacing / trapping of detail - the detail records are fine

             

            There are 2 append templates needed. 1st, right above the detail records is a group code. It's not multi-column. It is set as 1st append. 2nd is the row of dates as the 2nd header - it needs to append to the details in multi-column form.

             

            I've tried including / excluding the row inside the multi-column Detail that is working and still feel like I'm getting odd results.

             

            The report yields the correct # of records, around 64k lines, but all "months" of record counts except the 12th month,"Dec-06" (also the last line of the multi-column trap), are exactly 157 per month with all the rest of the records returning "Dec-06" as the append value - Summary comes in handy sometimes...

             

            I'll take a look at the model in the morning with any suggestions...Seems like it should be simple

             

            Are there issues creating multi-column appends in a certain order?

            • Multi-Column Help
              Grant Perkins

              Hi Greg,

               

              Sorry, didn't mean to sound patronising in the response but given the open nature of the forum, the interesting nature of MCR processing and some of the things I have seen in reports which make life tricky I thought, in the early hours of the morning, that I had better try to keep the post structured and assume nothing!

               

              Did that work?

               

              You analysis does sound odd though in some ways the description of the problem sounds familiar - I may have come across something similar before but where is not coming to me at the moment. I will let it bubble for a while and see if I come up with the answer.

               

              If you have control over the report could you try swapping the column header row for the months with the group code row to see what differences that makes. I always feel happier if a column heading row immediately precedes the columns rather than having something else between them.

               

              The other thing that occurs to me is that the trap for the heading row append may be ambiguous without it being obviously so. However, if it repeats regularly and is not subsequently compromised as a trap by being overrun with a prior trap on subsequent pages - for example a page header trap which for some reason is not always the same number of lines - the proportion of odd results might be expected to be the other way around - a few anomalies rather than mostly anomalies.

               

              I guess the 157 number should give a clue somehow. 64k records suggests the report has around 5300 rows (12 records per row if fully populated). If you divide the number of rows by 157 does it give you something clses to the number of effective lines on each 'page'in Monarch model definition terms? Or perhaps the number of  group codes in the report sample?

               

              Obviously I am taking some wild guesses here and my mental image of the report layout could be miles away from what it is. If you are in a position to share a sample of the report privately I will be very happy to have a look to see if anything comes to mind. You could zip the whole thing up in a prf if you like and mail it to me. Send me a PM with your email address if you want to go that way.

               

              I have a few things on which will keep me busy most of today but could probably have a look this evening if it would help. (That would be afternoon your time!)

               

              HTH

               

              Grant

              • Multi-Column Help
                Greg_Goodman _

                Grant, as I reviewed this am, the column headers were not really page headers but report headers (not repeated on any pages, only on 1st page). Naturally, multi-column won't work w/ Page header only appends. Seemed to work ok until about 4th "page" where things broke down.

                 

                Things work fine now that I went back to Excel, played around w/ column width, font, alignment, and page setups (esp. 'repeat rows on each page'), printed to PDF and adjusted model.

                 

                Scaling on PDFs seemed to cause the biggest grief as by the time I was out to 12th column, scaling had changed column positions a byte or 2...

                 

                Thanks for your help - I figured it was self-inflicted. A stronger note on multi-column working w/ "page" headers vs. report headers might have helped - but I will remember for a while !!!

                • Multi-Column Help
                  Grant Perkins

                  Hi Greg,

                   

                  I will confess that the column heads being report headers had not occurred to me.

                   

                  If you had defined them as the page header, and they didn't repeat, then the 4th page problem might relate to the 256 row enforced page break? Could that also represent the 157 detail records where things seemed to work?

                   

                  As for the PDF column shift - are you using 8.01? And is the font exported from Excel a fixed font? I would be interested to know for future reference.

                   

                  Is there an option for you to save from Excel as a delimited or prn file rather than a pdf if the pdf issue cannot be reliably resolved for some reason?

                   

                   

                  Grant

                  • Multi-Column Help
                    Greg_Goodman _

                    Grant, have been too busy to get back on this subj but had to a couple days ago...

                     

                    I did decide to try printing from Excel using the Generic Text print to file so that I had page headers to deal with for appends and everything worked well when it worked...I worked out a standard set of column widths, margins, etc. in Excel and sometimes I could something usable and sometimes I didn't. Settings included 'legal' size as paper source.

                     

                    Kept trying w/ inconsistent results and once I had what I needed, I had to turn to other things. But today I decided to try some more to figure out what the heck was going on. I found MS article KB184057, 'Printing Wide Carriage w/ Generic/Text Only Print Driver'. Learned something new and this did the trick. Now everything works 100% of the time w/ this data set printing to generic text from Excel.

                     

                    Just wanted to thank you for your help and pass this along

                    • Multi-Column Help
                      Grant Perkins

                      Greg,

                       

                      My apologies   :eek:    redface.gif[/img] 

                       

                      I should have mentioned the print to generic text file requirement as a preferred route, especially if you have a wide 'report'. It's one of those things are easy to assume that people are aware of and so forget to mention when in fact it is not common knowledge at all and may be quite obscured from most users. So it should be mentioned at all times if only for others who may read the post later.

                       

                      Of course I only know about it because I had the exact same problems a few years ago and a colleague, who knew everything there was to know thankfully, explained it to me. In some detail. Several times. I finally remembered and that kind of sticks with you after the repetition! (And the frequently raised eyebrows with eyes following them ...)

                       

                      Anyway, glad you got it sorted.

                       

                      All the best.

                       

                      Grant