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.