6 Replies Latest reply: May 15, 2014 10:06 AM by Olly Bond RSS

    Conditional suppressing of footer template possible?

    Schorschi _

      Hi there,

       

      I've been working with Monach Pro 8.0 for a few weeks now and recently came across a problem that I have not been able to solve satisfactorily.

       

      I have a report with variations in records that look as follows:

       

      -+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++»

      ¦ 123456789     COMPANY A                                       ¦

      ¦                                                               ¦

      ¦   LAST ACTIVE 10/26/09                                        ¦

      ¦                                                               ¦

      ¦   ACCOUNT_SUMMARY  -    SDFS EQUITY       -  CLOSING_BALANCES ¦

      ¦                                                               ¦

      ¦                  (010)                                222,478 ¦

      ¦                  (022)                                  2,528 ¦

      ¦     ACC'T    00902                  2,280                     ¦

      ¦     ACC'T    00954                    248       _____________ ¦

      ¦                                   *TOTAL*           225,006 ¦

      ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++]

       

      -+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++»

      ¦ 234567891    COMPANY B                                        ¦

      ¦                                                               ¦

      ¦   LAST ACTIVE 09/19/08                                        ¦

      ¦                                                               ¦

      ¦   ACCOUNT_SUMMARY  -    SDFS EQUITY       -  CLOSING_BALANCES ¦

      ¦                                                               ¦

      ¦                  (022)                                543,000 ¦

      ¦     ACC'T    00902                543,000       _____________ ¦

      ¦                                   *TOTAL*           543,000 ¦

      ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++]

       

      -+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++»

      ¦ 345678912     COMPANY C                                       ¦

      ¦                                                               ¦

      ¦   LAST ACTIVE 01/12/10                                        ¦

      ¦                                                               ¦

      ¦   ACCOUNT_SUMMARY  -    SDFS EQUITY       -  CLOSING_BALANCES ¦

      ¦                                                               ¦

      ¦                  (010)             *TOTAL*          142,918 ¦

      ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++][/CODE]

       

      My detail template captures - without any problems - the amounts in the rows that contain the three-digit numbers in parantheses. In the first example record the amounts are 222,478 and 2,528.

       

      I am capturing the TOTAL amount in a footer template. Unfortunately, this doesn't work correctly in the case of the third record type, where the detail data coincides with the footer data.

       

      What happens for records of the third type is, that the Total amount is taken from the subsequent record.

       

      So, the data - in table view - looks as follows:

       

      REPORT_DATE     COMPANY_ID    COMPANY_DESC CLOSE_BAL TOTAL

      -


           -


          -


      -


         -


      2010-02-11      123456789     COMPANY A    222478    225006

      2010-02-11      123456789     COMPANY A    2528      225006

      2010-02-11      234567891     COMPANY B    543000    543000

      2010-02-11      345678912     COMPANY C    142918    60000[/CODE]

       

      ..which is incorrect for the last record, because the amount in the Total column gets picked up from the subsquent record (60000). But it should be 142918.

       

      I can understand why this is happening. Monarch apparently can't find the TOTAL value for the third record type (because it already captured the detail data on the same line) and simply takes the next available footer information, which happens to be in the next record's Total line.

       

      So, my question is, is there a way to prevent Monarch - at the very least - from taking the footer value from the next record?

       

      Ideally, it would be great if it was able to pick up the Total value correctly from the same record, so the ideal result to the above example would look as follows:

       

      REPORT_DATE     COMPANY_ID    COMPANY_DESC CLOSE_BAL TOTAL

      -


           -


          -


      -


         -


      2010-02-11      123456789     COMPANY A    222478    225006

      2010-02-11      123456789     COMPANY A    2528      225006

      2010-02-11      234567891     COMPANY B    543000    543000

      2010-02-11      345678912     COMPANY C    142918    142918[/CODE]

       

      If this doesn't work, I'd be happy if at least the incorrect total value wouldn't be picked up, so a way to suppress a value from a footer template if it can't be found within the record is what I'm looking for. In that case the data would look as follows:

       

      REPORT_DATE     COMPANY_ID    COMPANY_DESC CLOSE_BAL TOTAL

      -


           -


          -


      -


         -


      2010-02-11      123456789     COMPANY A    222478    225006

      2010-02-11      123456789     COMPANY A    2528      225006

      2010-02-11      234567891     COMPANY B    543000    543000

      2010-02-11      345678912     COMPANY C    142918    -[/CODE]

       

      I have played around with the Summary View and was able to generate a report of summaries only, but that's not what I'm looking for.

       

      Georg

        • Conditional suppressing of footer template possible?
          Grant Perkins

          Hello Georg and welcome to the forum.

           

          You have an interesting challenge.

           

          Based on the sample I created a detail template trapping on the ( and ) characters.

           

          I crerated a Footer template trapping on L**.

           

          Then I created an APPEND template trapping on exactly the same as the detail template and trapping the same value field BUT changing the field's Advanced Parameters to set a PRECEDING STRING of *total*. In this was the field will only be 'found' of the detail trap and a total line coincide (at least that is the case for the sample report!)

           

          Now you table will have detail values, footer values and the additional field which will show NULL for all normal fields but a value if the detail and footer coincide on the same line.

           

          Now create a calculated field using the IF() function that is conditional on your APPEND template field so that if its value is NULL you use the value from the footer template and if there is a genuine value in the field you use the value and discard whatever appears in the footer field. Use the calculated field for all output for the footer field value.

           

          Does that make sense to you? I am writing in a hurry so it may be unclear. If so just tell me and I will re-write it later. From what you originally wrote I have assumed that you have become reasonably comfortable with Monarch in the time you have been using it so I hope this will make sense to you.

           

          Grant

            • Conditional suppressing of footer template possible?
              Schorschi _

              Hi Grant,

               

              Thanks for your reply.

               

              I think I got it to work.

               

              Let me recap to see if I understand the three templates correctly.

               

              The first one is straight forward: Detail template trapping on "(   )" to capture the CLOSING BALANCE amount.

               

              The second one, a footer template, captures the TOTAL amount, but of course will fail to capture the correct value in case of a record that only has the TOTAL line. Instead it will capture the TOTAL amount from the subsequent record.

               

              Up to here it's pretty much how I had it before too.

               

              So, with the third (append) template, we are again trapping the same parentheses as before "(   )" with the addition of adding the "*total*" string in the "preceding string" parameter in the advanced tab of the additional TOTAL field.

               

              So, this means that only on those lines where we have the parentheses AND the string "*TOTAL*" will the amount be captured, all others will be NULL.

               

              Later then in the additional calculated field, we use the existence of that very value to indicate that this is the correct total value (as otherwise it would use the total amount captured for the subsequent record as part of the footer template).

               

              I'm having trouble though grasping the defintion of the append template. I played around with this template a little and added the "*TOTAL" string as a trap (next to the already existing "(   )"), but even though I removed the "total*" parameter from the second TOTAL field's advanced parameters inside the append template, it didn't capture the total amount for any record. They were all NULL.

               

              What is the reason for the different capture behavior between my modified append template and your original one?

               

              Georg

                • Conditional suppressing of footer template possible?
                  Olly Bond

                  Hello Georg,

                   

                  I think that you might have missed Grant's suggestion to use the advanced field properties to set the Total to pick up only on the Preceding Sting of "TOTAL" appearing in the relevant line.

                   

                  This would prevent "The second one, a footer template, captures the TOTAL amount, but of course will fail to capture the correct value in case of a record that only has the TOTAL line. Instead it will capture the TOTAL amount from the subsequent record" being an issue.

                   

                  HTH,

                   

                  Olly

                    • Conditional suppressing of footer template possible?
                      Schorschi _

                      Hallo München, hier ist Florida.

                       

                      Danke für das Dazuschalten am späteren Abend. Hier kommt erst noch der Feierabend.

                       

                      Hm, I'm not sure I misunderstood.

                       

                      I'm not questioning the validity of Grant's solution. From what I can tell by looking at the table results and comparing them with the report, it works great.

                       

                      The one thing I don't understand is why the append template has to be defined the way it is. In other words, why can I not define the append template by trapping on both the parentheses and the *TOTAL* string and achieve the same result?

                       

                      I observed that the second TOTAL field defined within my version of the append template didn't capture any amounts at all, only NULLs. That's what's confusing me.

                       

                      Georg

                        • Conditional suppressing of footer template possible?
                          Olly Bond

                          Hello Georg,

                           

                          There's a fair amount of discussion here about the guru trap - which is what a variant of is being used here to handle your data - and the you're right to question it - it seems illogical to most Monarch users to rely on the same trap as a  previous detail to capture data below that detail as an append.

                           

                          COMPANY A - 010     222478      225006                    225006

                          COMPANY A - 022     2528       225006                    225006

                          COMPANY B - 022     543000     543000                    543000

                          COMPANY C - 010     142918              142918          142918[/CODE]

                           

                          But you do have to trap the append on the same characters as the detail to get it to work. I've just managed to crash Monarch (a rare treat) saving the model for you as a PRF. Check out your PMs and I'll let you know when I get it online for you.

                           

                          An example Portable Report Format file is now online at: http://www.greenbar.info/examples/conditional-footer.prf[/url]

                           

                          Best wishes, mfG,

                           

                          Olly

                      • Conditional suppressing of footer template possible?
                        Grant Perkins

                        Hi Grant,

                         

                         

                        I'm having trouble though grasping the defintion of the append template. I played around with this template a little and added the "*TOTAL" string as a trap (next to the already existing "( )"), but even though I removed the "total*" parameter from the second TOTAL field's advanced parameters inside the append template, it didn't capture the total amount for any record. They were all NULL.

                         

                        What is the reason for the different capture behavior between my modified append template and your original one?

                         

                        Georg[/quote]

                         

                        Hi Georg,

                         

                        The way the extraction works is dependent on the Monarch 'operational rule' that Append template traps must be defined (if they are to work as expected) BEFORE the detail template and Footer template traps must be defined AFTER the detail template trap. It makes logical sense for a well structured report but not all reports are quite as well structured as that, especially when trying to extract data for other purposes.

                         

                        In the case of the suggested APPEND we are pushing the limit to be 'the same as the detail template' or, more precisely, the last part of the definition of the APPEND Template must not fall after the last part of the definition of the DETAIL template. If it does the results may be, at best, unpredictable. More likely it just will not produce anything -  but some reports may have oddness that means something is extracted.

                         

                        So, if I understood correctly, you added the 'total' (etc.) to the trap which would 'push' the net trap definition further along the line and therefore the trap will 'end' after the detail trap 'ends' which therefore breaks the extraction parsing rules (as far as I know).

                         

                        You can think of the preceding string, in this respect, as a positive selection filter that grabs only character strings that obey the rule of the prerequisite. It is a very powerful feature when circumstances allow it to be.

                         

                        There are a number of methods of using Monarch that have been discovered over the years that were probably not part of the 'must do this' specification for development. Fortunately progress has not led to any losses of functionality and I feel there may be more to be discovered. Thinking of Monarch as a set of tools rather than just a solution prescription seems to offer opportunities for discovering some less-than-obvious approaches from time to time as the systems of the world throw their problems in our direction.

                         

                        If you have any opportunity to upgrade to V10.5 I would recommend it - the toolkit expands quite significantly and I think you would appreciate the options.

                         

                        I very much recommend experimentation - you never know what you might discover!

                         

                        HTH.

                         

                         

                        Grant