Dynamically loading reports in ReportViewers

by Lisa Nicholls Wed, September 19 2007 10:34

I've been saying that I was going to post about what I learned while writing the RDLDocumenter utility, its test form, and other XMLRSDocs-related bits, because that journey represents a practical FAQ for many different the Reporting Services programming areas.

One of those areas is how you load local report definitions (RDLCs) dynamically in a ReportViewer control.  When this question was asked again this week in any "any ideas?"  or "can you post an example" sort of way, I decided it was time to write a walkthrough of how RDLDocumenter's demo and test interface handles these chores.

Look See what I'm talking about? at the various buttons in the form, and you get some idea of why RDLDocumenter's test harness form gives dynamic report loading such a workout:

  • second button from the top needs to switch between data sources, loading data from different XML data files, each representing a different RDL to be documented
  • third button from the top switches to reporting using a SQL Server instance, rather than an XML data file, to load data about multiple RDLs to be documented
  • second and third button also switch between RDLCs (Report1.RDLC handling data for a single report and ReportMany.RDLC, slightly more complex, for multiple reports)
  • fifth button switches between locations for the RDLCs, so that you can substitute your own documenting layouts and format from a set on disk from the two embedded resources included in the test form executable.

There's one additional challenge, which you can't see in the buttons, forcing the test harness to go "that extra mile": 

  • when it switches between data sources, TestHarness.EXE must also dynamically load data connection objects, as well as data sources, in some cases. 

Here's why: I wanted to allow for the possibility of multiple reports without necessarily expecting an instance of SQL Server to be available, so I hit on the idea of delivering a sample database implementation in SQL Server Compact Edition along with the RDLDocumenter files. (Of course you can use a configuration switch to connect to other SQL Server editions if you wish.)

However, CE connections use different. NET classes and I couldn't be sure that these classes would be available. (As a developer's tool and teaching device, RDLDocumenter doesn't install anything on your box; it gives you the information and options you need but can't assume anything beyond the standard .NET framework.)  As a result, it has to do some persnickety error testing, trapping, and user feedback.

That's the problem to solve -- parts of which you may not need in your scenario -- from end-to-end.

How does it work?

Here's your walkthrough of how RDLDocumenter handles these various tasks.

1. Defining the form elements in the layout

The form has two associated datasets, representing the different schemas required by the Report1 and ReportMany RDLCs.  They're actually pretty similar but DataSet1 lacks DataSetMany's columns representing each RDL's report filename and datetime stamp of its upload into the database. The form also has two binding sources, one for each dataset, although I suspect this is just for design convenience and not required.

There is also a SQLConnection member object visually defined (dragged and dropped onto the form layout with the other components), with some default properties.  This does no harm if it's not ever used. 

The form does not have a SQLCEConnection explicitly defined, since if the required libraries were not available this would bomb at form startup.  Instead, I just defined a member of the VB as an object, like this:

Private SqlCeConnection1 As Object = Nothing
   ' see notes in InitializeCEDataConnection

An additional member of the form provides a way to store the user's chosen override for location of the two reports on disk, should they edit the external copies provided and prefer their versions to the two embedded report resources:

Private reportLocation As String = ""

OK so far?

2. Initializing the ReportViewer

At the form Shown event -- note that the Load event appears to be too early -- I store the EXE location for later use and override whatever information I've used for the ReportViewer control at design time, by issuing a call to a method I'll use for similar changes throughout the life of the form.  In this first call I set the viewer up for the most innocuous default use (the embedded Report1.rdlc resource):

Private Sub Form1_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
   Me.InitializeDefaultXSLT()
   ' the XSLT in use, like report resources, is editable by the user
   Me.exeLocation = System.Reflection.Assembly.GetExecutingAssembly().Location
   Me.InitializeViewer("TestHarness.Report1.rdlc")
End Sub

Notice the use of the executable name in the reference to the embedded resource above.  This was the source of confusion in the question I answered this morning, FWIW; if a resource is embedded you have to tell the ReportViewer where it is (in this case, inside an EXE named TestHarness).  This requirement has absolutely nothing to do with reports in particular; the call you see above to InitializeDefaultXSLT() has to do exactly the same thing when choosing to load the default XSLT embedded resource rather than an external transform selected by the user from XSLT files on disk.

