Dynamically render a DataTable into a Winform using the SSRS ReportViewer Control

Question

BACKGROUND

• I'm writing a method that will (eventually) take as input a System.Data.DataTable and render it as an (simple,tabular) SSRS report into a Winform using the Microsoft's ReportViewer Control (http://www.gotreportviewer.com/)
• In order to do this I need to (1) dynamically create an RDL file based on the DataTable (2) load the RDL into the ReportViewerControl (3) bind the ReportViewerControl to that DataTable

QUESTIONS

• Ideally I would simply love a link to a sample that did all of the above - I have searched but have been unable to find one.
• Otherwise, I need some help specifically with #1 and #3 above.
• For #1 - Is there a simple way of generating an RDL file dynamically at runtime? (I have already starting writing code to emit the correct XML, but re-using something will same me some time)
• For #3 - It's unclear to me how to bind the ReportViewerControl to a DataTable I have locally. Most of the examples I found assume I that ReportViewer control will fetch data that is on a remote SQL server (which is to be expected) instead of getting it from a local DataTable.

CONTEXT

• I'm only recently starting working with the ReportViewer control - I have found samples googling - but none seem to cover the full scenario
• I do not know the schema of the DataTable ahead of time. The DataTable's schema will not even be constant during calls to my method which will render it.
• I cannot use a different reporting control - I must use the ReportViewer control. If you do know of other reporting controls that make this task easy, please do let me know. Even if it doesn't solve my current problem, it's useful for later.
• The person viewing this report is an end-user and does not have any rights to publish RDL to a SSRS server
• The DataTable will already be sorted, filtered, etc. The types will all be simply values of strings, ints, doubles, and dates. The DataTable will be of reasonable size - 1-30 columns and have from 100 to 5000 rows. The DataTable is also being constructed locally (sometimes manually constructed via code) and is not retrieving data from some remote datasource.
• The data will always be rendered as a simple table (no charts, etc.). Later on I may need to add grouping
• I cannot switch to using HTML, XAML, etc to display the report - there are features in ReportViewer that I will eventually make use of that that HTML, XAML, etc do not have.

UPDATE ON 2010/01/15

Starting from Jon's answer below I was able to achieve what I needed. As he mentions the difficut part is learning the RDL XML schema and knowing what RDL elements to write to achieve the desired kind of report.

Answer 1

I did exactly this over a 4 month period. My code is in VB.NET and is quite lengthy. I started with the code listed at GotReportViewer, and built on top of it. In a nutshell, this is what you will need to be doing:

• Render and RDLC file in memory - using a DataTable (or dataset, for multitable reports) as input

For this, I created a class called ReportEngine. It is basically just a bunch of functions that create RDLC files. This is the guts of the whole operation, and the code is quite long. Here are some of the Main Functions I am using. It would be best just to email you my Classes - as they are very long:

  'Data Building variables
  Private _reportDataset As DataSet             'Data displayed in report
  Private _AllFields As List(Of String)         'All column field names
  Private _AllCaptions As List(Of String)       'All column names to display in report (needed for french translation)
  Private _reportRDL As MemoryStream            'Report definition file
  Private _reportControl As ReportControl       'Control that displays the report

    Public Sub LoadReport(ByVal reportDataset As DataSet)
    Try
      _reportDataset = reportDataset

      'check if the datatable contains data
      _hasNoData = False
      If _reportDataset.Tables(0).Rows.Count = 0 Then
        _hasNoData = True
      End If

      'Get table column fieldnames, captions and widths
      _AllFields = GetTableFields(0)
      _AllCaptions = GetTableCaptions(0)

      'reset RDL file if already existing
      If Not (_reportRDL Is Nothing) Then
        _reportRDL.Dispose()
      End If

      GenerateRdl()                   'Create the RDLC file
      ShowReport()                    'Load it into the ReportViewer Control
      RaiseEvent ReportLoaded(Me)     'Indicate via event that report is loaded and ready to be displayed

    Catch ex As Exception
         'Handle error
    End Try
  End Sub



     'returns a list of fields from a datatable used for the report
  Public Function GetTableFields(ByVal tableIndex As Integer) As List(Of String)
    Dim dataTable As DataTable = _reportDataset.Tables(tableIndex)
    Dim availableFields As New List(Of String)
    Dim i As Integer
    For i = 0 To dataTable.Columns.Count - 1
      availableFields.Add(dataTable.Columns(i).ColumnName)
    Next i
    Return availableFields
  End Function

  'returns a list of captions from a datatable
  Public Function GetTableCaptions(ByVal tableIndex As Integer) As List(Of String)
    Dim dataTable As DataTable = _reportDataset.Tables(tableIndex)
    Dim captions As New List(Of String)
    Dim i As Integer
    For i = 0 To dataTable.Columns.Count - 1
      captions.Add(dataTable.Columns(i).Caption)
    Next i
    Return captions
  End Function

• Load the RDLC file into the reportViewer from memory
• Add the datasource to the ReportViewer control, using the same name specified in the RDLC file. If the names don't match up, you will get errors.

[code start here - code block messed up and can't fix it.]

Public Sub DisplayReport(ByVal ms As MemoryStream, ByVal ds As DataSet)

Dim RowCount As Integer = 0

ReportViewer1.Reset()
ReportViewer1.LocalReport.DataSources.Clear()
ReportViewer1.LocalReport.LoadReportDefinition(ms)

For I As Integer = 0 To Me.ReportEngine.ReportDataSet.Tables.Count - 1
  'Bind dataTables to the report viewer control - matches the datasources contained in the RDLC files
  ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource("MyData" + I.ToString, ds.Tables(I)))

  'Calc total rows returned
  RowCount += ds.Tables(I).Rows.Count
Next

SetupReport()
ReportViewer1.RefreshReport()

End Sub

Anyways, if you have more questions, I could go on for days on this. There is a lot to do to get this running.

Answer 2

Hi Jon,

Would it be possible for me to also get the full code above?