How to Create and Implement Custom Queries for Complex Data Retrieval in Crystal Reports using VB.NET and C#?

Create Queries for Complex Data Retrieval in Reports

Your VB application prints a report using ODBC. Tables are added through the database expert and a Crystal Report is designed. The question arises on where to place a custom query to retrieve specific data for the report.

Locating the Query Field in Crystal Report

Navigate to the Crystal Report Wizard and locate the ODBC connection you created with username and password. A "Command Text" field will be present where you can enter your custom query.

Sample Query

The provided query retrieves data from multiple tables:

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 & "'

VB.NET Code

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# Code

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

Remember to specify the same parameter names in your code as in the Crystal Report design.

The above is the detailed content of How to Create and Implement Custom Queries for Complex Data Retrieval in Crystal Reports using VB.NET and C#?. For more information, please follow other related articles on the PHP Chinese website!