Chris Hays's Reporting Services Sleazy Hacks Weblog

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.

Published Thursday, January 05, 2006 6:14 PM by ChrisHays
Filed under:

Attachment(s): GroupPageReset.rdl
Anonymous comments are disabled

This Blog

Syndication

News

This blog is provided 'AS IS' with no warranties, and confers no rights. All rights reserved. Some assembly required. Batteries not included. Your mileage may vary. Objects in mirror may be closer than they appear. No user serviceable parts inside. Opening cover voids warranty. Keep out of reach of children under 3.

© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Microsoft
Page view tracker