Walkthrough: TOC+Local Reports, or The RDLC with the Fringe on Top

C and I continue to have medical adventures this year, and I've been a bit distracted in the past few weeks.

But I continued to work on Fulano's problem, although it was clear that local reports were going to take a completely fresh look at pre-processing a report; the ReportViewer renderer doesn't seem to play by exactly the same rules as the Reporting Services server-side renderer. 

As you may recall from our server-side adventures in preprocessing, each renderer does have the option of processing report output events in a different manner as suits their content, so the server-side preprocessing code takes separate paths for PDFs and HTML renderers.  It was a bit disheartening to think I was going to go through that again in the local mode context.

As I started to work through this, therefore, I told myself I would accept certain limitations. These turned out to be not nearly as drastic as I originally thought, for example, I didn't have to impose serious page break structure on the results.  But I did find myself humming "They've gone about as far as they can go [Kansas City]" from Oklahoma! a few times; hence the even-odder-than-usual title of this post.

Oh what a beautiful TOC

What are the essential features of a Table of Contents?  The general idea (and it's a classic one) is this: a TOC shows up first in the order of the report pages, but it lists the page numbers for the rest of the report, which in most cases hasn't been built yet. 

The general solution (which makes it a classic application for report preprocessing) is to run the report once, record the page numbers, and then run it again with the TOC properly filled out.  To do this "properly" means to render the report exactly the same way the first time as the second, so that the page numbers you record the first time exactly match the pagination of the final result.

Corralling the requirements

1. Following Fulano's requirements (you can read them in full in the previous post's comments), we're doing this in an RDLC, so I've used RS and VS 2005 here.  I'm not entirely comfortable with the VS 2008's ReportViewer implementation with regard to localmode, because it wasn't really updated to handle the rendering of 2008-specific contents such as tablix, so I figure its event sequencing might change a lot when MS updates it properly. Last I heard this was scheduled to happen in VS 2010.

2. Fulano also needs this to work with PDFs.  This has a tendancy to collide with the "proper" pre-processing in an illustrative example, because the first run you're going to get is going to be HTML. For the purpose of this example, I can give PDFs the right behavior by imposing the following layout restrictions:

  • Margins of 0 all the way around 
  • Interactive size has to be exactly the same as page size
  • The two page sizes have to allow for the margins with which the PDF renderer is going to "frame" the actual report content. 

In my case this meant a page and interactive size of 7.5in * 10in, which will permit the PDF renderer to add its frame for an 8.5in * 11in final result.  It's going to look a bit funny in the HTML rendition, but the page numbers are going to come out right in the final PDF.

This limitation means that I didn't have to put in all kinds of extra code to handle any differences between PDF and HTML render-event sequencing on the local side, neither did I have to test it exhaustively for HTML; each renderer took a separate effort in server mode and I was not willing to go through that again. Instead, I worked on the PDF event sequence, observed what would work for this renderer for the final result, and stopped testing there.

3. I also set the reportviewer to appear in DisplayMode.PrintLayout, to help ensure the same pagination.  I'm not actually sure if this is necessary in the final version or not, but I was unwilling to take chances and test hugely again.  Don't worry, you'll see the code for this, below.

4. I think that Fulano's final code is going to involve instancing the ReportViewer programmatically, so he can feed it the right parameters (to tell the report code when it's running in preprocess mode, to record information, and when it's doing the run that displays the final values).

Consider that, in local mode, the reportviewer isn't going to prompt for parameters. The reasons, aka "rationale", defy understanding, but you do have to remember that local mode represents the "cut-down" version of RDL processing.  With RDLCs, MS is making RDLs available without a ReportServer instance (and license) behind it so they don't have any obligation to do anything at all here. 

I've simulated this behavior by exposing a "Reprocess" button on my form.  So, you see the report come up with 0's in the TOC in the form, you wait (please!) for it to finish rendering, and then you press the button.  The button flips the "preprocess" parameter for the report to "off" and then refreshes the report.  After this action, you see the right result in the reportviewer and you can Save/Export the PDF with the right information.

