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

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

Patricia Arquette

Release： 2024-11-25 17:00:11

Original

524 people have browsed it

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` = " &amp; lblgrade.Text &amp; "
    AND ts.`SECTIONNAME` = '" &amp; lblsect.Text &amp; "'
    AND ts.`DEPARTMENTNAME` = tf.`DEPARTMENT`
    AND tf.`ADMINISTRATOR` = 1
    AND tse.`SECTIONNAME` = '" &amp; lblsect.Text &amp; "'

Copy after login

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 &amp; "\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

Copy after login

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

Copy after login

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!