How to Work with Crystal Reports 

Crystal Report is a Reporting application that can be used to produce reports from data sources like databases, XML files, datasets, etc. Here we’ll learn installing crystal reports and then use the application to generate reports in a project. We need to follow a number of steps to complete this process. Let’s get started.

STEP 1: Download crystal reports for the visual studio from the adjoining site, https://www.sap.com/india/products/crystal-visual-studio.html.  An important point to note is that you need to first register with your name and email id before you start with downloading crystal reports.

STEP 2: After installing crystal reports, you install the software. After a successful installation, we need to open a new windows form project in visual studio.

STEP 3: Create tables and stored procedures in the database. I created a table named ‘PatientReg’ and ‘PatientReport’ in the database. The former stores the personal details of a patient and the latter includes the details test status of a patient.

PATIENT DETAILS

CREATE TABLE [dbo].[PatientReg]

(

    [PatientId]  INT          NOT NULL,

    [FirstName]  VARCHAR (10) NULL,

    [LastName]   VARCHAR (10) NULL,

    [Age]        INT          NULL,

    [Gender]     VARCHAR (10) NULL,

    [BloodGroup] VARCHAR (50) NULL,

    [Date]       DATETIME     NULL,

    [Address]    VARCHAR (50) NULL,

    [City]       VARCHAR (10) NULL,

    [PhoneNo]    BIGINT       NULL,

    [ReferredBy] VARCHAR (90) NULL,

    PRIMARY KEY CLUSTERED ([PatientId] ASC)

);

REPORT DETAILS

CREATE TABLE [dbo].[PatientReport]

(

    [PatientId]    INT          NOT NULL,

    [ReportStatus] VARCHAR (20) NULL,

    [TestSpecimen] VARCHAR (20) NULL,

    PRIMARY KEY CLUSTERED ([PatientId] ASC)

);

Now I created the stored procedures to fetch the records

GET PATIENT DETAILS

CREATE PROCEDURE [dbo].PatientRegproc

     @PatientId int

    AS

     SELECT * from PatientReg where PatientId=@PatientId

RETURN 0

GET PATIENT TEST STATUS

CREATE PROCEDURE [dbo].PatientReportproc

     @PatientIdd int

AS

     SELECT * from PatientReport where PatientId=@PatientIdd

RETURN 0

STEP 4: Create a new class named ‘sqlhelper’ for performing database operations.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;




namespace LabManagementSystem

{

    class sqlhelper

    {

        public string _connectionstrings

        {

            get

            {

                return ConfigurationManager.ConnectionStrings["mds"].ConnectionString;

            }

        }

        public sqlhelper()

        {

            con = new SqlConnection(_connectionstrings);

            con.ConnectionString = _connectionstrings;

            cmd = new SqlCommand();

            cmd.Connection = con;




        }

        public DataSet Patient(int idd)

        {

            SqlCommand com = new SqlCommand("PatientRegproc", con);

                com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@PatientId", idd);

            SqlDataAdapter adp = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            adp.Fill(ds);

            return ds;







        }

        public DataSet PatientReport(int id)

