Another boring title for another important topic. 

When I wrote https://spacefold.com/lisa/2007/09/15/Reset-On-Group-(Page-X-of-XX-in-a-group-within-a-total-Y-of-YY-for-the-report)-SQL-Reporting-Services-Style-, as usual I thought that was the end of it. 

Well, not exactly.  I thought I was finished writing about that technique, and that I was going to go on and write more about debugging techniques and other related subjects in RS.  It's a subject dear to my heart and, I thought, of more general interest that the specialized trick involved in reset-pagenumbers-on-group and group page totals.

As usual, I was wrong —  this time on at least two counts. 

I was wrong to think I was going to have enough time to blog in the next couple of months to start the extended report debugging technique series I had in mind (although I did allude to it briefly here).  And I was wrong to think that people weren't going to need or  want a more explicit discussion of how can you do this specific thing.

So I sat down (three weeks ago) to write down the proper code for a full-blown walkthrough, and realized I had no idea how many different areas were involved and how long it was going to take me to explain. My heart (and free time) totally failed me.

OK, I'm ready now.  I may be posting in separate parts.

Reminder: the goal

We have a group or more than one group in an RDL. One group has "pagebreak after" set for it. We want to be able to write some expression like this in our page header:

="Page "& Globals!PageNumber &" of "& Globals!TotalPages &" Report Total " & vbCRLF &
  "Page " & [Group Page Number] & " of " [Group Total Pages] " for " & [GroupLabel]

Should be a breeze, right?

Well… you can't.

Well, as you may remember from the previous discussions I've linked to above, both here and on the forums, there's no easy way to do this in RS, partly because the page headers are figured out at a different time from the body content, I think.  There's a good reason for this: pagination is going to be different for each renderer.

So, and again please refer to the other posts for background, you have to hack it.  To get the group page numbers you can keep a collection of values yourself, and feed these to the page header.  To get group total page numbers you need to preprocess the report, so that you know what the group totals are at the end of the preprocess run, keeping them in a collection you can reference on the second run.

Now, we're going to look into all the things you have to do, in order to do that simple thing.

Setting the stage for this walkthrough

To simplify but cover all the necessary bases, I had to make some choices here:

• I have chosen to do this in a server-mode report, because that seemed to me to be the more significant case and offered the more difficult challenges, so I wanted to be sure and explain them.
• I have chosen to do this from the point of view of an ASP.NET client rather than a Winforms client — ditto.
• I'm not using a custom DLL, because that's beyond scope in this discussion.  I'm going to do everything in the custom code you can embed in the RDL. If you do use a custom DLL, it will actually make one step of this much more rational (whether you choose an XML approach or a database approach): setting the server security permissions properly.  Also, the code I've written here is pretty generic, so it is a good candidate for custom DLL usage.  If you want to do this in a local report, you might like to look at this post on using custom code in a local report.  If you want to do this in a custom DLL, you might like to look at this article on writing a custom code dll . Note that there are many different articles and posts on this subject, but I'm pointing to this one because it also looks at accessing database values in the custom code, which you might want to do as well (see next point).
• I have chosen to keep my preprocess information in an XML file, while in reality you might want to send the values to a database instead. This was largely for clarity/streamlining in the example, although I like to work in XML for stuff like this.  Again, taking the database route might actually make things easier for you than what you see here. There are some caveats you'll see in this walkthrough regarding the shared variables, which you might be able to get around if you persisted everything to a database, although I'm not sure it would work to try to do things there.  See next point.
• I am showing the code for two different renderings: HTML and PDF.  This seemed to me to cover the two significant types of rendering from the point of view of pagination: one interactive layout and one "printable" layout. Again, each renderer paginates differently and may internally do things in a different order.
  Showing the code for two styles of rendering may have added a little complexity to this code that you would not need. But if you do write it, I recommend you add a parameter representing "which rendering" to your report, just in case.

Let's be bad guys

Time to start hacking away at these tasks.  We're going to start with the RDL in this installment, and in a followup post I'll cover the client side.

Our reporting source data

We're going to use the MySQL World database, my favorite for tutorials, because it's a simple, clear structure.  You don't need the actual database for this (although you can d/l it from here for MySQL, or use my variant of the MySQL script if you're only running SQL Server for your data sources).  All you really need to know is that, in the example report, we are reporting on countries and their languages. To help you visualize, the SQL for single dataset in the report looks like this:

SELECT       
   Country.Name, Country.Continent, Country.Region, CountryLanguage.Language
FROM           CountryLanguage INNER JOIN
                   Country ON CountryLanguage.CountryCode = Country.Code
ORDER BY Country.Region, Country.Name, CountryLanguage.Language

See?   Nice and simple.

Our reporting goal

Our report design has an outer group on Regions, with page-break-after set for it, and an inner group on country name. Having multiple groups is not relevant to the example, but I set it up this way just to make sure a more complex case would work, and also because this was the scenario for one of the MSDN requests for information about this.  

I've included the Continent name above the Region information in the outer group header, for no really good reason except it looked good there.

So the report layout we're looking for is something like this:

SQL World Languages Page 3 of 30 Report Total Page 1 of 12 for Eastern Africa Region
Africa
Eastern Africa	Language
Burundi
	French
	Kirundi
	Swahili
Comoros
	Comorian
	…

… also pretty simple.

Things (between us)… not so simple.

Here's the actual report expression I'm using to get the red page number result you see in the layout above:

="Page " & Globals!PageNumber & " of "&
   Globals!TotalPages & " Report Total "& vbCRLF &
   "Page " & Code.GetGroupPageNumber(
   ReportItems!txtGroupHeaderItem.Value.ToString() ,
   Globals!PageNumber,Globals!TotalPages,
   Parameters!FileKey.Value,
   Parameters!PreProcess.Value,Parameters!ExportType.Value ) &
   " of "& IIF(Parameters!PreProcess.Value,"[PreProcessRun]",
   Code.ReadGroupPageTotal(Globals!TotalPages)) &
  " for " & Trim(Code.currentgroup) & " Region "

Hmmm.  The good news is there's only two functions in there. The first function fills out a bunch of variables and persists information if we're in the preprocess run. The second provides the preprocessed group total values during the second run.  The other good news is that you don't have to call any other custom code anyplace else in the report.

The bad news is… obviously this takes a bit of doing. 

Let's look at the parameters first.

I could actually have passed the parameters collection to the function instead of passing each parameter value individually, but it actually helps me to see them all individually.  For one thing, that way I remember to tell you what they all do. 

The first parameter in this report is FileKey.  This string has the default value "test". This is sort of a placeholder for a way to remind you to keep the results of different runs of this report distinct.  My placeholder is providing a way for you to pass the name of the XML file that will hold the preprocess results, which you could generate differently for each user session.  In your version, if you're handling persistence with SQL, you will still have to do something like this. 

Note that this technique will require some shared variables.  As discussed in earlier posts on this subject, I'm basing it off something that Chris Hays originally wrote — and FWIW I didn't see why he needed to use shared until I got to the PDF case.  If this is an issue in your case, I think there are ways to mitigate it; see http://gotreportviewer.com/ and look for the section on "supplying RDLs as a stream".

When we're all done building it, we're going to be calling this report twice, once to preprocess and once to provide results to the user. The PreProcess parameter tells the report which mode it's supposed to operate in. This boolean has the default value true.

Finally, you see an ExportType parameter.  This string has the default value "HTML", with "PDF" being the other value currently accepted by my code. This is required to handle differences between rendering types that may sequence things differently. 

FWIW this technique was much, much simpler to prepare when I wasn't working with the PDF renderer, the HTML renderer's sequencing was fine but the PDF renderer couldn't "see" my activities in the order I expected.  Much head-scratching ensued, followed by much more code than the original.

If you're curious, here are the principle differences: 

