1 Reply Latest reply on May 4, 2012 10:47 AM by philmodjunk

    Showing Total Balance of Each Account

    ShaikhAtta

      Title

      Showing Total Balance of Each Account

      Post

      Hello All,

                     I have the 2 below tables with some sample data and the relationship of Accounts::AccountID  with Transactions::AccountID

       

      Table 1: Accounts

      Account ID          Name

      001                      John

      002                     Abraham

      003                     Kate

       

      Table 2 : Transactions

       

      Account ID          Name             Credit               Debit          RunningBalance

      002                     Abraham          500                     0               500

      003                     Kate                    0                  100             400

      002                     Abraham              0                  200             200

      001                     John               1000                     0             1200

       

       

      Now this is what I want, the list of each total account like this in a report printable format.

       

      Account ID       Name               TotalCredit           TotalDebit            TotalBalance

      001                     John                     1000                           0                    1000

      002                     Abraham                500                         200                    300

      003                      Kate                        0                           100                   100

       

       

      Thanks for the support in advance

        • 1. Re: Showing Total Balance of Each Account
          philmodjunk

          You have two options for producing this result.

          Option 1:

          Create a new layout based on accounts. You can get totals on these fields either with calculation fields defined in Accounts like this one:

          Sum ( Transactions::credit )

          Or you can define summary fields in Transactions that compute the "total" of the Credit, Debit, and the total of cBal, a calculation field defined as Credit - Debit. In this case, you put the summary fields from transactions directly on your report layout. (Do not use the running balance summary field for this.)

          Option 2:

          Use the summary fields described above, but on a layout based on transactions. Remove the body layout part and add a sub summary layout part "when sorted by" Account ID. Put the summary fields, as well as the account ID and the name field from Accounts in this sub summary part. Make sure that you sort your records by AccountID or the layout will be blank.

          Option 1 may be best if you only want a grand total for each account. Option 2 can be a better option when you want a report of account totals for a sub set of the total records such as all transactions taking place in a given range of dates...