To use data from an in memort 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 calcualte fields, or remove fields from the dataset before it is passed to the report.

- Design the report as normal. You can design the rpeort 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=" & n_querynumber
  Dim s_sqlcmd2 As String = "SELECT * FROM bill_lines WHERE parent_billnr=" & n_querynumber _
  & " 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()


Visitor Comments