3 Replies Latest reply: May 15, 2014 10:09 AM by Data Kruncher RSS

    Report giveth details..... and taketh away.

    mdyoung _

      I have a report that contains billing information and a list of transaction details. My detail line is trapping the / characters in the TXN DATE field of the transaction details section. Append templates are capturing the billing information above it. The problem comes in when the client does not have any detailed transactions. In this case, I still need to capture the appended data and just show blank values for the detailed fields. Below is a test report that shows client two with no detailed transactions.

       

      I could have sworn I've seen a similar issue in these forums somewhere, but just couldn't find it, so I apologize if this is a duplicatated issue.

       

      Here's the output that I'm expecting to get out of all this.

      SSN           NAME                ACCT_BALANCE     TXN_DATE     TYPE     TXN_AMOUNT

      111-11-1111   TEST,CLIENT ONE             0.00     01/21/10     A          -2001.15

      111-11-1111   TEST,CLIENT ONE             0.00     01/21/10     R          -5530.95

      111-11-1111   TEST,CLIENT ONE             0.00     01/21/10     A            -61.00

      222-22-2222   TEST,CLIENT TWO          5467.50                                    

      333-33-3333   TEST,CLIENT THREE          16.00     04/29/10     A          -8415.30

      /code

       

       

      111-11-1111      TEST,CLIENT ONE

       

           LAST BILLED: 01/07/10         CHARGE TOTAL:   7593.10                                                                

            BAR STATUS: FB               ACCT BALANCE:      0.00                                                                   

                AGENCY:                   INS BALANCE:      0.00                                                                   

      STATEMENT GROUP: GOVT           CLIENT BALANCE:      0.00                                                                   

             ZERO DATE: 03/08/10                                                                               

      BILL STATUS: POSTED                                                                               

      -


      TRANSACTION DETAILS -


                TXN DATE  TYPE   TXN AMOUNT

                -


        -


         -


                01/21/10  A      -2001.15

                01/21/10  R      -5530.95

                03/08/10  A        -61.00

       

       

       

      222-22-2222      TEST,CLIENT TWO

           LAST BILLED: 01/18/10         CHARGE TOTAL:   5467.50                                                                

            BAR STATUS: BD               ACCT BALANCE:   5467.50                                                                

                AGENCY: LEGAL             INS BALANCE:   4933.27                                                                

      STATEMENT GROUP: ALL            CLIENT BALANCE:    534.23                                                                   

             ZERO DATE:                                                                               

      BILL STATUS: REVERSED 

      -


      TRANSACTION DETAILS -


                TXN DATE  TYPE   TXN AMOUNT

                -


        -


         -


       

       

       

      333-33-3333      TEST,CLIENT THREE

       

           LAST BILLED: 01/14/10         CHARGE TOTAL:   8431.30                                                                

            BAR STATUS: BD               ACCT BALANCE:     16.00                                                                   

                AGENCY: 17                INS BALANCE:      0.00                                                                   

      STATEMENT GROUP: SP             CLIENT BALANCE:     16.00                                                                   

             ZERO DATE:                                                                               

      BILL STATUS: PRINTED                                                                               

      -


      TRANSACTION DETAILS -


                TXN DATE  TYPE   TXN AMOUNT

                -


        -


         -


                04/29/10  A      -8415.30

       

      /code

       

      All help is greatly appreciated.

       

      Thanks,

       

      Micheal

        • Report giveth details..... and taketh away.
          Data Kruncher

          Hi Michael,

           

          As you've undoubtedly surmised, you won't be able to include the SSN and name if you don't have detail records for them. Accordingly, the solution (well, mine anyway ) lies in using multiple models/projects to accomplish the end goal. Here's the overview.

           

          In order to get the right final sequence, we need a simple list of SSNs and their order, using the RecNo() function. Build a model using the account balance as the detail template, appending SSN and Name. Now add the transaction fields using a Footer template. Good thing that you've got v10.5, because you want to use the "Cleared by" feature in the footer template editor to set it to the Name append template. This ensures that the transaction values only get associated to the right records.

           

          Now we need to be able to isolate the records without transactions, so build a No Transactions filter using IsNull(TXN_DATE). Set the Options-View null value to nothing if it has some value as a default.

           

          Build a summary named Records using SSN as the key field and RecNo as the measure. No filter should be used for this summary. Build a No Transactions summary using the No Transactions filter.  Key fields are SSN, NAME, ACCT_BALANCE, TXN_DATE and TYPE, measures are TXN_AMOUNT and Recno (the calculated field that uses RecNo() ).

           

          A project export sends the Records summary to Records.xlsx (overwrite), and another project export sends No Transactions to Final Data.xlsx (overwrite). Run the project exports and save and close the model and the project.

           

          Next up, we need the data from a model that captures the transaction data as detail records. Add a Recno field to this model too using an external lookup to Records.xlsx and display Recno as the last field in the table (mirroring the layout of the No Transactions summary). Build a project export to append Final Data.xlsx with the Table data. Run the project export and save and close the model and project.

           

          Finally, open Final Data.xlsx as a database, sort on Recno and hide Recno. Save and close the model and project.

           

          Whip up a batch file to run the steps in order.

           

          Mission accomplished.