How to suppress subtotals on reports.
Q: Barbara Gorches writes: I am creating a report that needs to subtotal my detail records on several different levels.
Level 1 – project phase total
Level 2 – project total
Level 4 – grand total
This works fine, but when we have a lot of start-up projects there is only one line for the details, one line with the project phase totals and then the project totals line.
Is there a way to hide the project footer based on the count of the record in the total, i.e. suppress the project total when the count is 1.
A: Yes, this can be done by placing a RunningSum control in the Detail section, and then making the subtotal control(s) in the group footer invisible if the count is less than 1. See rptOrders in the Northwind 2000 Stepped Report database (it is based on the old Northwind database).
The txtDetailCount control has a control source of =1, Running Sum set to Over Group, and Visible set to No. The OrderID Footer Format event procedure is listed below:
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Debug.Print “Order count: ” & Me![txtDetailCount].Value
If Me![txtDetailCount].Value > 1 Then
Me![txtOrderSubtotal].Visible = True
Else
Me![txtOrderSubtotal].Visible = False
End If
End Sub
You can also make an entire section invisible, which is handy if there are a lot of controls in the section. See the Section Help topic for the syntax for referencing sections.
