The first problem with the cross-tab is that it only shows one year. This is because the record selection formula that was written in Tutorial A-2 is only allowing journal entries that have the same year as the report parameter. The formula should allow the past two years to be included in the output. We can fix that by changing the record selection formula.

25. Select the menu options Report > Selection Formulas > Record. This opens the Formula Workshop.
26. The part of the formula that filters on the journal entry date needs to be modified to allow the previous year to be displayed. Modify the formula to the following:

{Account.Account Type ID} in [5, 4] and
{Journal_Entry.Journal Entry Type ID} <> 9 and
Year({Journal_Entry.Date}) in {?Year To Print}-1 to {?Year To Print}

27. Click the Save and Close button to save your changes.
28. Click the report preview button to refresh the report. If you are prompted about refreshing the report data, click Yes so that the prior year data is read into the report. The report preview should now show both years in the cross-tab.
29. The next step is to hide the grand totals for the rows and columns. These are unnecessary and use up too much report space.
29. Right-click the top left corner of the cross-tab object and select Cross-Tab Expert.
30. Click the Customize Style tab at the top of the dialog box.
31. At the bottom of the dialog box, in the Grid Options section, check both of the options Suppress Row Grand Total and Suppress Column Grand Total. This disables the grand totals for the entire cross-tab.
32. When the cross-tab object displays multiple groups, it makes two columns and displays each group name in its own column. For the income statement, we want the section names (e.g. Gross Margin, Operating Income, etc.) to be listed below the categories. This is done by turning on the option to indent the group names.
32. Right-click the Row #1 Name field (the Section ID) and select Row Options > Indent Labels. This reformats the cross-tab rows so that the section names appear under the account class summaries.
33. Let’s change the formatting of the categories that are bold. We only want the sections to be bold.
33. Select the Row #2 Name field and click the Bold button to turn off bold formatting.
34. Select the Row #2 summary field (in the last column) and click the Bold button to turn off bold formatting.

If you preview your report at this point, it should be looking really good now. The rows are indented properly so that you can see the difference between the sections and the categories. There are also two years listed in the columns so that this is truly a multi-year income statement.

There are two big problems left with the report and they both deal with the summary balances listed on each row. The first problem is that the expenses are listed as negative numbers. While this is certainly accurate, one of the rules of the income statement is that only positive numbers are to be shown. Negative numbers need to have their sign flipped so that they look like positive numbers. Even though they are still treated as negative numbers within the calculations, the display of them should be positive.

The second problem is that the section balances are not correct. As we discussed in the previous tutorial, section balances are a running total of all the account class balances that came before it. Unfortunately, the cross-tab object is showing a sub-total of the account class balances within each section. We can’t tell it to show the running total balance because Crystal Reports doesn’t allow multiple summary functions for the same field. We have to use the same summary function for every row.

To get around these problems, we have to use formulas within the cross-tab object to manually flip the signs of the expense balances and to create manual running total balances for the sections. In addition to that, we have to overwrite the cross-tab summary balance with our own running total calculations. Sound complicated? It is. This is where it helps to have a solid understanding of the advanced cross-tab techniques.