@bobnoordam

Creating a crystal report from an in-memory data table

Posted on | By bobnoordam

To use data from an in memory created table object over a direct SQL query has the advantage that you can manipulate the data before it is send to the report, e.a. – you can calculate fields, or remove fields from the dataset before it is passed to the report.

  • Design the report as normal. You can design the report using a direct link to the sql server, or the data set present in visual studio
  • Create a form with an empty reportviewer on it, do not bind the rpeort viewer to a data source.
  • Call the form with the code sample below. The report will now use the offered data in the table object and not the datalink you used at design time.
' For this sample, two tables are loaded from an sql server, a relation is made, and the dynamic object
' is passed to the crystalreport at runtime
' this sample generates a bill, from a tabel with header info, and a table with line info. Only the bill number
' as referenced in n_querynumber is generated in the report.
  
  
' Imports CrystalDecisions.CrystalReports.Engine
' Imports CrystalDecisions.Shared
  
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim dataset As New DataSet
Dim s_sqlcmd1 As String = "SELECT * FROM bill_headers WHERE billnr=100
Dim s_sqlcmd2 As String = "SELECT * FROM bill_lines WHERE parent_billnr=100 ORDER BY id ASC"
connection = New SqlConnection(My.Settings.MyConnectionString)
connection.Open()
  
' load the table with headers into the dataset
command = New SqlCommand(s_sqlcmd1, connection)
adapter.SelectCommand = command
adapter.Fill(dataset, "bill_headers") ' tablename *MUST* match with the tablename used in the report
  
' load the 2nd table into the dataset (lines)
adapter.SelectCommand.CommandText = s_sqlcmd2
adapter.Fill(dataset, "bill_lines")
  
' define the relation between the tables
dataset.Relations.Add("relation", dataset.Tables("bill_headers").Columns("billnr"), dataset.Tables("bill_lines").Columns("parent_billnr"))
  
' dataset is ready, get rid of the objects used to construct it
adapter.Dispose()
command.Dispose()
connection.Close()
  
' Load the defined report with the dataset we just created
Dim myreport As ReportDocument
myreport = New ReportDocument
myreport.Load(Application.StartupPath  & "\CrystalReport_finance_Bill.rpt")
myreport.SetDataSource(dataset)
  
' bind viewer
CrystalReportViewer1.DisplayGroupTree = False
CrystalReportViewer1.ReportSource = myreport
CrystalReportViewer1.Refresh()
Category: Crystal reports