Tutorial A-5. Comparative Multi-Year Income Statement

Anytime you create a report that shows two consecutive years side by side, someone always comes along and says, “Hey – can you add a third column that shows the difference between the two years?”. Income statements are no exception. In fact, having a third column to show the changes between the two years is more of the rule than the exception. Let’s modify the previous tutorial so that we add a third column which shows the change from the previous year to the current year.

The cross-tab object has built-in totals for both the rows and columns. It can summarize all the values printed in the entire cross-tab. For this tutorial, what we need the cross-tab to do is subtract the first column (the prior year) from the second column (the current year) and print the difference in the third column. Unfortunately, there is no way to set an option on the cross-tab to make it automatically calculate the difference between two columns. It can only add them together. We have to create formulas that do it manually instead. The good news is that we can use what we learned from the previous tutorial to make this work. The bad news is that, once again, this is going to get very tricky, very quickly.

We need to figure out how to make the cross-tab object subtract the first column from the second column. The first time I built a report like this, I created an array for each column in the cross-tab and used a formula to populate it as the cross-tab was built. This allowed me to put another formula in the total column which subtracted each row and printed the difference. Although this worked really well, the coding is incredibly complex.

Since the cross-tab is designed to add numbers together and not subtract them, a second idea is to make the balances in the first column negative numbers so that when the cross-tab adds them to the second column, it is really subtracting them. This sounds like a good idea except that negating the amounts in the first column is also going to make them show up on the report as negative numbers. To get around this, we will have to flip the signs so that they appear as they did originally.

Negating numbers and flipping signs can get pretty confusing. In addition to that, if you recall from the previous tutorial, we already flipped the signs for the expense accounts so that they show up as positive numbers on the report. This means that, in some cases, an amount will get its sign flipped twice. If we aren’t careful, it will be very easy to lose track of which amounts get flipped and which ones don’t. The key to making this work is to make as few changes as possible to the original formulas. The changes that are made have to be the same for every formula. By staying consistent with each formula, it will be easier to apply the changes and reduce the risk for making mistakes.

1. Open the report “Income Statement MultiYear.rpt” from the previous tutorial.
2. Save the report as Income Statement Comparative.rpt so that you are working on a new copy.
3. The first step is to have the cross-tab display the Total column. Since the Total column will be updated after every change we make, this allows us to double-check our work at each step throughout this tutorial.
3. Right-click on the top left corner of the cross-tab object and select Cross-Tab Expert.
4. Click on the Customize Style tab. Un-check the option Row Totals On Left and the option Suppress Row Grand Totals. This enables the display of the grand total column and puts it in the right-most column.
5. Click the OK button to save your changes and close the Cross-Tab Expert dialog box.

If you preview the report, you’ll see that the Total column on the right adds both years together. While this is accurate, it is not what we want. We need to negate all values for the previous year so that they are effectively subtracted in the Total column. We will modify the @Amount formula so that the amounts for the previous year have their sign switched.

6. In the Field Explorer window, right-click on the Amount formula and select Edit. This opens the Formula Workshop.
7. Update the existing formula to the following code.

//Determine if the amount should be negated or not
NumberVar FlipSign := 1;
if Year({Journal_Entry.Date}) < {?Year To Print} then
FlipSign := -1;
if {Account_Type.Account Type} = "Asset" Then
if {Journal_Entry.Debit Or Credit} = "Debit" Then
{Journal_Entry.Amount} * FlipSign
Else
{Journal_Entry.Amount} * -1 * FlipSign
Else
if {Journal_Entry.Debit Or Credit} = "Credit" Then
{Journal_Entry.Amount} * FlipSign
Else
{Journal_Entry.Amount} * -1 * FlipSign

1. This formula creates a variable called FlipSign and stores a 1 in it. It uses the {?Year To Print} parameter to find out if the journal entry date is for the current year or the previous year. If it is the previous year, the FlipSign variable becomes -1. The FlipSign variable is how we will negate the prior year balance.
2. The second portion of the formula is the same formula that we used for the other income statement. But this time, each amount is multiplied by the FlipSign variable. If the date is for the current year, the FlipSign is 1 and this has no affect on the amount field. But, if it is for the previous year, multiplying the value by FlipSign will negate the amount.
8. Click the Save and Close button and click the OK button to save your changes and return to the report designer.