Reset Page Number On Group
Question:
How can I reset my page number back to 1 every time I get a group break?
Answer:
Resetting the page number on group breaks isn't natively supported, but it can be achieved by tracking group breaks in a shared variable and subtracting off the page offset of the first page of the group from the current page number.
Step 1: Make sure there's a textbox in the report which contains the group expression
Step 2: Add shared variables to track the current group and page offset
Shared offset as Integer
Shared currentgroup as Object
Step 3: Add a custom function to set the shared variables and retrieve the group page number
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If Not (group = currentgroup)
offset = pagenumber - 1
currentgroup = group
End If
Return pagenumber - offset
End Function
Step 4: Use the function in the page header or footer
=Code.GetGroupPageNumber(ReportItems!Category.Value,Globals!PageNumber)
Note: Because this uses static variables, if two people run the report at the exact same moment, there's a slim chance one will smash the other's variable state (In SQL 2000, this could occasionally happen due to two users paginating through the same report at the same time, not just due to exactly simultaneous executions) If you need to be 100% certain to avoid this, you can make each of the shared variables a hash table based on user ID (Globals!UserID).
A full working sample of page number reset on group is attached.