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

    Count Transactions

      I want to count transactions within an ACCOUNT#. 

      Sample:

      ACCOUNT# TRANSACTION DESCRIPTION

       

      8861-8400 JV121 CAPITALIZED EQUIP LEASE     GL

      8861-8400 JV159 PREPAID INSURANCE AMORT     GL

      8870-8400 JV108 PROPERTY TAX AMORT     GL

      8870-8400 Voucher/Credit Canceled     AP

      8870-8400 Voucher/Credit Created     AP

      8870-8400 Voucher/Credit Created     AP

      8880-1300 JV07091114     GL

      8880-1300 JV130 ESTIMATE EE BENEFITS     GL

      8880-1300 JV131 I/C PHC DUE TO     GL

      I need to know count of transactions for 8861-8400

                                                             8870-8400

                                                             8880-1300

      Along with the count, I also need to show the counter contents next to each transaction.

      Example:

       

      ACCOUNT# TRANSACTION DESCRIPTION                   TRANS COUNT

       

      8861-8400 JV121 CAPITALIZED EQUIP LEASE     GL     COUNT 01

      8861-8400 JV159 PREPAID INSURANCE AMORT     GL     COUNT 02

      8870-8400 JV108 PROPERTY TAX AMORT     GL     COUNT 01

      8870-8400 Voucher/Credit Canceled     AP                  COUNT 02

      8870-8400 Voucher/Credit Created     AP                  COUNT 03

      8870-8400 Voucher/Credit Created     AP                  COUNT 04

      8880-1300 JV07091114     GL                               COUNT 01

      8880-1300 JV130 ESTIMATE EE BENEFITS     GL     COUNT 02

      8880-1300 JV131 I/C PHC DUE TO     GL                  COUNT 03

       

      I believe count in the summary is the way to go (although so far haven't gotten desired results), but not really sure if there is an easier way to accomplish that.

        • Count Transactions
          Data Kruncher

          Gerhard posted [URL="http://www.monarchforums.com/showpost.php?p=10020&postcount=4"]a solution to a similar ranking challenge[/URL] last year, but the layout of the report in question was considerably more favorable to the solution than is yours.

           

          Will need to consider this one before posting ideas.

            • Count Transactions
              Grant Perkins

              Kruncher recalling the excellent solution from Gerhard got me thinking about this and it seems to me that the key is to use the LINE() function, with adjustment as per Gerhard if and when required. (That is report format dependent.)

               

              Line() works at the PAGE level so if you can make the ACCOUNT number change force a PAGE break you get close.

               

              This should be fairly easy to do using a 2 step process.

               

              Firstly process your input file to grab the records and add a calculated field so that similar records are not aggragated in a summary. You can use the LINE() function for this as well, as a throwaway piece of data that should ensure each report line gets a unique line in a summary.

               

              Define a summary with Account, Description and the line() field as the key fields.

               

              For the ACCOUNT field set the DISPLAY properties so that it includes a header line for each account number. We will use this for pagination in the next step.

               

              Ensure the columns are all suitable widths to display all the data they will contain and export the summary as a fixed width text file (or PDF if you are prepared to live dangerously!). A fixed font is also advisable.

               

              Now open this newly created file in a new Monarch session. Map the detail from the report as usual and then use the newly added Account Header line as a Page Header template.

               

              In the TABLE window add a calculated field using the LINE() function once again, with or without the Gerhard offset adjustment to make the LINE() number the record count number you require, depending on what your previous export looked like.

               

              Line() will now be working within the PAGE NUMBER (i.e. Account Number if all has gone well) and should give you the result you need.

               

              I can see ways to come up with a record count per Account Number as a subtotal within a single pass summary but not an obvious and consistent way to do what you want without, in effect, remodelling the report a little to start with. However it may just be that my old brain is not attuned to the possibilities so if anyone has a one step solution please post it. I am sure many of us would be very grateful.

               

              HTH.

               

               

              Grant