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

Figure-02, The report regions

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

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

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

Figure-05, Custom code tab

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

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

• 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

</font></p> <p><font face="Arial Narrow">Dim oSSRHelper as ReportingServiceUtils. GroupTotalPage() = New ReportingServiceUtils.GroupTotalPage()</font></p> <p><font face="Arial Narrow">

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)

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

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

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)

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

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

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

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)

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.

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>
''' <remarks></remarks>
Public Class GroupTotalPage
 ''' <summary>
 ''' Dictionary to hold the Group Name and the page number at which this group ends
 ''' </summary>
 ''' <remarks></remarks>
 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>
 ''' <remarks></remarks>
 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>
 ''' <param name="group"></param>
 ''' <param name="groupCurrentPageNumber"></param>
 ''' <remarks></remarks>
 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 <see>_GroupAndMaxPageNumberWithInTheGroup</see>
 ''' and computes the result.
 ''' <see>_GroupAndMaxPageNumberWithInTheGroup</see> 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 <see>_GroupAndMaxPageNumberWithInTheGroup</see> 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>
 ''' <returns></returns>
 ''' <remarks></remarks>
 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 <see>_GroupWithTotalPageNumber</see> 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>
 ''' <param name="key"></param>
 ''' <returns></returns>
 ''' <remarks></remarks>
 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>
 ''' <remarks></remarks>
 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

Advertisement