3. Ready to dynamically switch between RDLCs

Here's how the viewer is initialized, or, later, re-initialized to use a different report. There are several significant activities demonstrated here:

  1. First, the ReportViewer's Reset method is called and a new datasource object is defined on the fly. At this time, no data is actually loaded, we're just distinguishing between the reports and their potential datasets.  We can load different datasets later without necessarily changing the schema or the report. 
  2. The ReportViewer.LocalReport.ReportEmbeddedResource is filled as requested in the method call, to indicate one of the two embedded report resources. Again, in the test form's scenario, the two reports serve to indicate which of the two schemas and types of data sources are requested. As noted in the code comments (below), the value of this property will be overridden if ReportPath is also supplied. We'll be filling ReportPath if the user supplied a ReportLocation value by browsing to a directory using the fifth button in the dialog.
  3. We determine which schema, one report or many, was requested, and bind our new datasource object appropriately.
  4. We add our new datasource to the ReportViewer.LocalReport.DataSources collection.

Private Sub InitializeViewer(ByVal tsReport As String)

   Dim ReportDataSourceX = _
      New Microsoft.Reporting.WinForms.ReportDataSource() 
   Me.ReportViewer1.Reset() 
   Me.ReportViewer1.LocalReport.ReportEmbeddedResource = tsReport 
   Me.ReportViewer1.LocalReport.EnableExternalImages = True 
   Me.ReportViewer1.LocalReport.EnableHyperlinks = True 
   If Me.reportLocation.Length = 0 Then 
      Me.ReportViewer1.LocalReport.ReportPath = "" 
   Else 
      ' this will take precedence if used 
      Me.ReportViewer1.LocalReport.ReportPath = _ 
         Me.reportLocation & System.IO.Path.DirectorySeparatorChar & _
         tsReport.Split(".")(1) & ".RDLC" 
   End If 
   If tsReport.Contains("ReportMany") Then 
      ReportDataSourceX.Name = "DataSetMany_RDLDocPrimary" 
      ReportDataSourceX.Value = Me.rdlDocPrimaryBindingSourceDatabase 
   Else 
      ReportDataSourceX.Name = "DataSet1_RDLDocPrimary" 
      ReportDataSourceX.Value = Me.rdlDocPrimaryBindingSource 
   End If 
   Me.ReportViewer1.LocalReport.DataSources.Add(ReportDataSourceX)
End Sub

4. Dynamic data load, parameter handling, and report display

Once the appropriate report and datasource is available, the test interface can display a report each time the user clicks the second or third button in the dialog.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles Button2.Click
   Me.ReportFromDocRDLXML()
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
   Handles Button3.Click
   If My.Settings.DatabaseIsCompact Then
      Me.CheckSQLCeInitialization()
      ' doing the above as late as possible
      If Not IsNothing(Me.SqlCeConnection1) Then
         Me.ReportFromDocRDLData()
      End If
   Else
      Me.ReportFromDocRDLData()
   End If
End Sub

The only difference between two subs referenced in the button clicks above is that loading from DocRDLXML asks the user to pick the DOCRDL XML file containing the data to load, so I won't include them here.  They both call the same DisplayReport method, which does the work of deciding which type of data was meant and what to do about it. 

You can see that the viewer is re-initialized, if necessary, in the process.  (The necessity to reinitialize is determined by whether the report name has changed since the last call.) You can also see a simple example of parameterizing the report run programmatically for a local report in this method.