There are a number of interesting things to note here. 

  • I tried loading the report and then flipping the param and refreshing right in my form initialization code, but I couldn't get this to work properly without more work/event handling, because the full contents of the report did not get rendered before the refresh, leaving me without the right/complete set of TOC values persisted.  (You might be able to do this.)   But if it seems to work for you and you don't use events to make it work, be careful and try your method with a couple of hundred pages before feeling confident.
  • You might also be able to handle it in the ReportExport event, once you're sure that the report has properly rendered the first time — but it looked like it wouldn't work.  I am not 100% sure the localmode PDF renderer actually re-runs the report completely.  I have a feeling it doesn't, so the page number data used in your TOC page doesn't get re-evaluated.  It looked like the PDF exporter was picking up the XML version of the content (which has 0's for page numbers in the TOC from the preprocess run) and generating the PDF result from there.  This would make sense and be efficient. 
  • The code I'm going to show you in the page footer didn't work the same way at all when I put it in the page header.  I might have done something wrong, and I would be interested to hear what you experience, but for now: don't assume the two formatting band collections are processed the same way.

5. I've used the Winforms client in this set of experiments, simply because I used a web scenario last time.  There is no reason why the localmode renderers should work differently in the Webforms client, and, even if it did, you should be able to instance the Winforms client silently/programmatically even on a web server to produce a PDF if you are not interested in the visual display of the control in the first place.

The most likely difference in the two environments is permissions problems.   To persist the page numbers in the preprocess run, you have to write to a file or a db, and to read the page numbers in the second run the way I'm doing it (with XML), you have to use an XML object.  You'll see a little code here to take care of that, because even in the fat client the issue doesn't go away, and it should be similar for the Webforms client.  In the server-side version, I covered this pretty thoroughly.

6.  Fulano's requirements are to have different "report chapters" as subreports.  He proposes a "carrot" textbox preceding each one in the main report.  This should  probably work, using similar ReportItems references to what you see here — and if he has questions about exactly how to apply it, he can ask here; he's not shy <g>. But, for the purposes of a sample report, I've just created a set of tables in the main report, and included the reference textboxes directly in each of the tables.

Dancing the Dream Ballet

Act I – Sample Setup

The sample report is, once again, drawn from MySQL Tutorial World database.  It's pretty simple. 

Our TOC (with a blue background in this screen shot) precedes a table of Countries, a table of Cities, and a table of CountryLanguages.   The significant textboxes for our purposes, which read and write TOC information, have a light yellow background.

The TOC has one entry for each table, because I think that's what Fulano wants, so I've made the TOC a list. But I've included enough information in the persisted values so that you could potentially have a second level in the TOC (for example, on what page do the Cities for each Country group start?). In this case, it would be a table data region instead.

The report has one parameter to handle "which pass are we rendering", similar to what we've done before.

The report has an appropriate reference to the  .NET framework XML classes that I'm using in the code.

 

Act II – Report Expressions

There are not a lot of fancy report expressions in this report. 

  • =Code.RecordPage(
       Parameters!PreProcess.Value,
       Globals.PageNumber,
       Globals.TotalPages,
       ReportItems)

    The footer has a single expression that reads values out of various report layout control items during the preprocessing run, and stores them. Notice that I pass the whole ReportItems collection, rather than mentioning any particular textbox explicitly.  If I wrote more complex code supporting this function, it could be really elegant, based on some control naming conventions and interrogating the report items collection in a very generic way. I don't think it's necessary for the example, and if you don't need to look through the whole collection generically there's no reason to slow things up.  So, I've compromised, by showing you the generic argument here, and then referring to individual items directly in the code.

  • ="page " &
       Code.GetPageInfo(Parameters!PreProcess.Value,"Country")

    The TOC page number items in the second run have to get the values from the values persisted in the preprocess run. They use an expression like this, with a second parameter that indicates which TOC value each control needs to receive.  If you are doing a more complex TOC, you probably need a second parameter here, which will be table-driven. 

  • ="Cities" &
       StrDup(1000,".")

    This is one more non-essential, but interesting, expression, which I used  in the TOC list entries.  Fulano specified:

    7). The TOC should look something like this:
    1). Section 1……………4
    2). Section 2……………15
    3). Section 3……………23
    etc. 
         

    … and, while that format with the "." leaders is pretty easy to do, it sometimes gives people trouble to figure out.  So, here's the trick: I've simply used far more "." characters in the expression than I will ever need, and set "Can grow" to False for this expression.  Its right edge butts right up against the page number control, and it gets truncated at the right length.