• The original code opened a file for writing at the beginning of the preprocess report run, appended to it throughout the run, and closed it at the end.  Seems reasonable, doesn't it?  But the pattern-of-use I experienced with the PDF Rendered indicated that this was not a great idea. Things seemed to be happening out of order.  As a result, when preprocessing, you'll see that I open and close the file on every "hit". Oh well. 
  This is another problem you might not have when persisting to a database rather than a file — or you might find that something similar happens if you try to hold onto a connection throughout the run.
• You'll see a PDFRunCount variable in this code.  What's that about?  It turns out that, at least when I invoked the PDF Renderer through URL Access, there were two sets of page numbers begin recorded; one for an internal HTML run and the other for the actual PDF pagination.  I may have done something terminally stupid here, but in this code I'm making sure to record only the correct values, using this indicator.

Now, about that code.

Here is the set of variables declared up-top in the custom code.  Some are used to keep track of things internally, like PDFRunCount.  But these variables can also be used directly in report expressions; you can see me doing that with Trim(Code.currentgroup) in the page header report expression, above.

    Public Shared offset As Integer = 0
    Public Shared currentgroup As String
    Public Shared previousgroup As String
    Public Shared groupNo As Integer = 0
    Public Shared fileName As String
    Public Shared PDFRunCount As Integer = 0
    Dim sw As System.IO.StreamWriter
    Dim doc As System.Xml.XmlDocument

Here's the first function, which collects group information, returns the current group's page number, and persists data between the runs so that the second run has group page totals.  It looks alarming, but actually it's pretty straightforward. 

Where you see "c:\temp" for a file path below, you will want to make sure you are looking at a path that your report code has permissions to write — wherever that is, is okay (and again this would be irrelevant if you persist to a database).

    Public Function GetGroupPageNumber(ByVal group As String, _
    ByVal pagenumber As Integer, ByVal total As Integer, ByVal filekey As String, _
    ByVal preprocess As Boolean, ByVal exporttype As String) As Object
        Try
            If pagenumber = 1 Then
                groupNo = 0 
                currentgroup = ""
                offset = 0
                If exporttype = "PDF" Then
                    PDFRunCount += 1
                End If
            End If
            If Not (group = currentgroup) AndAlso PDFRunCount <> 1 Then
                If groupNo = 0 Then
                    ' fileName = System.IO.Path.GetTempPath() & _
                    fileName = "c:\temp\" & _
                                filekey & ".xml" 'System.IO.Path.DirectorySeparatorChar &
                    If preprocess AndAlso pagenumber = 1 Then
                        sw = New System.IO.StreamWriter(fileName, False)
                        sw.WriteLine("<root>")
                        sw.Close()
                        sw.Dispose()
                        sw = Nothing
                    End If
                ElseIf preprocess Then
                    sw = New System.IO.StreamWriter(fileName, True) 
                    sw.WriteLine( _
                     "<Group  current='" & currentgroup &  _
                     "' lastPage='" & CStr(pagenumber – 1) & "'/>")
                    sw.Close()
                    sw.Dispose()
                    sw = Nothing
                End If
                groupNo += 1
                offset = pagenumber – 1
                previousgroup = currentgroup
                currentgroup = group
            End If
            If (pagenumber = total AndAlso preprocess) Then 
                sw = New System.IO.StreamWriter(fileName, True)
                sw.WriteLine( _
                   "<Group  current='" & currentgroup & _
                   "' lastPage='" & CStr(total) & "'/>")
                sw.WriteLine("</root>")
                sw.Close()
                sw.Dispose()
                sw = Nothing
            End If
            Return pagenumber – offset
        Catch Ex As Exception
            Return Ex.Message()
        End Try
    End Function

Besides creating the current page number for group as its result, the function creates an output file that looks like this:

<root>
   <Group current="Central Africa" lastPage="2" />
   <Group current="Eastern Africa" lastPage="6" />
   <Group current="Northern Africa" lastPage="7" />
   <Group current="Southern Africa" lastPage="8" />
   <Group current="Western Africa" lastPage="12" />
   <Group current="Eastern Asia" lastPage="14" />
   <Group current="Middle East" lastPage="16" />
   <!– etc –>
