Calculate a Running Total in Your Report

A running total is the cumulative sum of values that are increased or decreased over time. A familiar example is a bank account statement where usually after each debit and credit, the running total is calculated based on the starting balance and is displayed as the ending balance for the day. In this article, we will show you how to calculate and maintain a running total column alongside debits and credits columns.

Steps:

1. Create a Tabular DataPage and select the data source.
2. In the results page, include the following fields:
   • Date (Timestamp) – the date and time of transactions. 
   • Transaction ID (Autonumber) – a unique ID value for each transaction. 
   • Client ID (Text 255) – clients’ unique IDs. 
   • Credit Amount (Currency) – values added to the total. 
   • Debit Amount (Currency) – values subtracted from the total.
     
     
3. Proceed to the Configure Results Page Fields screen and from the field picker, add a Calculated Field.
   
   
4. Rename your calculated field and in the Formula section, paste the following formula:
   (SELECT SUM(IsNull([@field:CreditAmount],0) -​ IsNull([@field:DebitAmount],0)) FROM Running_Balance WHERE Transaction_ID <= target.[@field:Transaction_ID] AND Client_ID=target.[@field:Client_ID])
   
   
5. Click Next and from the Default sort order dropdown, select sorting by transaction date.
   
   

Note: Ensure that you are not using the Date/Time field type for sorting by transaction dates. To calculate the running balance, each record must be unique and in order.

Output example: