The problem I’ve found with writing financial reports for companies is that when given the initial specifications (and I use that term loosely), the person requesting the report says something like, “For the account descriptions and classifications, just use what is stored in the database.” I can tell you from experience that this is usually said by someone who hasn’t taken the time to look at the database, and doesn’t want to be bothered with it right now. Once you present them with a draft of the report to review, they are all too eager to rename the groups, change descriptions, and consolidate multiple accounts into a single account. This is to be expected because financial reports serve different purposes for different people. What is in the database can’t fit the needs of everyone. Public reports generally follow a strict format while internal reports are tweaked to meet the needs of different managers. Since you are the report designer, you have to be flexible to account for change requests as the report is being designed. Depending upon how you designed the report, this can will either be no problem or cause you a lot of frustration.

I like to set up the report so that no actual database fields are used directly on the report. Instead, I create a formula for each database field and display that formula on the report. Initially, the formula simply returns the exact value of the database field. Since the formulas just repeat the data field, the report doesn’t suffer a performance hit. This might seem a little wasteful, but as the report is reviewed and revised, the formulas can be easily tweaked to make custom changes where necessary. For example, the final formula for the account name will usually consist of a bunch of If-Then statements that modify the account name for specific account numbers.

What most report designers are tempted to do is drag and drop all the database fields onto the report designer. When it comes time to change account names or merge multiple accounts into a single group, they then have to create new formulas that make these changes and swap out the original fields with the new formulas. This creates a lot of extra work as you reposition the fields and fix the formatting. I suggest taking the extra two minutes in the beginning to create a few simple formulas that mimic the original fields. In the long run, you won’t need to buy as much aspirin and you will have a much quicker turn-around time for change requests.

8. For each field that is displayed on the report, create a formula that returns its value. The formulas that you need to create are listed in Table A-5.

Table A-5. Initial balance sheet formulas.

Now that the basic formulas are built, let’s place them on the report for the first draft of the balance sheet.

9. For group #1, click the Insert Group button (or select the menu options Insert > Group).
10. Select the formula {@Account Type} and click the OK button.
11. For group #2, click the Insert Group button. Choose the formula {@Account Heading Number}, but don’t click the OK button yet.
12. For this group, we don’t want to display the account heading number on the report. Instead, we want to display the heading’s name. Click the Options tab and click the option Use Formula as Group Name. This opens the Formula Workshop dialog box.
13. For the formula, enter the following text:

{@Account Heading Name}

Notice that this formula references the Account Heading Name formula that was created from Table A-5. It does not reference the field directly. This is important because all changes to the heading name will be made in the formula. You never want to reference the actual data field anymore because then you would have to copy the field updates to this formula as well (and possibly forget to do it).

14. Click the Save and Close button to save the changes.
15. Click the OK button to save your changes for group #2.
16. Move the group #2 header field to the right a bit so that it is offset from group #1.
17. For group #3, click the Insert Group button. Choose the formula {@Account Number}.
18. Rather than display the account number on the report, we want to display the account name. Click the Options tab and choose the option Use Formula as Group Name. This opens the Formula Workshop dialog box.
19. For the formula, enter the following text:

{@Account Name}

20. Click the Save and Close button to save the changes.
21. Click the OK button to save your changes for group #3.
22. Suppress the Details section so that it is hidden for now.

At this point, you’ve created three groups for showing the account categories and the account number. However, for the account number you don’t want anything to be in the group header because you will show the summary data in the group footer. You need to move the group name field out of the header and put it in the footer.

23. Drag and drop the field Group #3 Name from the group header to the group footer. Move it to the right a bit so that it is offset from group #2.
24. Right-click on the grey area to the left of the Group Header #3 section and select Section Expert. Click the Suppress option so that it isn’t shown.
25. Preview the report and you will see the basic outline of the balance sheet. It should look similar to Figure A-5.

Figure A-5. Pages 1 and 2 (side by side) of the balance sheet preview.

You can see that the report is organized by the three group fields. The first group is the account type which lists the categories such as Asset, Liability, and Equity (group #1). The next group breaks down the accounts into categories such as Current Assets, Inventory Assets, and Current Liabilities (group #2).Within each category group is listed the actual account names (group #3).