Generating reports with group level pagination (i.e. Page x of xx) using MS.Net Report viewer control in local processing mode

A common use case in (batch) report creation is generating reports with group pagination i.e. reset page number on group.

Example Scenario

Batch generating customer invoices. The total number of pages per invoice may be 1 to n depending on the number of line items in each invoice. Each invoice should get printed with page number in the top / bottom like page 1 of 2, page 2 of 2 etc. The next invoice should start in a new page with page number starting from 1 again like page 1 of 3, page 2 of 3 and page 3 of 3.

Unfortunately this is not supported out of the box in VS.NET2008 report viewer control and also doesn’t seem to be addressed in VS.NET2010 report viewer control. This article attempts to provide a workaround solution with fully running customizable source code along with detailed documentation. The idea for this solution is based on the discussion forum posts and blog posts by Lisa Nicholls and Chris Hays (please see the references section at the end of this article), on top their ideas, I implemented my own logic to overcome certain difficulties to provide a complete running solution with code and documentation which also covers the VS.NET development environment setup details to reference external .Net assemblies inside rdlc’s custom code which is again based on the wonderful detailed blog post by Mohamed Sharaf.

If you find this article is useful then all the credit goes to

  1. Nicholls
  2. Chris Hays
  3. Mohamed Sharaf

Without their ideas, code and references, I wouldn’t have completed this solutions. Hats of to the above gentlemen’s

Enough complaints and theory let us jump into the sample application walkthrough.

Requirement

We need to print work order reports in batch. Each work order report will have 1 [or] many pages with the following regions

Header – Show up in all the pages with work order number which is the grouping identifier and report generation date

Body

Static part (only show up in first page)

Dynamic (Running in to pages based on the size of the text). In this case it is just a text area. This can be list control

Footer – Show up in all the pages with group level pagination. For each group the page numbers will be printed as Page 1 of 5 (i.e. Page x of xx) where the xx determination is the challenging part that is what this article is addressing

Figure-01, The final work order report

clip_image002

Figure-02, The report regions

clip_image002[15]

Solution Architecture

