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()