Tutorial :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.


Solution: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.


Solution:2

    public static DataTable GetDataTabletFromCSVFile(string filePath, bool isHeadings)      {          DataTable MethodResult = null;          try          {              using (TextFieldParser TextFieldParser = new TextFieldParser(filePath))              {                  if (isHeadings)                  {                      MethodResult = GetDataTableFromTextFieldParser(TextFieldParser);                    }                  else                  {                      MethodResult = GetDataTableFromTextFieldParserNoHeadings(TextFieldParser);                    }                }            }          catch (Exception ex)          {              ex.HandleException();          }          return MethodResult;      }        public static DataTable GetDataTableFromCsvString(string csvBody, bool isHeadings)      {          DataTable MethodResult = null;          try          {              MemoryStream MemoryStream = new MemoryStream();                  StreamWriter StreamWriter = new StreamWriter(MemoryStream);                StreamWriter.Write(csvBody);                StreamWriter.Flush();                  MemoryStream.Position = 0;                  using (TextFieldParser TextFieldParser = new TextFieldParser(MemoryStream))              {                  if (isHeadings)                  {                      MethodResult = GetDataTableFromTextFieldParser(TextFieldParser);                    }                  else                  {                      MethodResult = GetDataTableFromTextFieldParserNoHeadings(TextFieldParser);                    }                }            }          catch (Exception ex)          {              ex.HandleException();          }          return MethodResult;      }        public static DataTable GetDataTableFromRemoteCsv(string url, bool isHeadings)      {          DataTable MethodResult = null;          try          {              HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(url);              HttpWebResponse httpWebResponse = (HttpWebResponse)httpWebRequest.GetResponse();                StreamReader StreamReader = new StreamReader(httpWebResponse.GetResponseStream());                using (TextFieldParser TextFieldParser = new TextFieldParser(StreamReader))              {                  if (isHeadings)                  {                      MethodResult = GetDataTableFromTextFieldParser(TextFieldParser);                    }                  else                  {                      MethodResult = GetDataTableFromTextFieldParserNoHeadings(TextFieldParser);                    }                }            }          catch (Exception ex)          {              ex.HandleException();          }          return MethodResult;      }          private static DataTable GetDataTableFromTextFieldParser(TextFieldParser textFieldParser)      {          DataTable MethodResult = null;          try          {              textFieldParser.SetDelimiters(new string[] { "," });                textFieldParser.HasFieldsEnclosedInQuotes = true;                  string[] ColumnFields = textFieldParser.ReadFields();                DataTable dt = new DataTable();                foreach (string ColumnField in ColumnFields)              {                  DataColumn DataColumn = new DataColumn(ColumnField);                    DataColumn.AllowDBNull = true;                    dt.Columns.Add(DataColumn);                }                  while (!textFieldParser.EndOfData)              {                  string[] Fields = textFieldParser.ReadFields();                      for (int i = 0; i < Fields.Length; i++)                  {                      if (Fields[i] == "")                      {                          Fields[i] = null;                        }                    }                    dt.Rows.Add(Fields);                }                MethodResult = dt;            }          catch (Exception ex)          {              ex.HandleException();          }          return MethodResult;      }        private static DataTable GetDataTableFromTextFieldParserNoHeadings(TextFieldParser textFieldParser)      {          DataTable MethodResult = null;          try          {              textFieldParser.SetDelimiters(new string[] { "," });                textFieldParser.HasFieldsEnclosedInQuotes = true;                bool FirstPass = true;                DataTable dt = new DataTable();                while (!textFieldParser.EndOfData)              {                  string[] Fields = textFieldParser.ReadFields();                    if(FirstPass)                  {                      for (int i = 0; i < Fields.Length; i++)                      {                          DataColumn DataColumn = new DataColumn("Column " + i);                            DataColumn.AllowDBNull = true;                            dt.Columns.Add(DataColumn);                        }                        FirstPass = false;                    }                    for (int i = 0; i < Fields.Length; i++)                  {                      if (Fields[i] == "")                      {                          Fields[i] = null;                        }                    }                    dt.Rows.Add(Fields);                }                MethodResult = dt;            }          catch (Exception ex)          {              ex.HandleException();          }          return MethodResult;      }  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »