At this point, the balance sheet is pretty much finished. The report balances, the accounts are in the correct categories, and net income appears as a single line within retained earnings. There are just a few additional changes we should make. We need to clean up the formatting a bit to make it look more professional and we’ll add some features that you will likely need with your own reports.

The first feature to add is giving the user the ability to specify which year to print. This requires adding a report date parameter. We also have to create a record selection formula that filters the records based on this date.

41. In the Field Explorer window, right-click on the Parameter Fields category and select New. This opens the Create New Parameter dialog box.
42. Enter the parameter name Date Range. Make it of type Date, and click the OK button to save your changes and create the parameter.
43. Choose the menu items Report > Record Selection Formulas > Record. This opens the Formula Workshop dialog box.
44. Enter the following formula into the code window:

{Journal_Entry.Date} <= {?Date Range}

45. Click the Save and Close button to save your changes and close the dialog box.

Earlier in this appendix, you learned that you have to careful when setting the date range. It is dependent upon whether the accounting system uses year-end closing entries for revenues and expenses, as well as, what point in the fiscal year the report is being printed. For example, even if the company closes out its net income balance to retained earnings, you would still need to include revenues and expenses if the balance sheet is printed mid-year. This is because you have to account for all the transactions that took place in the months after the end of the year. To modify this formula for your particular circumstance, you need a different date range formula for revenues and expenses than you do for the other accounts. For example, if this were printed in mid-year, we only want transactions from the first day of this year thru the report date. An example record selection formula would be the following code. You can modify it according to your circumstances.

({Account_Type.Account Type} in ["Asset", "Equity", "Liability"] and
{Journal_Entry.Date} <= {?ReportDate})
OR
({Account_Type.Account Type} in ["Revenue", "Expense"] and
{Journal_Entry.Date} IN Date(Year({?ReportDate}), 1, 1) To {?ReportDate})

46. Let's add company information in the report header. Make the Report Header larger so that it is about a half inch tall. Right-click on it and select Insert Text Object. Place the text object near the top and center it.
47. Enter the text Xtreme, Inc. in the text object.
48. Right-click on the report header and insert another text object below the previous one. Enter the text Balance Sheet.
49. Drag and drop the parameter {?Date Range} onto the report header and place it below the previous text object.
50. Click on all three text objects and center them as well as make them bold.
51. Click on the Group #1 Name object and change its font size to 12.
52. Click on the Group #3 Name object (the account name) and turn off the bold formatting property. Do the same for the subtotal object directly to the right of it in the same section. By making the objects in this section a normal font, the other groups will stand out more.

Many financial reports use borders around important sub-totals to highlight them on the report. The balance sheet is no exception.

53. Right-click on the amount sub-total in the Group Footer #2 section and select Format Field.
54. Click on the Border tab. In the Line Style area, add a single line to the top and bottom areas.
55. Right-click on the amount sub-total in the Group Footer #1 section and select Format Field.
56. Click on the Border tab. In the Line Style area, add a single line to the top area and for the bottom area add a double line.

What we haven't worked on yet are the contra-accounts. If you recall, contra-accounts are used to offset the balance of another account. They are listed after the main account and have special formatting so that it is obvious to the reader that it is a contra-account. In this database, we have two contra-accounts: Accumulated Depreciation (Machinery) and Accumulated Depreciation (Building). For these two accounts, let's insert the text "Less:" in front of the account name so that the reader knows that it's a contra-account.

57. In the Field Explorer window, right-click on the {@Account Name} formula and select Edit.
58. Modify the existing formula to be the following:

//Consolidate Revenue and Expense accounts into Net Income
if {Account.Account Number}>="4000" then
"Net Income"
//Modify the contra-account names
else if {Account.Account Number} in ["1845", "1865"] then
"Less: " & {Account.Account Name}
//For all other accounts, return the name as expected
else
{Account.Account Name}

In addition to consolidating the revenue and expense accounts into net income, this formula changes the names of the two contra-accounts (account numbers 1845 and 1865).

As a last exercise, let's look at consolidating multiple accounts into a single account. Public companies often don't want to show every minor account in their financial reports. They want to take the accounts with small balances (referred to as ‘non-material' accounts) and merge them into one account that has a more material balance. In another circumstance, you could be writing a financial report for a business manager and he or she doesn't want to see the accounts that don't apply to their area. While you can't delete the accounts from the report (then it wouldn't balance anymore), you can merge them into a single "Miscellaneous" account.

For this database, the Xtreme mountain biking company sells bicycles and various cycling accessories (gloves, helmets, etc.). The accessories are not the primary source of revenue for the company and their total balance isn't very much. Let's modify the report so that the accounts that track sales for the accessories are consolidated into a single account. The account numbers for the accessories are 1510, 1520, 1530, and 1540.

59. In the Field Explorer window, right-click on the {@Account Number} formula and select Edit.
60. Overwrite the existing formula with this new formula:

//Consolidate revenues and expenses into one account
if {Account.Account Number}>="4000" then
"4000"
//Merge the bike accessories into one account
else if {Account.Account Number} in ["1510", "1520", "1530", "1540"] then
"1510"
else
//All other accounts return the natural account number
{Account.Account Number}

Lastly, you need to change the account name for these accounts to be the same. We'll call them, "Other Bike Accessories".

61. In the Field Explorer window, right-click on the {@Account Name} formula and select Edit.
62. Overwrite the existing formula with this new formula:

//Consolidate Revenue and Expense accounts into Net Income
if {Account.Account Number}>="4000" then
"Net Income"
//Modify the contra-account names
else if {Account.Account Number} in ["1845", "1865"] then
"Less: " + {Account.Account Name}
//Merge the bike accessories into one account
else if {Account.Account Number} in ["1510", "1520", "1530", "1540"] then
"Other Bike Accessories"
//For all other accounts, return the name as expected
else
{Account.Account Name}

After making all these changes, your report should look similar to Figure A-8.

Figure A-8. Balance sheet final preview.