How do I calculate a financial balance in a report?

Posted by itservices on 10-Jul-2015 08:22

In my application I have 2 main objects: "Earnings" and "Expenses"
My end goal is to have a report which lists:
- All Earnings
- The sum of all Earnings
- All Expenses
- The sum of all expenses
- The total balance (sum of Earnings - sum of Expenses)

In order to get both objects into 1 report I have made triggers which clone each Earning or Expense onto another object through a conversion map. This third object "Record" can contain every possible piece of information an Earning or Expense can contain as well as select from a picklist whether the Record is an Earning or an Expense.

Next I make a report using my Record entries. The report groups all entries by "Earning" or "Expense" (picklist value) and then creates subtotals of both categories. Now for the actual question:

At the bottom of the report there is a grand total, which displays the sum of all of my Earnings and Expenses. This information is irrelevant. I would like to display the total balance instead, calculated by subtracting total Expenses from total Earnings. How can I do this?

Posted by pvorobie on 10-Jul-2015 11:56

You can create an Expression field which is equal to Amount for Earnings and -Amount for Expenses, then calculate total on that Expression field.

All Replies

Posted by pvorobie on 10-Jul-2015 11:56

You can create an Expression field which is equal to Amount for Earnings and -Amount for Expenses, then calculate total on that Expression field.

Posted by itservices on 13-Jul-2015 02:43

Do you mean a formula field in the Record object or in the report itself?

Posted by pvorobie on 13-Jul-2015 10:28

Field always belongs to an Object, "Record" in your case.

This thread is closed