        {

            SqlCommand com = new SqlCommand("PatientReportproc", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@PatientIdd", id);

            SqlDataAdapter adp = new SqlDataAdapter(com);

            DataSet dp = new DataSet();

            adp.Fill(dp);

            return dp;

         }

   }

}

STEP 5: Add a class for Patient details. I named it class as ‘Patient.cs’. This class is used to bind the details of the patient. The code within this class is as follows

using System;

using System.Collections.Generic;

using System.Data.SqlTypes;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace LabManagementSystem

{

    class Patient

    {

        public int PatientId { get; set; }

        public string FirstName{ get; set; }

        public string LastName { get; set; }

        public int Age { get; set; }

        public string Gender { get; set; }

        public string BloodGroup { get; set; }

        public string Date { get; set; }

        public string Address { get; set; }

        public string City { get; set; }

        public long PhoneNo { get; set; }

        public string ReferredBy { get; set; }

        public string ReportStatus { get; set; }

        public string TestSpecimen { get; set; }

     }

}

STEP 6: Add the crystal report into the project. It can be added by right-clicking the project name and clicking the ‘add new item’ option.

Select a blank report as soon as a report opens up.

Now customize the format of the report in a way you want to print it.

STEP 7: Add windows form within the project so that we can display the report in this form.

On this form add a crystal report viewer from the toolbox. Drag and drop this control onto the form.

STEP 8: Add a dataset within the project. It can be added by right-clicking the project name and then selecting Add >> new item >> Dataset

Add the tables to the dataset you wish to use within the report.

STEP 9: Add a new form and format it according to your project. Drag and drop controls onto this form according to the functionality of your project. I added various buttons, labels, and text boxes and customized the form as shown below.

I wanted the process to flow when the user adds the patient id into the textbox and then clicks the ‘print’ button. This leads to the generation of the report. The coding for the ‘print’ button.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace LabManagementSystem

{

    public partial class Diagnostic : Form

    {

        public Diagnostic()

        {

            InitializeComponent();

        }

     private void pictureBox2_Click(object sender, EventArgs e)

        {

            this.Hide();

            Form2 f = new Form2();

            f.Show();

        }

        sqlhelper sql = new sqlhelper();

     private void button1_Click_1(object sender, EventArgs e)

        {

            string query = $"select * from PatientReg where PatientId= {textid.Text}";

            DataTable dt = sql.GetRecords(query);

            dataGridView1.DataSource = dt;

        }
     private void btnsave_Click(object sender, EventArgs e)

        {

            string query = $"Insert into PatientReport 
            values({textid.Text},'{txtstatus.Text}','{textspecimen.Text}')";

            if(sql.GetUpdate(query)>0)

            {

                MessageBox.Show("Report Compiled");

            }

            else

            {

                MessageBox.Show("Error occured");

            }

            textid.Clear();

            txtstatus.Text = "";

            textspecimen.Text = "";

            textid.Focus();

        }

    private void btnprint_Click(object sender, EventArgs e)

        {

            Report r = new Report();

            r.Show();

            CrystalReport1 cr = new CrystalReport1();

            List<Patient> _list = new List<Patient>();

           

            DataSet ds2 = sql.Patient(Convert.ToInt32(textid.Text));

            foreach (DataRow dr in ds2.Tables[0].Rows)

            {




                cr.SetDataSource(_list);

                cr.SetParameterValue("PatientId", dr["PatientId"].ToString());

                cr.SetParameterValue("FirstName", dr["FirstName"].ToString());




                cr.SetParameterValue("LastName", dr["LastName"].ToString());

                cr.SetParameterValue("Age", dr["Age"].ToString());

                cr.SetParameterValue("Gender", dr["Gender"].ToString());

                cr.SetParameterValue("BloodGroup", dr["BloodGroup"].ToString());

                cr.SetParameterValue("Date", dr["Date"].ToString());

                cr.SetParameterValue("City", dr["City"].ToString());

                cr.SetParameterValue("PhoneNo", dr["PhoneNo"].ToString());

                cr.SetParameterValue("ReferredBy", dr["ReferredBy"].ToString());

              }

           

            DataSet ds = sql.PatientReport(Convert.ToInt32(textid.Text));

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

               

                cr.SetParameterValue("ReportStatus", dr["ReportStatus"].ToString());

                cr.SetParameterValue("TestSpecimen", dr["TestSpecimen"].ToString());
 }

             r.crystalReportViewer1.ReportSource = cr;

          }

       }

   }






           

OUTPUT

The user enters the patient id and then presses the ‘Go’ button. This shows the details of the patient in a datagridview.

The user then selects the report status and test specimen. And clicks the ‘print’ button. Finally, the report is printed.