2 Replies Latest reply: May 15, 2014 9:53 AM by Dee Moore RSS

    foot-band or append-band?

    Volker Pohlers



      I'm use Monarch Std. V7 german.


      Now I have reports in the form:


      line type_a

      line type_a

      append line type_b

      line type_a

      line type_a

      line type_a

      line type_a

      append line type_b


      i.e. sometimes with an appending line.


      I have tried to define lines of type_a as detail and lines of type_b as append line. In this case the append line will be connect to the folloing line, but not to the prior line :-(. If I define line_b as type foot-band, it will be connected to all prior lines, not only the direct one above


      How must I define the bands to analyze the report correctly?




        • foot-band or append-band?
          Grant Perkins

          Hello Volker and welcome to the forum.


          I think you will not find an easy solution to your problem using append or footer features when the report format is as you describe.


          Your B type lines really need to be part of the detail somehow.


          If you have a blank line between each detail record you could create a 2 line template and pick up the B lines as well. But I have a feeling this will not be the case for your report.


          There are ways to extract fields from reports where the data sometimes exists and sometimes does not exist AND may be on a different line in the record each time. However it requires use of the "Preceding String" function to identify the field when it exists and there may not be a suitable preceding string to use or there may be no correct place in the template to define field.


          These things are rather data and report format specific. It is difficult to describe completely without reference to the real report. Do you have a sample that you could make available for experiments without the risk of releasing confidential information?


          Another way to look at this would be to firstly extract all of the B lines with a reference key to their parent A line and then make a lookup for a second model which would use a template for the A lines only but lookup any addition values.


          This is easy to define and automate with the Pro version of Monarch. It is possible using the Standard version but, as far as I know, not easy to automate.


          Here is a description of how to cut and paste data from a summary or table (In this case your B line extract)into an internal lookup table using a calculated field.




          The requirement of the process is a little different to your requirement but the way to create the lookup table should be the same I think.


          Let us know if it works for you and how well it matches your production needs.


          And if you have a report sample you can release for people to play with send me a Private Message with your email address and I will make contact directly with my email address so that you can send the file.


          I hope this helps.


          Good luck.




          [size="1"][ May 18, 2006, 03:10 PM: Message edited by: Todd Niemi ][/size]

          • foot-band or append-band?
            Dee Moore

            Hello Volker,


            If your data appears as in your example, you will not be able to use a Multiline Sample for your Template. This will omit some of your records.


            For your version, the best approach would be to:


            Choose your Detail line

            Highlight the entire length of data on the line

            Double click to bring up the Field Properties

            Change the Datatype to Memo

            Click on the Advanced tab

            In the End Field On Options, choose Line count: 2


            The data from both lines will appear in one cell.


            From here split the lines using Calculated Fields.

            If the data is well formatted, it may be as simple as using a Substr() or LSplit/RSplit()function. If the data lengths are variable you may need to incorporate an Instr(),Len(), or combination of functions.


            Also, if the first line contains data that needs to be split into seperate fields, you do not need to create Calculated Fields to do this. In the Report window, choose the first line again, and create an Append Template. Define your Fields.


            Certainly upgrading to the Professional Edition will give you the ability to perform External Lookups (database joins). However, this may be a bit cumbersome. I always advise creating one Model when ever possible. If your company deals with hundreds of Reports, the less to keep track of, the better.


            I will give you one good reason to upgrade to Monarch V8 when it is available in German (there are many more). It contains a new function:




            This function returns the specified line from a Memo field. This is much easier than creating Calculated Fields.


            I hope this is helpful,


            Dee Moore

            Datawatch Technical Support