There is an initial problem with the current report layout. There are too many account types listed. If you look back at Figure A-4, you’ll see that there are only two account types: Assets and Liabilities & Owners Equity. This corresponds to the Accounting Equation we discussed earlier. Another way to think about it is that the two account types are assets and ‘everything else’. If it isn’t an asset, it gets lumped into the Liabilities & Owners Equity section and we need to change the account type formula accordingly.

25. In the Field Explorer window, right click on the formula {@Account Type} and click the Edit option.
26. Change the existing formula to the following:

if {Account_Type.Account Type}="Asset" then
"Assets"
else
"Liabilities & Owners Equity"

This formula keeps the Asset type the same and changes all other types to Liabilities & Owners Equity.

The next step is the most important: displaying the account balances. After all, without numbers the report would mean nothing. However, if you just drag and drop the {@Amount} formula onto the report, all the balances will be wrong. You can’t just display the account balance on the report because all numbers are stored in the database as positive numbers (even the negative numbers). You need to write a formula that determines whether an amount should be negated or left as a positive number.

There are two things that determine the sign of an amount. The first is its account type and the second is whether it is a debit or a credit. If you recall, you have to use the rules in Table A-1 to determine which numbers are positive and which are negative. In the next step, I give you the formula that does this.

27. In the Field Explorer window, right-click the formula {@Amount} and select Edit Formula.
28. Replace the existing formula with the following formula:

if {Account_Type.Account Type} = "Asset" Then
if {Journal_Entry.Debit Or Credit} = "Debit" Then
{Journal_Entry.Amount}
Else
{Journal_Entry.Amount} * -1
Else
if {Journal_Entry.Debit Or Credit} = "Credit" Then
{Journal_Entry.Amount}
Else
{Journal_Entry.Amount} * -1

This formula first looks at the account type and then it looks at whether the number is classified as a debit or credit. Using Table A-1, when the account type is Asset and the value is a debit, it increases the account value. Thus, the formula returns a positive number. When the account type is Asset and the value is a credit, it decreases the value. The number is negated by multiplying it by -1. The formula uses the opposite for the other accounts (as dictated by Table A-1).

You should note that the Expense account doesn’t follow the rules in Table A-1. According to Table A-1, expenses are treated the same as the Asset accounts, but this formula doesn’t do that. This is because expenses are included in the Net Income calculation and they must be combined with revenues. This is a special circumstance that we’ll learn more about later in this tutorial.

29. After entering the new formula and saving it, drag and drop the formula to the Details section. Make sure that the Details section is suppressed because we don’t want to show every journal entry on the report. We just want to show the sub-totals in the group footers.

The amount needs to display a sub-total in each group footer. This summarizes the data at each level. The next three steps walk you through adding each sub-total.

30. Right-click on the {@Amount} formula in the Details section and select Insert > Summary.
31. On the Insert Summary dialog box, change the Summary Location property to Group #3 – @Account Number. Click OK to create the summary field in the footer section of Group #3.
32. There needs to be a sub-total for the account heading. Right-click on the {@Amount} formula in the Details section and select Insert > Summary. On the Insert Summary dialog box, change the Summary Location property to Group #2 – @Account Heading Number. Click OK to create the summary field in the footer section of Group #2.
33. We need another sub-total for the account type. Right-click on the {@Amount} formula in the Details section and select Insert > Summary. On the Insert Summary dialog box, change the Summary Location property to Group #1 – @Account Type. Click OK to create the summary field in the footer section of Group #1. This is what lets the user check that Assets are equal to Liabilities & Owner’s Equity.
34. Modify the formatting of the account type sub-total so that it is more prominent. Drag it to the right a couple inches and make it bold.
35. The sub-totals in group #1 and #2 each need descriptions so that the user knows what data they are associated with. For each section add a new text object. Type in the word Total.
36. In the Field Explorer, find the folder Group Name Fields and click the plus sign to expand it. Drag each group name into the text box that is in that group’s footer section. This concatenates the group name field immediately after the word “Total” so that they appear side-by-side. Lastly, check how they are formatted so that they look the same as the sub-total field.

Let’s look at the report to see where we are so far. If you preview it, you should see something similar to the next two figures. I split the report into two separate figures so that it is easier to read. Figure A-6 is the Assets section and Figure A-7 is the Liabilites & Owners Equity section.

Figure A-6. Balance Sheet preview showing the Assets section.


Figure A-7. Balance sheet showing the Liabilities & Owners Equity section.