</root>

Besides creating the current page number for group as its result, as you can see above, the function is also kind enough to tell you what went wrong by returning information about the error instead of a page number.  That's why it returns Object instead of anything else.  The report engine is kind enough, in return, to manage the cast for you.  This is an invaluable practice; please take note for other hacky techniques no matter what you are doing.

Here's the code for function #2.  This one (very straightforward) only has to work during the second run.  During the first run, as you can see from the report expression above, this function is not invoked, and the word "[PreProcess Run]" is displayed instead.  You can see this in the report preview window, if you leave the PreProcess parameter value set at its default of true.

 Function ReadGroupPageTotal(ByVal total) As Object 
     Dim g As String, xa As System.Xml.XmlAttribute, thisLastPage As Integer 
     Try 
         If doc Is Nothing Then 
             doc = New System.Xml.XmlDocument() 
             doc.Load(fileName) 
         End If 
         If doc Is Nothing Then 
             g = "NO DATA" 
         Else 
             xa = doc.SelectSingleNode( _
                "/root/Group[@current='" & currentgroup & "']/@lastPage") 
             If xa Is Nothing Then 
                 g = "NO DATA" 
             ElseIf groupNo = 1 Then 
                 g = xa.Value 
             Else 
                 thisLastPage = CInt(xa.Value) 
                 xa = doc.SelectSingleNode( _
                   "/root/Group[@current='" & previousgroup & "']/@lastPage") 
                 If xa Is Nothing Then 
                     g = "NO DATA" 
                 Else 
                     g = (thisLastPage – CInt(xa.Value)) 
                 End If 
                 If thisLastPage = total Then 
                     doc = Nothing 
                     ' could clean up file here 
                 End If 
             End If 
         End If 
     Catch Exc As Exception 
         g = " error reading: " & Exc.Message() 
     End Try 
     Return g 
End Function

This code is using System.Xml.  Depending on your method of persistence, you may have to provide an explicit reference to the appropriate assembly or assemblies. You do this in the Report Properties dialog, References tab.  I'm using pretty basic functionality here, and I don't need to provide any class information in the dialog, just the assembly information.

Now for the publishing

Now you just have to get this technique to work on the server.  As explained earlier, you'll want to make sure you're writing to an "acceptable" location when you create your XML file or, if you're writing to a database, that you've provided appropriate credentials for your connection.  Other than that, deploying this report should be pretty much like deploying any other report you've written… except for one thing.

You also have to make sure that your code has permissions to perform the requisite actions.  This is one part that is definitely more sensible to do in a custom DLL, because you can add the permissions in a much more "targeted" way.  Because I'm doing this example completely in custom code, I need to issue a "blanket" permission for custom code in report expressions, I can't target it more accurately than that.

To do this, you have to change something in the rssrvpolicy.config file (you'll find it in your Reporting Services\ReportServer webapp home directory).  You'll find a code group that looks something like this:

<CodeGroup class="UnionCodeGroup" version="1"
   PermissionSetName="Execution"
   Name="Report_Expressions_Default_Permissions"
   Description=
   "This code group grants default permissions for code in report expressions and Code element. ">
  …

… and, if you are following this walkthrough exactly as written, you'll need to change the PermissionSetName attribute value you see above from Execution to FullTrust.

Stay tuned.

That's pretty much all she wrote.  If you want to preview in Visual Studio or the Report Manager you can change the values of the parameters, making sure to keep them in synch: always call the preprocess run first and then the second run, make sure that you use the filekey to allow values to be maintained separately for different rendering systems and make sure that your preprocess+second run are paired for each rendering system.  In other words, you can't preprocess for HTML and then call the second run for PDF; the page numbers won't be right.

What does all this look on the client side, how do you get it to look seamless to the user?  Not as hard as it sounds.  I'll do that in a second installment of this walkthrough.