16 Replies Latest reply: May 15, 2014 10:04 AM by Grant Perkins RSS

    Page Breaks, Append, and MCR

    BradH _

      I have an issue using a combo of append for a report header with lines of detail (it's actually using an MCR as well, but that appears unrelated).  This is with Monarch Pro 8.02.

       

      The issue is my report header appears once in the entire report.  Using Append works only for the first artificial 'page' that Monarch defines.  After that, it just repeats a single append record on all following rows.  The "force page break" option in Input options only lets you define values between 10 and 256.  I want this OFF!

       

      Mimicked sample of the report:

       

      Header    May 09   Apr 09  Mar 09

      ID1         1        2       3

      ID2         4        5       6

      /code

       

      Expected output:

       

      ID1  |  May  |  09  |  1

      ID1  |  Apr  |  09  |  2

      ID1  |  Mar  |  09  |  3

      ID2  |  May  |  09  |  4

      ID2  |  Apr  |  09  |  5

      ID2  |  Mar  |  09  |  6

      /code

       

      I have a multi-column region defined that grabs the dated columns, leaving out the ID column.  The Header is defined as Append pulling out the month and year as two separate fields.  The ID rows are a Detail template, pulling out the ID as one field and the value its column as another.  This works as expected until the report reaches a forced page break, then after that the same Month and Year fields appear for every row of output.

       

      Does anyone know of a solution around this besides running through a script to insert headers every X lines (in which case I'd just say 'forget it' and just use awk on this report instead)?

       

      Thanks!

        • Page Breaks, Append, and MCR
          Olly Bond

          Hello Brad,

           

          It was helping solve an awk nightmare that first got me into Monarch - it's nice to be reminded.

           

          It sounds to me that if you're experiencing Page Header / Append oddities with MCR that the issues are related. It's to do with the definitions of when the MCR region begins and ends - and it sounds like you've got it set to start on the Append template and end on bottom of page? I think that's the default - anyway, I'd suggesting experimenting with these options.

           

          Feel free to email the report & model and I'll have a bash - I'm using v9 & v10.

           

          Best wishes,

           

          Olly

            • Page Breaks, Append, and MCR
              BradH _

              Thanks for the response!

               

              And yes, it's definitely how the MCR ends.  I tried adding a couple unique lines to the beginning and ending of the report and trapping templates to them to define as the begin/end points for the MCR.  Unfortunately the options for "Stop at" on the MCR are either "Bottom of page" or "[a template] or bottom of page".  It looks like that forced page break is the issue.  I really don't know if there is a way around it or not.

                • Page Breaks, Append, and MCR
                  Grant Perkins

                  Brad,

                   

                  I think you have hit the 'under the hood' technical boundaries here.

                   

                  The best thing I can come up with so far is make a selection of just the header and create a fiel to be used as an external lookup table for a seconf step in the process - read the main detail lines and parse those into a table then lookup the related column.

                   

                  Now in V10 that would be quite readily available. You would MCR ignoring the header line and add a calculated field for the new Column() function. For each line you would then know the column from which the data was extracted making it relatively simple to perform a lookup to the previously created lookup table.

                   

                  Before attempting any detailed suggestion foor a V8 solution I will wait to see what Olly comes up with. However, in outline, I am thinking of a first pass that results in a modified version of the report with a column number inserted before (or after) each detail data field. Then export the result and re-open it with a new model, processing this time as MCR witt 2 fields per column - our calculated Column field and the data you want.

                   

                  We then perform a lookup from the calc column filed to the previously generated lookup table to get the 'names' for column 1, column 2, etc.

                   

                  Slightly convoluted as a process but each step is quite simple, it would do the job and could be collected into a batch file.

                   

                  Grant

                    • Page Breaks, Append, and MCR
                      Olly Bond

                      Hello Brad,

                       

                      I'm think perhaps the easiest approach might be to force page breaks into the data using a first pass that ignores MCR completely but generates a fixed width text summary with a hidden key of Page(), inserting a blank line at each key change value. The resulting output can then be treated very easily using conventional MCR.

                       

                      I'd be happy to mock something up in my coffee break - I'll generate a 1000 line example text file to illustrate, or send me your data if you like. It's a two stage solution but 100% awk free, and easily automatable.

                       

                      Hope this helps,

                       

                      Olly

                        • Page Breaks, Append, and MCR
                          Olly Bond

                          Hello Brad,

                           

                          Just tested it on 1000 lines, and it works fine using the two stage approach. In stage 1, grab the entire line of data as a field, so you have one field called Header from the Append template and one called Data from the Detail template. Set the input options for this model to force a page break at say 90 lines. Create a calculated field of called, say Page, with formula Page().

                           

                          Make a summary with Page as a hidden key, with a subtotal (I'd suggest using count as a measure and hiding this), as well as Header and Data as keys (remember, as they're long lines of text with spaces they have to be character fields and so can't be measures).

                           

                          Header should be a hidden key column with the option set to display "header" with value. You can embellish it with a few blank lines to make it easier to see what's going on.

                           

                          Then export this summary as fixed width text, and open this output again in Monarch and away you go.

                           

                          As ever, the batch file generator from www.********************[/url] will help you automate the two stage process if required.

                           

                          Best wishes,

                           

                          Olly

                            • Page Breaks, Append, and MCR
                              Grant Perkins

                              OK, I came up with a similar idea to Olly.

                               

                              Same process for extracting an entire line for the details and another fo the append giving something like this;

                              Header    May-09   Apr-09  Mar-09  | ID1         1        2       3

                              Header    May-09   Apr-09  Mar-09  | ID2         4        5       6[/code]

                               

                              in the table. (Not, the "-" in the MMM/yy were auto added by Monarch in V10 ....)

                               

                              Now slice and dice to re-arrange the component parts of the fields. In V10 I used INTRIM on the field to strip unrequired spaces and then LSPLIT to select the part of the field I wanted. IIRC V8 does not have INTRIM as a function but the slice and dice may be managed by other means since the position of the required strings on the line is (presumably) known. The STR() function can do that job.

                               

                              So, build a new calculated field that gives this format.

                               

                              ID1       May-09    1         Apr-09    2         Mar-09    3

                              ID2       May-09    4         Apr-09    5         Mar-09    6

                              /code

                               

                              This can be in a single field but is often easier to build using intermediate fields, as you will see in the code below. It's more readable that way. You can always substitute the field formula for the field name in the working end result field if you choose. Then delete the intermediate field since they would no longer be needed.

                               

                              Then export the table (having hidden all field except the calculated field) for use as you input report for stage 2. You can control the format by setting the size of the 'columns' to a workable value and filling spaces something like this:

                               

                              ID+

                              Space(10-len(ID))+

                              +

                              Space(10-len())+

                              +

                              Space(10-len())+

                              +

                              Space(10-len())+

                              +

                              Space(10-len())+

                              +

                              Space(10-len())+

                              +

                              Space(10-len())

                              /SIZE[/code]

                               

                              Use a fixed font for print/export to make the next step easier.

                               

                              Stage 2 now allows you to use a 3 column MCR with 2 fields in each column (MMM-YY and the number) and once sorted by ID should give you what you want.

                               

                              So, as you can see, in geeneral the same approach as Olly but just achieved in a slightly different way.

                               

                              HTH.

                               

                               

                              Grant

                                • Page Breaks, Append, and MCR
                                  Olly Bond

                                  Hi Grant,

                                   

                                  Nice idea - we might might well be able to do it in one pass this way. One caveat, using Lsplit instead of prepping the data for normal MCR work requires that there is something in all the fields. If you had:

                                   

                                  Header: January February March

                                  001          12        42          76

                                  002           45                   234

                                  003          453     456      

                                  /CODE

                                   

                                  you'd hit snags.

                                   

                                  Best wishes,

                                   

                                  Olly

                                    • Page Breaks, Append, and MCR
                                      Grant Perkins

                                      Hi Grant,

                                       

                                      Nice idea - we might might well be able to do it in one pass this way. One caveat, using Lsplit instead of prepping the data for normal MCR work requires that there is something in all the fields. If you had:

                                       

                                      Header: January February March

                                      001          12        42          76

                                      002           45                   234

                                      003          453     456      

                                      /code

                                       

                                      you'd hit snags.

                                       

                                      Best wishes,

                                       

                                      Olly[/quote]

                                       

                                      Olly,

                                       

                                      Yes you're right, LSPLIT may not work out but it was quick way to run with the sample and I will guesss that the real data may have additional foibles to deal with. Indeed since Brad has V8 he won't have the INTRIM function that lack pretty much forces the use of using a different function set BUT the concept of the slice and dice remains much the same. I think. It was the concept I was most focused on for now.

                                       

                                      The next question becomes: Are we into general butchery of the data here or do we need to work with the finesse of a brain surgeon?

                                       

                                      Given a consistent input report structure but some unfilled positions in the data, the STR function would be my function of choice, I think.

                                       

                                       

                                       

                                      Grant

                                        • Page Breaks, Append, and MCR
                                          BradH _

                                          Well, that was a lot of activity overnight for me.  Your work from both of you is much appreciated!

                                           

                                          I like Grant's approach, as I've actually done something similar before.  Fortunately I can guarantee that every field will be populated in the columns I'm extracting, so that's one less issue.  Even if it wasn't I could still split by position as the columns are rigidly formatted.

                                           

                                          Oh, and V8 does have INTRIM, though I rarely have had need to use it.

                                           

                                          -


                                           

                                          Since I mentioned having seen a similar issue, I'll give an example of it just to share it as well:

                                           

                                          PART            PK     DESCRIPTION                        PRICE   PRICE   PRICE   PRICE   PRICE      COST                              

                                          NUMBER          CD LOC                                        1       2       3       4       5                                                                               

                                          12345           EA   1 AAA                              117.800   0.000  66.180  61.256  58.900    34.280                              

                                          1234567         KT   1 BBB                              %1141.630   0.000 706.280 593.652 570.820   332.217                            

                                          56789           EA   1 CCC                              117.800   0.000  66.180  61.256  58.900    34.280                              

                                          /code

                                           

                                          In my case, I couldn't get a floating trap to work quite correctly on the 'odd'  > 999.99 value lines.  Any column containing a value like that would have the preceding % sign added to it throwing off the columns.  Instead, I just grabbed all the price values as a single string field the used val(lsplit(,,)) to extract each field.  That ended up working perfectly in my case.

                                           

                                          Thanks a bunch for the help everyone, I'll be working on wrapping this up today!

                                            • Page Breaks, Append, and MCR
                                              Grant Perkins

                                              Oh, and V8 does have INTRIM, though I rarely have had need to use it.

                                               

                                              /quote

                                               

                                              Hi Brad,

                                               

                                              Heck, must be getting old. I could have sworn INTRIM slipped in with V9 or V10. I wonder which function I was thinking of?

                                               

                                              Just so difficult to keep track of these things.

                                               

                                              Sounds like you have a good solution there. And I'm actually kind of happy, in an odd sort of way, that the 'odd' lines existed since these things often do occur in the reports at some point but not in the samples being used for development. If they appear after development it can mean a rather wasted effort - and of course Olly's comments about using LSPLIT are another example of where it is possible to prempt downstream problems with a little forward planning.

                                               

                                              Excellent result Brad. Thanks for letting us know.

                                               

                                              Have fun.

                                               

                                              Grant

                                                • Page Breaks, Append, and MCR
                                                  BradH _

                                                  Well, it looks like I have a bigger issue here than I originally thought.

                                                   

                                                  The MCR ends up defined for only the first page.  For every page after that it only retrieves the first column of output.  This makes the ideas that we've been tossing around pretty much unworkable.

                                                   

                                                  Rough example, where the first column is the 'append' and the subsequent numeric columns are in an MCR as detail records:

                                                   

                                                  A 1 2 3

                                                  B 4 5 6

                                                   

                                                  /code

                                                   

                                                  Output:

                                                  A 1

                                                  B 4

                                                  A 2

                                                  B 5

                                                  A 3

                                                  B 6

                                                  C 7

                                                  D 0

                                                  E 3

                                                  F 6

                                                   

                                                  /code

                                                   

                                                  In the report view it even visually does display only the first page being in red lines marking the MCR while further pages only have the first column greyed in by the template.  I'd call this a bug...now if I could just get Monarch Support to answer their phones or return a call.

                                                    • Page Breaks, Append, and MCR
                                                      Grant Perkins

                                                      Brad,

                                                       

                                                      Arrgh. I half thought of this one early in the process and then entirely forgot about it. Apologies. I only have V9 and V10 on the notebook I was using to experiment with a solution for you.

                                                       

                                                      What distracted me is that using V10 in the original MCR covering detail AND header line concept I came across your 'only one header value for all lines after the first page' issue and subsequently noted that the 'revise the report layout' process seemed to overcome the problem, promptly forgetting about the subtly different V8 issue.

                                                       

                                                      From memory (but remember from my earlier post that this may be less than perfect!) the problem you describe came up rather late in the V8 lifecycle and so was addressed in V9.  In V8 I think there was a work around involving setting a virtual page length and then ensuring that each 'page' had a header. That goes back a while, some fo the detail may have escaped my recollection.

                                                       

                                                      So, what to do?

                                                       

                                                      If an upgrade is[/B] an option V10 seems to work fine for your purposes as far as I can tell. If you have to stick with V8 we need and enhanced plan.

                                                       

                                                      BTW you may struggle to get a response from Datawatch Support since V8 is no longer an officially supported version.

                                                       

                                                      I'll await your information about the potential for upgrade before jumping in to seek solutions in V8. (Partly because I'm going to be busy for a few days so time is at a premium.)

                                                       

                                                       

                                                      Grant

                                                        • Page Breaks, Append, and MCR
                                                          Olly Bond

                                                          Hi Brad,

                                                           

                                                          Don't panic. There's a fix, I think. Define a stopper template, as a footer, on blank lines, (assuming you're using my two page summary approach, injecting the blank lines in the right place (below the detail, above the page header)will be easy) and then force the the MCR to behave by starting it after the page header and stopping it on the blank footer.

                                                           

                                                          Please PM me or email olly@greenbar.info[/email] if you want some live help - we're a few time zones apart but I should be around for another hour or so.

                                                           

                                                          Best wishes,

                                                           

                                                          Olly

                                                            • Page Breaks, Append, and MCR
                                                              BradH _

                                                              I appreciate the offers and all the assistance, but I went ahead and just ran it through awk to get it over with (I'm hitting the road for 1400 mile drive in a couple hours so just out of time!).

                                                               

                                                              Eventually I'll try to get the company to upgrade me to a more current version, but it was already like pulling teeth getting upgraded from v6 to v8 a couple years ago (turns out I got it less than a month before V9 was released...doh!).

                                                               

                                                              The other method I was going to try was putting the header into Append, the MCR as detail, and calculate the fields using RowNo() and some calculations to determine which portion of the header to extract for each row.  Unfortunately that all comes back around to the bug with page breaks in an MCR.

                                                               

                                                              I did try putting in my own header and footer and defining templates for them and using that as the start/stop points for the MCR.   However, the MCR will always end on the generated page break if it doesn't find the ending template first, so the bug still stands.

                                                               

                                                              I'm not going to worry too much over this issue.  Unlike many people using Monarch, almost all my work involves practically one-off models, I'm not converting the same data every day.  I'm primarily doing data conversions, extracting data from competitor's software (inventory/POS/accounting) to load into our own software.  Our particular part of the industry has a LOT of systems out there, and major inconsistencies from version to version within those systems.  It is rare that I use a model more than twice.  If anything this was more of a search for answers should I run into a similar situation in the future, which it has definitely helped give me some insight towards.

                                                               

                                                              Thanks again for your help!

                                                                • Page Breaks, Append, and MCR
                                                                  Olly Bond

                                                                  Hello Brad,

                                                                   

                                                                  Pleased to hear you've solved it and hope you have a safe drive. If it helps persuade management to find $200 in these troubled times, I think Datawatch are about to withdraw support for v8 and with it the v8 to v10 upgrade offer - end September is the rumour I hear but I'd watch their site or speak to someone there to check.

                                                                   

                                                                  Apart from better handling of string functions, filters, summaries and their exports, MCRs and the rest, v10 has got a lovely feature for occasional Monarch work - XPS handling. From any Windows machine, you can print output from any application to a format that Monarch now treats just like a PDF. In your line of work, I imagine that will be pretty helpful.

                                                                   

                                                                  I look forward to your next challenge being posted here!

                                                                   

                                                                  Best wishes,

                                                                   

                                                                  Olly

                                                                    • Page Breaks, Append, and MCR
                                                                      Grant Perkins

                                                                      Brad,

                                                                       

                                                                      Guess you will be on the road now but for when you return ...

                                                                       

                                                                      V9 introduced some other things that are likely to be of interest to you - notably Linked Objects and the ability to share and adapt components within and between models.

                                                                       

                                                                      I can't be sure but a feature like that just might make model building for data conversion purposes a bit faster through being able to re-use previous work more readily than was previously possible.

                                                                       

                                                                      Obviously there are some other things as well from both V9 and V10 releases but that is one that came to mind.

                                                                       

                                                                      HTH.

                                                                       

                                                                       

                                                                      Grant