In the last article, we created an Expense Report grouped by cost center using the “Specified Order” option in the Group Expert to change the order and group names (002-Direct, 001- Reimbursable, 003-Non-Reimbursable). Now, we will take a look at using Running Totals and conditional suppression of sections to display different subtotals.

For this report, we would like to display both the subtotal by cost center and the running total of expenses.  In the Section Expert, highlight the Group Footer and click the “Insert” button to add a new group footer below the existing group footer section.  We now have Group Footer #1A with our group subtotal and a blank Group Footer #1B, to which we will add the running total.

To create the new field, right-click “Running Total Fields” in the Field Explorer and click “New”.  In the “Edit Running Total Field” window, add the net change field as the “field to summarize.”  Leave all other options as they are (Evaluate on each record, Reset never) and click OK.  Now, add this new Running Total field to the Group Footer #1B, just below the group summary in GF #1A.

For the first group on this report, we don’t need both the group subtotal and the running total since they are the same.  We will suppress the Group Footer #1B for the first group.  Back in the Section Expert, highlight the Group Footer #1B and click the “Formula” button to the right of “Suppress” on the “Common” Tab.  In the “Formula Workshop”, expand the Print State node in the Functions window and select GroupNumber.  Complete the formula:

GroupNumber=1;

and save.  Now the Group Footer containing the Running Total is suppressed for the first group.

All we need now is the label for the Running Total in the Group Footer #1B.   Add a new Formula Field called “GF1B”.  In the Formula Workshop, we will use the GroupNumber function again along with an IF statement to display the appropriate text for each group.  For this simple report, the easiest way to do this is with the following formula:

if (GroupNumber = 2) then    “Direct and Reimbursable” else    “All Expenses”;

(Since we have suppressed GroupNumber 1, we don’t have to worry about the label for this group.)

Conditional suppression can be applied to fields as well as groups and is a flexible and powerful method for manipulating the information displayed on your report.