Private Sub DisplayReport(ByVal tsSource As String,  _
                                      Optional ByVal tsSourceIsServerInfo As Boolean = False)
   Dim p(0) As Microsoft.Reporting.WinForms.ReportParameter
   Try
      Array.Resize(p, 1)
      If tsSourceIsServerInfo Then
         Me.GetDataSetFromServer()
         If Me.ReportViewer1.LocalReport.ReportEmbeddedResource.Contains( _
            "Report1") OrElse _
            Me.ReportViewer1.LocalReport.ReportPath.Contains("Report1") Then
            Me.InitializeViewer("TestHarness.ReportMany.rdlc")
         End If
      Else
         Me.DataSet1.Reset()
         Me.DataSet1.ReadXml(tsSource)
         If Me.ReportViewer1.LocalReport.ReportEmbeddedResource.Contains( _
            "ReportMany") OrElse _
            Me.ReportViewer1.LocalReport.ReportPath.Contains("ReportMany") Then
            Me.InitializeViewer("TestHarness.Report1.rdlc")
         End If
      End If
      p.SetValue( _
         New Microsoft.Reporting.WinForms.ReportParameter("RDLDocInfo", tsSource), 0)
      Me.ReportViewer1.LocalReport.SetParameters(p)
      Me.ReportViewer1.RefreshReport()
   Catch Ex As Exception
      ' I'll omit my error message feedback stuff here 
   Finally
      p = Nothing
   End Try
End Sub

5. Switching between types of data connections at will 

You can see the DocRDL XML being read directly into DataSource1 in the DisplayReport method; this is possible because DocRDL XML is.. dataset shaped.  So that makes this pretty easy. 

The GetDataSetFromServer method you see referenced when the test form is loading data for ReportMany is just about what you'd expect for loading any dataset, except that it defers to a separate method, GetDataSetFromCEServer if the dialog's config settings says that it should.

if the SQLCeConnection property has not previously been initialized during the life of the form, the separate CE-handling method must check for availability of the appropriate classes, instantiating the CEConnection object if it can:

Private Sub CheckSQLCeInitialization()
   Try
      If My.Settings.DatabaseIsCompact AndAlso _
         Me.SqlCeConnection1 Is Nothing AndAlso _
         Me.Button3.Enabled Then
         Me.InitializeCEDataConnection()
      End If
   Catch
      Me.DisableDatabaseReporting()
   End Try
End Sub

Notice the test for Me.Button3.Enabled above.  We're doing a fairly expensive check here, so the DisableDatabaseReporting method you see in the Catch above disables the button for the life of the form, along with providing other user feedback. This ensures we never do it more than once. 

And, finally, for your reference, here's the code that does CE class initialization:

Private Sub InitializeCEDataConnection()
   If My.Settings.DatabaseIsCompact AndAlso _
      IsNothing(
Me.SqlCeConnection1) Then
      ' this part is taken care of by Designer-driven code for the other connection
      ' SqlCeConnection1 -- we are handling this differently from a normal
      ' SqlConnection in case CE support files not installed.
      Dim x As System.Reflection.Assembly = _
            System.Reflection.Assembly.GetEntryAssembly(), _
            y
As System.Reflection.Assembly = Nothing
      Try
         For Each objDepAssembly As System.Reflection.AssemblyName In _
            x.GetReferencedAssemblies()
            If objDepAssembly.ToString().ToUpper().Contains("SQLSERVERCE") Then
               Try
                  y = System.Reflection.Assembly.Load(objDepAssembly.FullName)
               Catch
                  y = Nothing
               End Try
            End If
         Next
         If Not IsNothing(y) Then
            Dim ox As New System.Data.SqlServerCe.SqlCeConnection()
            ox.ConnectionString = _
               
Global.TestHarness.My.MySettings.Default.RDLDocCompactDatabase
            Me.SqlCeConnection1 = ox
            ox =
Nothing
         Else
           Me
.SqlCeConnection1 = Nothing
         End If
      Catch Ex As Exception
         Me.SqlCeConnection1 = Nothing
      Finally
         x = Nothing
         y = Nothing
         If IsNothing(Me.SqlCeConnection1) Then
            Me.DisableDatabaseReporting()
         End If
      End Try
   End If
End Sub

And there you have it

That's pretty much all the generalizable code in TestHarness.EXE, covering a large number of the most frequently asked questions about dynamic report loading, as I said up-top.

Enjoy.

At some point I'll post some of what I learned by writing RDLDocumenterDesigner, and by choosing to decorate RDL schemas with custom data -- a much longer journey, of which TestHarness.EXE represented a final step.

Tags:

ASP.NET | Reporting | SQL Server | XML/XSLT | xmlRSDocs