2 Replies Latest reply: May 15, 2014 10:06 AM by Mike.Nordick _ RSS

    Report with 2 line formats, sometimes line 2 not present

    Mike.Nordick _

      I'm using version 9.01, and having difficulties getting my export to look like I want it to from this report:

      Account Number          Payment Date     Principal      Interest

           P&I Constant          Int Rate

       

      50001               01-26-10     173.56          401.44

           575.00               .082400

      50005               01-20-10     136.89          506.10

           642.99               .085500

      50005               02-20-10     137.89          505.10

      50011               01-25-10     158.47          273.19

           431.66               .072500

      /CODE

       

      Note that for the first account, there is a line 1 and line 2.  For the second account there are 3 lines... a line 1, a line 2, and another line 1 without a line 2.

       

      I want my table to look like this:

      Account Number     Payment Date     Principal     Interest     P&I Constant     Int Rate

      50001          01-26-10     173.56          401.44          575.00          0.082400

      50005          01-20-10     136.89          506.10          642.99          0.085500

      50005          01-20-10     137.89          505.10          642.99          0.085500

      50011          01-25-10     158.47          273.19          431.66          0.072500

      /CODE

       

      I've tried several approaches and nothing seems to work right.  For example, I defined line 1 as the detail, and line 2 as an append... also tried line 2 as a footer.

       

      When the line 2 format is an append, it looks like this:

      Account Number     Payment Date     Principal     Interest     P&I Constant     Int Rate

      50001          01-26-10     173.56          401.44                    

      50005          01-20-10     136.89          506.10          575.00          0.082400

      50005          01-20-10     137.89          505.10          642.99          0.085500

      50011          01-25-10     158.47          273.19          642.99          0.085500

      /CODE

       

      When line 2 format is a footer, it looks like this:

      Account Number     Payment Date     Principal     Interest     P&I Constant     Int Rate

      50001          01-26-10     173.56          401.44          575.00          0.082400

      50005          01-20-10     136.89          506.10          642.99          0.085500

      50005          01-20-10     137.89          505.10          431.66          0.072500

      50011          01-25-10     158.47          273.19          431.66          0.072500

      /CODE

       

      Can someone please help me out?

      Thanks!

        • Report with 2 line formats, sometimes line 2 not present
          Data Kruncher

          Hi Mike. Welcome to the forum!

           

          The ol' now-you-see-it-now-you-don't occasional data challenge.

           

          We refer to the solution to that particular beast as the "[URL="http://********************/extract/experimentation-produces-monarch-success"]guru trap[/URL]" technique.

           

          To model your report successfully:

           

          Create a one-line detail template, painting the account number, payment date, principal and interest. Trap using NNNNN for the account number.

          Build a new one line append template using the troublesome line as the sample. But here's the kicker: use [I]exactly the same trap[/I][/B] for this as you did for the detail template, being NNNNN, and paint the two fields.

          For each of the append template fields, go the their field properties Advanced tab and specify a - character for the preceeding string text. This will be found in the dates of the preceding line. Maybe not the most robust solution, but in your sample (at least) it'll work.

          Set the fields to copy values from the previous record.

          /LIST

          That ought to do it. I'd encourage you to spend a couple of minutes reading through Grant's description (follow the link above).

           

          HTH,

          Kruncher