SSRS adding page numbers to page break on grouped Item

As I work through converting Crystal Reports to SSRS reports at times I amazed at some of the functionality that is not yet available.   We have been using Crystal to create several different types sales documents.  The easiest way I have found to create Sales Document type reports is with a header/ detail type of scenario. You can pull the header information for the Sales Document and group on the document number.  Then you add page breaks between each instance. To account for multiple line items that will most likely be on sales documents add a sub report for the line items.  simple enough.  Then the request for page numbers comes along.  Seems reasonable that you would like to have page 1, 2, 3 on a sales document, but in this situation the page numbers would be based on the entire report not on the grouped items.  So if a sales document is three pages long OOB Report Projects or Report Builder will not just allow you to add the pages numbers for the grouped items.

Now for the solution.

When in the design mode of Report Builder or Visual Studio select Report >Code

Here is my code:

Shared groupName as Object
Shared PageNum as Object
Public Function GetPageNumber(group as Object) as Object

  If (group = groupName)
    PageNum =PageNum +1
else
    PageNum = 1
  End If
  Return PageNum
End Function

You will set two variables to store the values behind the scenes.

Then in your function you will pass in the name of the group you are on by passing the value you are grouping on into the function.  Then if the group matches the one that is stored the page number increments and if it does not it takes it back  to 1.

Then on the report where you would like to add the page number add a textbox.  Add an expression to the textbox that calls the GetPageNumber function. You will pass in the ReportItem field you are using to create the group

=Code.GetPageNumber(ReportItems!GroupName.Value)

Just an fyi on any custom code I add to the code tab has a redline when I try to call it in the expression.  Do worry.  It will still work.

This is a fairly simple approach to solving this problem.

Comment me if you have any problems.

About dorothyjarry

Super Dots
This entry was posted in SSRS. Bookmark the permalink.

6 Responses to SSRS adding page numbers to page break on grouped Item

  1. psionic says:

    Thanks for your solution, I wonder if its possible to get also TotalPages within the group.. thanks

    Like

  2. Paul says:

    I can get it to compile now, but the resulting page number is always 1. This is probably because you never set the value of the Shared variable “groupName”. Why do you expect it to be the same as the passed variable “group” when you never set it to any value? The expression group=groupName is always false, unless you pass Nothing as group.

    Did you test your solution for a report that is more than one page long? Does it work? If so, can we see all the code?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s