The above said requirement is achieved using the following

  1. VS.NET report viewer control using .rdlc files in local processing mode
  2. Custom embedded .net code used inside .rdlc for field level expression evaluation
  3. The above said (point # 2) custom code calls an utility class which is outside the reporting project’s namespace (separate class library)

Following sections will explain the

  • Actual logic to determine the group pagination
  • VS.NET setup while referencing external class libraries from embedded custom codes in .rdlc

Logic to determine the group pagination

There is no way we can determine the total number of pages in each group unless we preprocess the report. So in this case, we do two time report rendering, first one is pre-processing (a.k.a silent processing) and the next one is for actual rendering. Refer the source code which is well documented and also refer the links mentioned in the reference section.

 

Figure-03 , The high level flow

clip_image002

While during the pre-processing stage, we need to keep track of group and page number in each page run of the report and for that I used a shared dictionary variable resides in a utility class library with methods exposed for Adding in to the dictionary, consuming from the dictionary based on the key,. Clearing the dictionary etc… The utility class (the instance variable name is “oSSRHelper”) is called from the custom embedded code subroutines reside inside the .rdlc file. As shown in the figure-03. These subroutines are consumed as field level expressions (Refer Figure-11 ~ 16) and source code

Figure-04 – Custom .rdlc code

clip_image004

The custom code tab can be reached as shown in Figure-05

 

Figure-05, Custom code tab

clip_image006

If we use custom code inside .rdlc which references external assembly then there needs certain pre-requisites if we forget to satisfy those prerequisites then the VS.NET will not compile the source code and which is weird. We also need to satisfy other security pre-requisites otherwise the runtime will throw security error (related to Code Access Security, reflection etc.) which makes sense.

Satisfying VS.NET to compile the source code

The external assembly (in our case, ReportingServiceUtils.dll) which will be consumed from .rdlc should be kept in the VS.NET private assembly folder as shown below

Figure-06, VS.NET private assembly folder

clip_image008

Referencing external assemblies from .rdlc custom code

Follow the below screen shots to reference the external assembly from .rdlc reports in VS.NET designer

Figure-07, Referencing external assemblies from .rdlc

clip_image010

  • Click the browse button and pick the external assembly. Once successfully done then it will show up in the references section as above
  • Hand type the class name in the left side (fully qualified name)
  • Hand type the instance variable name of your choice in the right side. Based on this the report will create a instance like this for you using reflection

Dim oSSRHelper as ReportingServiceUtils. GroupTotalPage() = New ReportingServiceUtils.GroupTotalPage()

If the above is done, you can access its methods and properties with out explicitly creating instance inside the custom .rdlc code like the one shown below (Note: No intellisense support inside the code tab and it supports only VBA kind of code)

Figure-08, accessing the external assemblies’ class (instance variable)

clip_image011

Overcoming runtime security exceptions

Since, the embedded code needs to be compiled at runtime through reflection;

  1. It may be required to sign your external assembly with strong naming
  2. Telling the runtime that the external assembly referenced is from trusted source by including this code snippet in the calling code (report viewer code behind) as shown in the figure-xx. This step is mandatory

Figure-09

clip_image013

3. Based on the permission i.e. trust level the code is executing, you may run into security issues, the runtime may compile the embedded code through reflection for which we need to hint the assembly that, allow partially trusted callers in the assemblyinfo.<vb/cs> file as shown in the Figure-10

Figure-10

clip_image015

Sample Application Source Code

I hosted the complete source code in Google code @ http://code.google.com/p/ms-dot-net-report-viewer-group-pagenation/downloads/list

It has two projects

  1. Sample Client application project, which hosts the reportviewer, .rdlc. It uses object data source (dtoWorkOrder.vb) and the actual data is sterilized (XMLSerialization) as collection of dtoWorkOrders.
  2. ReportingServiceUtils project, utility class library whose dll is consumed by the .rdlc embedded code

Follow the screen shots for the key fields in the .rdlc files which have field expressions. This helps to focus on the key fields in the report.

Figure-11, Master Report (parent)

clip_image017

Figure-12, Sub Report which is called from parent for each group

clip_image019

Figure-13, Field responsible for populating the group total page number into a Dictionary in each page run

clip_image021

Figure-14, Field responsible for printing the current page number in the group i.e. Page x of xx

clip_image023

Figure-15, Field responsible for printing the Total page number for a group i.e. Page x of x x (which is getting the value from the dictionary whose key is group name / identifier)

clip_image025

Figure-16, a hidden field which holds the group name/ identifier in all the pages and whose value is consumed in the header otherwise if we directly refer it from header, the group name will be null if the page is not the first page.

clip_image027

Source Codes:

Utility Class

Imports System.Collections.Generic
''' <summary>
''' VS.NET reportviewer helper class to identify and print group page numbers
''' NOTE:
''' This class currently supports for windows application. If you want to use it web application then introduce a logic
''' to uniquely identify the dictionaries because it is using shared / static varibales. This can be easily achieved by having
''' one more dictoinary i.e. Dictionary(Of String, Dictionary) whose key will be the logged on user name and the value will be the
''' actual dictionary hold the data.
''' </summary>
''' 
Public Class GroupTotalPage
 ''' <summary>
 ''' Dictionary to hold the Group Name and the page number at which this group ends
 ''' </summary>
 ''' 
 Private Shared _GroupWithRunningMaxPageNumber As New Dictionary(Of String, String)
 ''' <summary>
 ''' Dictionary to hold the Group Name and the Total number of pages in that group
 ''' </summary>
 ''' 
 Private Shared _GroupWithTotalPageNumber As New Dictionary(Of String, String)

 ''' <summary>
 ''' Adds the group and the page number into the shared/static dictionary collection
 ''' The groupname / id being the key and pagenumber as value
 ''' If the groupname already exists then the GroupCurrentPageNumber value will be reset with the
 ''' surrent value. By doing so, always we will have the maximum value in each group
 ''' which will be the group's total number of page once the report is processed completely
 ''' This method is being called from the code in the rdlc file
 ''' </summary>
 ''' 
 ''' 
 ''' 
 Public Sub Add(ByVal group As String, ByVal groupCurrentPageNumber As String)
 If _GroupWithRunningMaxPageNumber.ContainsKey(group) Then
 _GroupWithRunningMaxPageNumber(group) = groupCurrentPageNumber
 Else
 _GroupWithRunningMaxPageNumber.Add(group, groupCurrentPageNumber)
 End If
 End Sub
 ''' <summary>
 ''' This Gives the Group and the Total number of pages within the group
 ''' Internally this function reads the data stored in the dictionary _GroupAndMaxPageNumberWithInTheGroup
 ''' and computes the result.
 ''' _GroupAndMaxPageNumberWithInTheGroup will have the data like below which is the input for this function
 ''' [group-A,04] ------- (1)
 ''' [group-B,09] ------- (2)
 ''' [group-C,10] ------- (3)
 ''' This function iterates the _GroupAndMaxPageNumberWithInTheGroup collection and computes the
 ''' total number of pages by substracting the page number with the previous page number like below
 ''' At Iteration-01
 '''          Copys as it is from _GroupWithRunningMaxPageNumber into _GroupWithTotalPageNumber
 '''          The result will be like [group-A,04]
 ''' At Iteration-02
 '''          It substracts (2) - (1) i.e. 09-04=05
 '''          The result will be like
 '''                                [group-A,04]
 '''                                [group-B,05]
 ''' At Iteration-03
 '''          It substracts (3) - (2) i.e. 10-09=01
 '''          The result will be like
 '''                                [group-A,04]
 '''                                [group-B,05]
 '''                                [group-C,01]
 ''' </summary>
 ''' 
 ''' 
 Public Function GetGroupWithTotalPageNumber() As Dictionary(Of String, String)
 If _GroupWithTotalPageNumber.Count = 0 Then
 Dim preKey As String = String.Empty
 For Each dic In _GroupWithRunningMaxPageNumber
 If String.IsNullOrEmpty(preKey) Then
 _GroupWithTotalPageNumber.Add(dic.Key, dic.Value)
 preKey = dic.Key
 Else
 _GroupWithTotalPageNumber.Add(dic.Key, (CInt(dic.Value) - CInt(_GroupWithRunningMaxPageNumber(preKey))).ToString())
 preKey = dic.Key
 End If
 Next
 End If
 Return _GroupWithTotalPageNumber
 End Function
 ''' <summary>
 ''' This gives the Total number of pages for a given group(key)
 ''' This looks up at the dictionary _GroupWithTotalPageNumber and returns the value for the matching key
 ''' This is called from the code residing in the rdlc for each page to print the total number of pages
 ''' </summary>
 ''' 
 ''' 
 ''' 
 Public Function GetTotalPageNumber(ByVal key As String) As String
 If Not String.IsNullOrEmpty(key) AndAlso _GroupWithTotalPageNumber.ContainsKey(key) Then
 Return _GroupWithTotalPageNumber(key)
 Else
 Return String.Empty
 End If
 End Function
 ''' <summary>
 ''' Resets the shared variables.
 ''' </summary>
 ''' 
 Public Sub Clear()
 _GroupWithRunningMaxPageNumber.Clear()
 _GroupWithTotalPageNumber.Clear()
 End Sub

End Class

 

 

Reference:

 

  1. Adding custom code to Local Reports in Visual Studio.NET 2005 (Problems & Solutions) by Mohamed Sharaf
  2. SQL Reporting Services – Page X of XX counts in header by Lisa Nicholls
  3. Reset Page Number On Group by Chris Hays

10 thoughts on “Generating reports with group level pagination (i.e. Page x of xx) using MS.Net Report viewer control in local processing mode

  1. Hi Senthal,

    I found very interesting your example. I want to know il your example can work on visual studio 2010 and what about of reportserviceUtil.dll ?

    Thank you

    Like

  2. Hello Senthal,

    Thanks a lot for this, I searched everywhere, and it seems that only you had a decent workaround for it.

    However, I have sometimes an issue when my PageTotal should be “1” it goes blank. I tried to resolve it by remake the expression for the textfield towards: =IIf(ReportItems!PageNumberTotalTextBox.Value = “”, “1”, ReportItems!PageNumberTotalTextBox.Value), but then I get an error code: The Value expression for the textbox ‘textbox1’ refers to more than one report item. An expression in a page header or footer can refer to only one report item.

    Do you have any idea on how to solve this?
    I tried workaround to combine textboxes in the same textbox, but with the same problem.

    Thanks in advance,

    Gert

    Like

  3. Hi Senthal

    I want to use your method to show page numbers but I could’nt find the ReportServicedUtils.dll.
    Could you please help me with this?

    Thanks in advance.

    Satya

    Like

  4. Hello Senthal,

    Your solution looks very very intersting, but do you know if it is possible to make your solution work in webform on a local report ?
    There is no event “RenderingComplete” on the web reportviewer control and I can’t find any event that can be used in replacement.

    Thanks,

    Laurent

    Like

  5. Hi Senthal

    I want to apply your method of page number but found no link on the current page for the dll or a sample project.

    Would it be a problem to email it to me?

    Like

  6. I am sorry I did not see the code above. When the report was upgraded to 2010 it stopped working do you know why this would happen.

    Like

  7. Hi, I am successful in resetting the page numbers for each group in rdlc but I am not getting a solution to get the Total number of pages in each group. Can anyone help me out with this? I am using VS2010

    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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s