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.
Hi, i want to know if their’s a way to do the 2 runs at the same time.. (to create and to generate) like.. if i say,
IIF(Parameters!PreProcess.Value,IIF(Parameters!PreProcess.Value, Code.ReadGroupPageTotal(Globals!TotalPages)”[PreProcessRun]”),
Code.ReadGroupPageTotal(Globals!TotalPages))
— here, we told him 2 times that preprocess is true.. first time we create it and second we want to run it at our screen.. (this thing don’t work, .. do u think that their’s a way to run it like that?)
Thank you,
It won’t work to say that preprocess is true twice, correct. Different things are being done on the two runs.
But, yes, there is a way to do it. You have to invoke the report yourself instead of using the Report Manager interface. In Part II of this walkthrough, you can see one way to do that.
First, thanks for ur help…
i’m a biginner in programming.. and i would like to know.. how can i integrate que “Parte II” in this code.. what should i change and alose, the other code is using RS web service format and i’m using the URL.. my question is, can i just take the other code.. change the variable and integrate the part of “doing the second run” in this code?
Thanks again
The two parts were meant to be used together, one showing you the server side and the other showing a client that would make the two consecutive calls. So the answer to your question “can they be integrated” is yes <smile>.
Also, while it’s true that I used the RS web service in this client example, I personally am happier using URL access, and have shown that approach in other posts. Is there anything in particular that you need help with, here? Maybe you can show me what you have been trying that does not work?
ok.. i took ur code of “part I” and put it in the “code” of RS and after, like u said, i refer it to my text box..
”
=”Page ” & Code.GetGroupPageNumber(
ReportItems!textbox6.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))
)
”
but after.. with ur second part, i dont know where i can put it in this code.. i’m lost about this thing..
thanks again,
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
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:\WINDOWS\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
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
which part or ur second code can we use it? and where we have to put it? i’m lost about that! :S
thank you
You don’t “put the second part into the first part”. You don’t “adjust the first part”. The two parts work together but they are not being invoked by the same object. Part I code stands alone, is invoked by the report during processing. Part II code is the code from the *client* that calls the report.
The *client* calls the report twice and then shows only the second report result to the user. How are you showing the report to the user now?
by .pdf
Sorry, Rolan, that is not what I meant. What type of client are you using to fetch the report? Are you using Report Manager? Is this a web application? a desktop client application? How does the client *get* the PDF, currently?
oh.. i’m using Reporting Services. and it is a desktop applications..
and i also have another question, and sorry for bugging u again, where can i invoked the client code..? do u know where is it in reporting services?
thank you again
You *are* invoking the client code, in your desktop application. Your desktop application is the client. You are using Reporting Services. Somehow your client application is calling reporting services. You haven’t yet told me how, but it is! <g>
Rolan… why don’t you write me an email (see Contact page) and explain more about your application and I’ll write a separate blog post (if generally useful information) or just an email (if not) to clear this up. This comment thread is getting silly.
ok.. i’ll do, and thank you very much by the way.
the contact page tell me this thing when i’m sending u an email:
TechSpoken (>L<‘s site)
The page or service you requested is not available!
i’ll write you everything here.. but u can answer on my email.: rolan.smeha@hotmail.com ..
Hi, and sorry for bugging u again with my project. Ok.. i’ll explain u what i’m doing. i’m doing an application to invoked different number.. and i want, for each number, a new pagination.. i did with ur code (part I) the pagination, but i have to do it twice.. (because of the preprocess, like you know..).. so i refer to ur (part II) but i’m lost about it… hum.. the first part i put it , it the propreties of the report, section code and i did the reference too, like u told us.. I also identify my parameters.. and everything is working well.. but i’m not able to do the second part.. i dont know where i have to put it.. how i have to do it.. i’m lost about that part. :S
this is the code in my fields.. to show it at the screen..
=
“Page ” & Code.GetGroupPageNumber(
ReportItems!textbox6.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))
and this is the code that i use in the Code:
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
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:\WINDOWS\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
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
but, i dont know what i have to do wit ur second part..
thank you
Rolan,
Hi L.
great solution, great explanation of the steps! Congratulation!
Just implemented/adapted it into my own report within 20 minutes, everything works fine!
cheers
Sven!
Super! thanks Sven.
Lisa
I have a similar problem where I have a report that I want to add a new page numbering with each new group (i.e. Group one has Page 1 of 5 and when Group two started it has Page 1 of 10). Does this code work for printing the report as I described? Additionally does this entire code go into the report property window under “Code”? I am fairly new of SQL Reporting Services so please treat me as if I have no KNOWLEDGE as I don’t!!! If you need further information please let me know.
Hi Wersey,
That’s a good question about the print renderer. Like the PDF and other renderers, it has the right to make its own decisions about what order to execute things in, so it might *not* work. The short answer is that the PDF would print properly! I will give this a try later.
Basically, because each renderer needs its own testing and behavior, I only tested and worked on two, to illustrate that fact.
Yes, the code godes into “Code”. It is possible to write it separately and attach a custom module to it, but you’re going to run into some additional issues for permissions, so I wouldn’t bother, at least at the beginning. Thanks for writing.
OK here’s your answer, now that I have had a chance to remember what I was thinking about when I wrote this, and what the strategy was…
Working directly from Report Manager interface, it would eventually work right, if I created another argument for ExportType, which currently just supports PDF and HTML, and did the work! <s>.
But I don’t particularly like printing directly from Report Manager for *any* report, because of the activeX control installation requirements. And also, this *specific* report isn’t suitable for the Report Manager interface because the user has to set the params properly (first, do the preprocess, then the “real” run explicitly). So if I were exposing this report, for printing or any other reason, it would be with my own client code calling the report — see Part II of the walkthrough, which shows you what happens: first you call it for preprocess, then you call it for “real”.
Given that scenario, I now remember why I *didn’t* bother testing the print renderer. As said in my first response, the PDF will print properly. SO if the user of my client interface wanted to print, I would invoke the PDF branch of the logic and then print the PDF. I hope this makes sense.
Lisa:
I have the following in my “CODE”:
The SafeDivide was already there and works great!
Public Function SafeDivide(Numerator as Double, Denominator as Double) as Double
If Denominator = 0 then return 0
Return (Numerator / Denominator)
End Function
I added this from your code:
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
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
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
I am however getting two errors:
[rsCompilerErrorInCode] There is an error on line 12 of custom code: [BC30002] Type ‘System.Xml.XmlDocument’ is not defined.
[rsCompilerErrorInCode] There is an error on line 68 of custom code: [BC42020] Variable declaration without an ‘As’ clause; type of Object assumed.
Since I am not 100% of the code I would ask you to look at it and tell me what I did wrong!
Thanks.
Hi there
For the first error, I suspect that the only thing you did wrong was not reading the instructions in the blog post that explain how to reference System.xml. As follows:
>>
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. [camera image]
<<
The little camera has a screen shot attached to it showing exactly where to do it.
For the second error, looks like it is probably here:
<code>
Function ReadGroupPageTotal(ByVal total) As Object
</code>
I don’t know why you are getting this complaint and I am not, unless it’s because I didn’t have the ByVal either. But all you need to do is change it to
<code>
Function ReadGroupPageTotal( _
ByVal total <font color=red>As Integer</font>) As Object
</code>
… okay?
Hope this helps.
Lisa:
Your the BOMB!!! Thanks. That was exactly the problem. AWESOME. I did read the instructions but when you said I’m using pretty basic functionality here, and I don’t need to provide any class information in the dialog, just the assembly information. [there is a little camera image here] I assumed if I was using ‘standard” functionality that SQL Reporting Services knew what to do, HAHA, SILLY ME! You were right that was the problem once I looked at the little camera it was exactly what I had missed. Here is what I ended up with and it worked!!!!
Public Function SafeDivide(Numerator as Double, Denominator as Double) as Double
If Denominator = 0 then return 0
Return (Numerator / Denominator)
End Function
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
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
Function ReadGroupPageTotal(ByVal total As Integer) 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
Thanks again Lisa you are a great resource for someone who is new to SQL Reporting Services. Talk to you again soon, I am sure.
Lisa
One more question do I need this statement also and if so where do I put it?
=”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 ”
Do I need this statement also and if so where do I put it?
=”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 ”
It’s a statement to be put in a textbox, probably in your page header, club. When it is evaluated at runtime in your report layout, the result is the red part of the example I show, which is this:
<font color=red>
Page 3 of 30 Report Total
Page 1 of 12 for Eastern Africa Region
</font>
Hope this helps.
Is there a version of this code for Local Reports. I need to create a Table of Contents in a Local Report and think this might work for me…can you help?
Thanks!
Hi Jack,
The reason I did this for server reports rather than local is that it’s generally harder to work out this kind of stuff on a server (especially permissions). I think it *should* work for local and you might be able to do it better, because you have more control of things.
For example, you could create the pagination information by inserting a SQL table rather than an XML document more easily in a local report, and then in the second pass you just give it a dataset that has this TOC information in it. In the first pass, the TOC dataset has no records, so the report “understands” implicitly that it is in the first pass just by looking at that count. Does this help?
Hi, I concur with Jack. Your article was GREAT, but it left us “Local Mode folks” out in the rain looking in on the party…would there be any possibility that you could help us (I’m sure there are more than just the two of us) develop a way to figure out the pagination for a Local Mode Report? Nothing too fancy, just a way to be able to say “Section D is on page 7 of 19.”
Thanks for your help,
Fulano
Again, guys, I don’t see any reason why this wouldn’t work “local” and I think it might be easier in local mode than server mode. My problem is: working a hugely long day and 7 of them per week <s>. These walkthroughs take a long time to write so generally I’m answering the shorter-to-answer questions <g>. But if I have any time that I’m <b>not</b> working this weekend, I’ll give it a shot.
Hi again, thank you for your prompt reply. I certainly don’t want to add any more work to your already overloaded schedule, and I would like to say that I really, really appreciate the courtesy you offer us to help us with our problems.
Having said that, I do have to admit (and I’m sure Jack will agree) that if you do find the time, I would he most grateful!!! I’ve been struggling with this problem for well over a month now (a lot of sleepless nights…) and your article is the only one that is even close to offering some hope to solve the problem.
Thanks for helping us!!!
Fulano
BTW, I’m not asking for anything with a bunch of Groups or anything like that…I simply need something simple. I have a Master report that has several sub-reports in it and I want to know were each sub-report begins. Much like what Jack want to do is create an executive summary of sorts that you can explain to the reader where they can find “Section D.”
Thanks,
Fulano
Thanks Fulano, a precise spec and a goal/challenge is always helpful <g>
Hi Lisa,
This is the approach I’ve been trying to accomplish and I think it’s a little bit simpler than looking into each Group or Subreport.
My Master report has about 10 Subreports within it. I simply need to know where each Subreport begins. So, I thought of adding a Textbox immediately before the each report that would act as a “carrot.” If I can find the page location of each carrot (Textbox), I can pass that to my executive summary or even to a table of contents (which would work just as well).
I think that your idea of a dataset and maybe a datatable would work nicely. I could find the location of each Textbox and pass that to the datatable. Then on the second pass, I could read my page values out of the datatable into my report’s body.
Does that seem like a reasonable idea?
Thanks,
Fulano
Oh, one thing that I didn’t mention above. The “carrot” Textbox would contain each of the section headings, such as “Section A”, so finding the “carrot” wouldn’t be that difficult since you know what you’re looking for. The part that I consider difficult is determining the page location of each carrot.
Fulano
I have been totally unsuccessful running this code in report builder 2. I seem to have no file system access. I have attempted to edit security in XP .Net Framework 1.1 and 2 but have not hade any success. I have attempted to follow instructions from MS regarding setting security in .Net Framework and Reporting service config files. I am only running in Report Builder 2. I was able to make the code work on my local machine last year but no longer can. Any help VERY appreciated.
Sure, but I think it’s easier in a local report… and haven’t had the time to test that out. At some point I probably will.
Hi Lisa,
I am facing the same problem like i need to show the group page total in footer but not getting how to do exactly. How to run a report twice or in silient mode.
I need to show Page 1 of 5 (Group Total for a invoice)
Please Help me….
[quote][/quote]
Hi Lisa,
I’ve implemented the group page totals tip in the past, my question is the current implementation uses the rs2000 and rs2005 compatable web service. Do you think it is possible to implement the same code using the ReportExecution2005.asmx service??
Many thanks,
Joe
Hi Lisa,
I am facing the same problem like i need to show the group page total in footer but not getting how to do exactly. How to run a report twice or in silient mode.
I need to show Page 1 of 5 (Group Total for a invoice)
????? Nope. This is not my own project. I finish it in a teamwork, so we spare the job in every of us.
lisa,
I’m getting this error:error reading: unexpected end of file has occued. The following elements are not closed
this is the code i’m using in report properties
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
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(“<?xml version=’1.0′ standalone=’yes’ ?>”)
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) & “‘></Group>”)
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) & “‘></Group>”)
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
Function ReadGroupPageTotal(ByVal total As Integer) As Object
Dim g As String, xa As System.Xml.XmlAttribute, thisLastPage As Integer
Dim Location As String
Try
Location = “START”
If doc Is Nothing Then
Location = “LOAD”
doc = New System.Xml.XmlDocument()
doc.Load(fileName)
End If
If doc Is Nothing Then
g = “NO DATA”
Else
Location = “LOAD GROUP”
xa = doc.SelectSingleNode( _
“/root/Group[@current='” & currentgroup & “‘]/@lastPage”)
Location = “GROUP LOADED”
If xa Is Nothing Then
g = “NO DATA”
ElseIf groupNo = 1 Then
Location = “GROUPNO1”
g = xa.Value
Else
Location = “GET LAST PAGE”
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 = Location + ” error reading: ” & Exc.Message()
End Try
Return g
End Function
this is in the tet box in the page footer using expression
=Code.ReadGroupPageTotal(Globals!TotalPages)
i’m trying to fix this i just want to display last page if this is the last page if not continue to next page.
Amy Rochester/Blue Pits, I missed this earlier:
>>Isnt adding the Page number twice kind of redundant
No, we’re not adding it twice. One is the page number in the GROUP, one is the page number in the FULL REPORT (GLOBAL). And some people need this. (Additionally, some people need only the GROUP part, which is what this post is about, the GLOBAL part is native behavior.
Brook, I really don’t have time to go through your code line by line, so can you please tell me WHAT in my code you changed (if anything — but I assume something otherwise why dump it into your comment?), and WHERE in the report (body, page footer, header, table header, whatever) you have put that expression?
That’s if you really need to do this at all, and I’m not sure you do. I don’t understand this comment:
>>i’m trying to fix this i just want to display last page if this is the last page if not continue to next page.
… but it sounds like maybe your goal is simpler than the code you are using to resolve it, and there might be a better way.
Also, I would need to know what VERSION of SSRS you’re using? If you’re using 2008 R2, you may not need this at all, even if your goal is complicated :-). Please see http://spacefold.com/lisa/2010/09/19/Group-Totals-Redux-Updating-this-technique-in-RS-2008-R2
Highly descriptive article, I loved that bit. Will there be a part 2?
Hi,
I am getting the error “The report references the code module ‘System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’, which is not a trusted assembly”
Kindly please help me to solve this error.
>>
I am getting the error “The report references the code module ‘System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’, which is not a trusted assembly”
<<
The blog post you are commenting on has *two* different reference links to instructions on using custom code and assemblies in a local report, specifically put there because I don’t use RDLC’s enough to pay a lot of attention to this. If this is not enough to help you, I think you should find exactly what you need in at least one of the samples at http://gotreportviewer.com/; look on the menu on the right side for the two samples involving “Custom Assemblies”.
Marcus H/Nancy 12, the part II of this walkthrough is (and has been for almost as long as this part I was available, I wrote them at the same time) here http://spacefold.com/lisa/2008/03/03/Report-Preprocessing-SQL-Server-Reporting-Server-Group-PageTotals-Part-II-A-Walkthrough-on-the-Client-Side.
>L<
The code is working fine except the file part.
I am getting an error . Request for the permission of type “System.Security.Permissions.FileIOPermission,mscorlib, Version=4.0.0.0, Culture=neutral,PublicKeyToken=b77a5c561934e089”
The report is in vs2010 I am not sure what I can do as you had mentioned a file to be edited in you case but in vs2010 I am not sure what file can be changed to give me rights.
Angelo, if you are a real person, check out http://gotreportviewer.com/; look on the menu on the right side for the two samples involving “Custom Assemblies”. If this doesn't help you, write to my email address with details and I will try to help.
Thanks lisa I found the solution for the security in another of your posts I added the following in the code.
[b] Dim auth As New System.Security.Permissions.FileIOPermission( System.Security.Permissions.PermissionState.Unrestricted) [/b]
Although I am currently facing another problem with the code when I am switching display modes from normal to print layout I get an “object reference not set to an instance of an object”. I can't quite get why this is happening. Also this isn't always happening which makes it more difficult to identify. Would it be possible to provide a sample project which could possibly be used as a guideline.
Also I would like to point out that this is being used in winforms.
Hi Angelo,
I have a feeling that the switch between print layout and normal layout is causing a refresh that is getting the two runs out of synch. Print layout is really a separate renderer I think or if it is not it probably behaves like one. It has to paginate differently, just like the PDF renderer does. Right?
It is very easy to imagine this being an intermittent problem. I'll do the best I can to help you — within the limits that I have, which are pretty strict right now.
I can't supply a project for you. For one thing, my home dev environment is really messed up right now — a tale of woe you don't need to hear, but I can't just whip out a clean project for an example like this.
Also, it probably wouldn't help you. The problem will be in some specific detail of your work that my project won't go near.
Also, I have a rule about helping people with peculiar things like this: *you* have to supply the code and strip it down as much as you can; chances are, you will find your own problem when you do this because it will disappear in the simpler case and you'll figure out what the critical element is. If you do not find it, and you send me the stripped down project, I will try to help (and you will have learned something in the process of simplifying the question, even if you don't solve it yourself).
I don't need your data, I would be able to dummy that up even if your report is quite complicated. But your project will tell me a lot — not just whether it's web or winforms, but whether it's local mode or server mode, what version, etc. For the scenario described in this particular post, all of that matters.