Act III – Custom code in the report

The report code sets up the following variables:

Public fileName As String = ""
Public sw As System.IO.StreamWriter = Nothing
Public doc As System.Xml.XmlDocument = Nothing
Public debug As Boolean = False
Public currentPage As Integer = 1

The function called directly from the expression in the report footer does the following to persist values during preprocessing:

PublicFunction RecordPage(ByVal tPreProcess AsString, _
  ByVal tPage As Integer, ByVal tLastPage As Integer, ByVal r As ReportItems, _
  Optional ByVal tFileKey As String = "stest") As String

   currentPage = tPage
   Dim ans As String
   If Debug Then
      ans = tPreProcess
   Else
      ans = ""
   End If

   If
tPreProcess = "Yes" Then
  
      If
tPage = 1 Then
         SetRecording(True, tFileKey)
      End If  

      ' now determine whether there is anything to do
      ' here I'll look for an item appropriate to each subreport or table
      Dim theType As String, theItem As String
      If LEN(CStr(r("txtCountryName").Value)) > 0 Then
         theType = "Country"
         theItem = r(
"txtCountryName").Value
      ElseIf LEN(CStr(r("txtCityHeader").Value)) > 0 Then
         theType = "City"
         theItem = r("txtCityHeader").Value
      ElseIf LEN(CStr(r("txtLanguageHeader").Value)) > 0 Then
         theType = "Language"
         theItem = r("txtLanguageHeader").Value
      End If
      If theType Is Nothing Then
         ' skip
      Else
         ans = ans + RecordSectionInfo(tPage, theItem, theType, "", tFileKey)
      End If
      If (tPage = tLastPage) Then
         ans = ans + SetRecording(False, tFileKey)
      End If 
   End If
Return ans

End Function

The SetRecording function you see referred to above sets up the beginning and end of the XML file persisting values for this run, and the RecordSectionInfo writes the instance information to the file. Here's the full set of supporting functions:

Function SetRecording(ByVal tStart As Boolean, ByVal tFileKey As String) As String
   Dim w As String, ans As String
   If tStart Then
      w = "<root>"
   Else
      w = "</root>"
   End If
   Try
      Open(tFileKey)
      sw.WriteLine(w)
      Disposal()
   Catch ex As System.Exception
      ans = ex.Message()
   End Try
   Return ans
End
Function

Function
RecordSectionInfo( _
   ByVal tPage As Integer, ByVal tInfo As String, ByVal tType As String, _
   ByVal
tCode As String, Optional ByVal tFileKey As String = "stest") As String

   Dim ans As String = ""
   Try
      Open(tFileKey)
      sw.WriteLine("<Item currentPage='" & CStr(tPage) & "' currentType='" & _
      tType & "' currentItem='" & tInfo & tCode & "'/>")
      Disposal()
   Catch ex As Exception
      ans = ex.Message()
   End Try
   Return ans
End
Function  

Sub Open(ByVal tFileKey As String)
   If
Len(fileName) = 0 Then
      GetFileName(tFileKey)
   End If 
   sw = New System.IO.StreamWriter(fileName, (currentPage <> 1))
End
Sub

Sub LoadDoc(Optional ByVal tFileKey As String = "stest")
   If
Len(fileName) = 0 Then
      GetFileName(tFileKey)
   End If
   If doc Is Nothing Then
      doc = New System.Xml.XmlDocument()
      doc.Load(fileName)
   End If
End Sub

Sub GetFileName(ByVal tFileKey)
   Dim
