Before getting into the details of creating the formulas, let’s first review the cross-tab specific functionality we need. This will help refresh your memory and it makes a good reference to look back on. Table A-9 lists the cross-tab formulas we’ll be using and gives a short description of how to use each one.
Table A-9. Cross-tab formulas used in the income statement.
| Formula | Description |
|---|---|
| GridRowColumnValue() | Returns the current value of either the row or column summary field. The fieldname parameter is a string that must match one of the summary fields for the rows or columns. If you get an error that the field is not valid, you probably didn’t type in the full name exactly (including the symbol). Open the Cross-Tab Expert and click on the Customize Style tab. The acceptable fields are shown in the Rows and Columns lists. Make sure you enter the field name exactly as it is shown in the list. |
| Display String | An option on the Format Editor dialog box that lets you use a formula to override the numeric value in the cross-tab cell. The formula has to return a string. |
| CurrentFieldValue | Returns the value of the cross-tab cell currently being printed. We will use this to keep a manual running total of all the balances in the cross-tab report. Note: it needs to be converted to a number using the ToNumber() function. |
- The first step of fixing how the balances are displayed is making the expense accounts display their balances as positive numbers. To do this you have to determine how to identify which accounts are expense accounts. By looking at the Account table in the Xtreme.mdb database, we can see that the revenue accounts have an Account Class ID value of 17 and expense accounts have multiple Account Class IDs. So it’s easier to test for an account being “not a revenue” since it only includes one number. We will flip the sign for any account that has a class ID not equal to 17. For your own database, if you aren’t lucky enough to have a single field that identifies this, you will have to use the account range to identify expense accounts.
- Right-click on the summary field for the Row #2 summary field (located in the last column) and select Format Field. This opens the Format Editor dialog box for the Account Class sub-total field.
- Click on the Number tab and click the Customize button. This opens the Custom Style dialog box.
- Find the option Reverse Sign For Display and click on the Formula button next to it. This opens the Formula Workshop dialog box.
- Enter the following formula:
GridRowColumnValue("@Account Class ID") <> 17;
- Click the Save and Close button to save your changes.
- Click the OK button to close the Format Field dialog box.
- The expense accounts will now be displayed as positive numbers on the report.
