Querying Multiple Tables for Crystal Reports
This article addresses a common question encountered when generating reports using ODBC and Crystal Reports in Visual Basic. The question seeks assistance in incorporating a SQL query that joins multiple tables into the report.
Placing the Query
To incorporate the provided query into your Crystal Report, navigate to the Report Wizard and locate the ODBC connection you established with a username and password. Within the wizard, you will find a field labeled "Command Text." This is where you can enter your SQL query.
SELECT ts.`SCHEDIDNO`, ts.`DAYNAME`, DATE_FORMAT(ts.`TIMESTART`, '%h:%i %p') as TIMESTART, DATE_FORMAT(ts.`TIMEEND`, '%h:%i %p') as TIMEEND, ts.`GRADELEVEL`, ts.`SECTIONNAME`, ts.`SUBJECTNAME`, ts.`FACFULLNAME`, ts.`ROOMNAME`, tf.`FACFULLNAME` as PERSONNEL, tf.`DEPARTMENT`, tse.`Adviser` FROM `tblschedule` ts, `tblfaculty` tf, `tblsection` tse WHERE ts.`GRADELEVEL` = " & lblgrade.Text & " AND ts.`SECTIONNAME` = '" & lblsect.Text & "' AND ts.`DEPARTMENTNAME` = tf.`DEPARTMENT` AND tf.`ADMINISTRATOR` = 1 AND tse.`SECTIONNAME` = '" & lblsect.Text & "'
Sample Code for Reference
Below is example code to guide you in incorporating your query into the report:
VB.NET
Private CRPT As New ReportDocument Private APPPATH As String Private PARAM As New ParameterFields Public PARAM_DESC As New ParameterDiscreteValue Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load APPPATH = Application.StartupPath & "\REPORT\sample.rpt" CRPT.Load(APPPATH) Me.CrystalReportViewer1.ReportSource = CRPT PARAM = CRPT.ParameterFields PARAM_DESC.Value = Format(DatePicker1.Value, "yyyyMMdd") PARAM("DATEP").CurrentValues.Clear() PARAM("DATEP").CurrentValues.Add(PARAM_DESC) Me.CrystalReportViewer1.Refresh() End Sub
C#
CRPT = new ReportDocument(); APPPATH = Environment.CurrentDirectory + "Sample.rpt"; CRPT.Load(APPPATH); Report_Viewer.Refresh(); CRPT.SetParameterValue("syear", Servercls.year); CRPT.SetParameterValue("smonth", Servercls.month); CRPT.SetParameterValue("sday", Servercls.day); CRPT.SetParameterValue("datevalue", Servercls.Datevalue); Report_Viewer.ReportSource = CRPT; sc.configureCrystalReport(); Report_Viewer.Refresh();
Considerations
Ensure that the parameter names in your Crystal Report match those specified in your code base. By implementing these instructions, you can effortlessly retrieve data from multiple tables and generate a comprehensive report using Crystal Reports in your VB application.
The above is the detailed content of How Can I Query Multiple Tables for My Crystal Reports Using VB and ODBC?. For more information, please follow other related articles on the PHP Chinese website!