auth As New System.Security.Permissions.FileIOPermission( _
   System.Security.Permissions.PermissionState.Unrestricted)
   auth.Assert()
   ' fileName = System.IO.Path.GetTempPath() & _
   fileName = "c:\temp\" & tFilekey & ".xml"
   'System.IO.Path.DirectorySeparatorChar &
End Sub

Sub
Disposal()
   sw.Close()
   sw.Dispose()
   sw = Nothing
End Sub

The XML output looks something like this (notice again that I'm recording more than I actually need for my simple TOC in this example):

<root>
<
Item currentPage='2' currentType='Country' currentItem='Brunei '/>
<
Item currentPage='3' currentType='Country' currentItem='Spain '/>
<!–…etc… then the cities for each country
  (with 'currentItem' meaning 'current country group' in my example) 
–>
<
Item currentPage='9' currentType='City' currentItem='ARG'/>
<
Item currentPage='10' currentType='City' currentItem='ARG'/>
<
Item currentPage='11' currentType='City' currentItem='AUT'/>
<
Item currentPage='12' currentType='City' currentItem='BGD'/>
<
Item currentPage='13' currentType='City' currentItem='BLR'/>
<
Item currentPage='14' currentType='City' currentItem='BRA'/>
<
Item currentPage='15' currentType='City' currentItem='BRA'/>
<!–
…etc… –>
<
Item currentPage='122' currentType='City' currentItem='ZWE'/>
<!–…followed by languages for each country, the same way… –>
<
Item currentPage='123' currentType='Language' currentItem='ARE'/>
<
Item currentPage='124' currentType='Language' currentItem='AZE'/>
<!–
…etc… –>
<
Item currentPage='154' currentType='Language' currentItem='ZWE'/>
</
root> 

… and, during the "real" report run, we read values out of the XML file as follows in the TOC report expressions:

Function GetPageInfo(ByVal tPreProcess As String, ByVal tType As String, _
   Optional ByVal tItem As String = Nothing) As String
   ' we're not using code, but we could have more info in the TOC than we currently do
   Dim thePage As String = "0", xa As System.Xml.XmlAttribute
   If tPreProcess <> "Yes" Then
      Try
         LoadDoc()
         thePage = "[NoData]"
         If doc Is Nothing Then
            ' problem
         Else
            xa = doc.SelectSingleNode( _
               "/root/Item[@currentType='" & tType & "']/@currentPage")
            If xa Is Nothing Then
               ' problem
            Else
               thePage = xa.Value
            End If
         End If
      Catch Exc As Exception
         thePage = " error reading: " & Exc.Message()
      End Try
   End If
   Return thePage
End Function

Act IV – The form code to invoke the report

Finally, here's the load I used on the load of the form, of course also filling the datasets required for this local-mode-rendered report:


Me
.ReportViewer1.LocalReport.ExecuteReportInCurrentAppDomain( _ 
   System.Reflection.Assembly.GetExecutingAssembly().Evidence)

Me.ReportViewer1.LocalReport.AddTrustedCodeModuleInCurrentAppDomain( _ 
   "System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")

Me.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)

… and here's the code under the "Reprocess" button, to run the report a second time:

Private Sub ReProcess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles ReProcess.Click
   Dim p(0) As Microsoft.Reporting.WinForms.ReportParameter
   p.SetValue(New Microsoft.Reporting.WinForms.ReportParameter("PreProcess", "No"), 0)
   Me.ReportViewer1.LocalReport.SetParameters(p)
   Me.ReportViewer1.RefreshReport()
End Sub

Not "All Er Nuthin" this time

Well, there you have it. I think a reasonably-extensible proof of concept version of what Fulano needs to do and where he needs to go, and as fully-worked as I can do it in my (ahem) spare time. While the actual code turned out to be completely different, the general approach in the code, as anticipated in my original walktrhough, as you could use in a server-side report.

It's a wacky idea, sure, but wacky extensions to what's possible are what I'm here for.  If you know the musical I've been quoting for, you probably have already guessed that, in my very early days, I was only interested in playing Ado Annie, not Laurie.