1. The next step is to fix the section balances. They are currently displaying the sub-total of their related account classes and we need them to display a running total that runs from the top to the bottom of the cross-tab. This requires creating a formula which calculates a manual running total. The difficulty is that within the cross-tab object the running total needs to calculate the total for a column of the cross-tab object and then reset to zero when a new column starts. Since each column represents a different year, we want each year to have its own running total calculation. If the running total doesn’t get reset for each column, then the balance from one year would carry over into the following year and the results would be incorrect.
2. The final section of Chapter 13 showed how cross-tabs are calculated by starting at the top of the first column and moving down. After the last row is printed in the first column, then it moves to the top of the second column. For the running total to be reset for each column, there needs to be a way to identify when a new column is being printed. You can test for the end of a column by checking if it is printing the last row. Or if you want to check when a new column is starting, you can do it by checking if it is on the first row. A final way to do it is to keep track of the previous column printed and see if it is different than the current column being printed. If so, you know that the cross-tab is starting a new column.
3. For this report, since the account section label in the row never changes, the easiest way to test for a new column is to look at the first printed account section and see what data is being displayed. By testing to see if the current section is equal to the first section printed, you know that the cross-tab is at the top of a new column. For this tutorial, I know that the first section is called Gross Margin and it has a Section ID of 1. I will use this in the running total formula as the criteria for resetting the running total balance for each year.
39. Right-click on the summary field for the Row #1 summary field (located in the last column) and select Format Field. This opens the Format Editor dialog box for the Section ID sub-total field.
40. Click on the Common tab.
41. Find the Display String option and click the Formula button beside it. This opens the Formula Workshop dialog box.
42. Enter the following formula:

global numbervar RunningTotal;
//Reset the running total balance for the first section in each column
if GridRowColumnValue("@Section ID")=1 then RunningTotal := 0;
RunningTotal := RunningTotal + ToNumber(CurrentFieldValue);
//Display the running total in the current cross-tab cell
ToText(RunningTotal) & " ";

1. This formula uses the function GridRowColumnValue() to get the current Section ID value. If it is 1, we know that we are at the first row and the running total is reset to zero. If not, the running total balance is unchanged. The next line uses the CurrentFieldValue function to get the current value being printed (the section’s sub-total) and adds it to the running total balance. Finally, the running total is printed in the cross-tab using the ToText() function. Thus, we used the Display String option to overwrite the section’s sub-total balance with the manual running total balance.

Notice that the formula also appends a space after the ToText() function. This is so that the section balance lines up with the sub-total balance that is shown above it. Since the Display String option requires you to return a string value and not a number, you are responsible for the formatting. If a field has special formatting in the cross-tab object, you have to format the string to replicate it.

43. Click the Save and Close button to save the formula and close the Formula Workshop.
44. Click the OK button to close the Format Field dialog box.
45. We also want to make the section balances appear bold so that the font matches the section name. Select the Row #1 summary field and click the Bold button.
46. If you’ve made it this far, then the hardest part it over. We just need to clean up the formatting so that it looks more professional. We want to remove the grid lines from the cross-tab object. Grid lines are not part of the standard formatting for an income statement.
46. Right-click in the top left corner of the cross-tab object and select Cross-Tab Expert.
47. Click on the Customize Style tab.
48. At the bottom right corner of the dialog box, click on the Format Grid Lines button.
49. At the bottom left corner of the Format Grid Lines dialog box, uncheck the option Show Grid Lines.
50. Click the OK button two times to return to the report designer. The grid lines should no longer be visible.
51. Select the Column #1 Name header field and click the Align Center button so that the year is centered in the column.
52. Save the report so that you don’t lose any of your hard work.

Preview the report and it should look similar to Figure A-15.

Figure A-15. Multi-year income statement.