Selection Based Crystal Report using Stored Procedure

Here you will see how to do a selection based CrystalReport using a Stored Procedure.

Step 1: Open a new project of type Windows; name it SelectionReport.



Step 2: put a Button and ComboBox on the form & write code for the form's Load event.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace SelectionReport
{
    public partial class Form1 : Form
    {
        SqlConnection con;
        SqlDataAdapter da;
        DataSet ds;
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            con = new SqlConnection ("<Path of Connectionstring>");
            da = new SqlDataAdapter ("Select StudentID From Student", con);
            ds = new DataSet();
            da.Fill(ds, "Student");
            comboBox1.DataSource = ds.Tables[0];
            comboBox1.DisplayMember = " StudentID";
        }

     private void button1_Click(object sender, EventArgs e)
        {
           ReportSelection Report = new ReportSelection();
            Report.Studno = int.Parse(ComBox1.Text);
            Report.ShowDialog();
        }

Step 3:

Add a Crystal Report to the project; choose blank report.

Configure a report with DB; open the field Explorer; right-click on a Database field, Database Expert; create a new connection using oledb, choose the provider Microsoft Oledb Provider for Sql/Sql Native Client; pass Connection Details or ServerName.

Choose StoredProcedure/Command Node Node under Connection node; open a window where you can define a select statement in LHS text area.

As Shown in figure.

Right-click on parameter field.



Write this Query into the prompt:

select
SI.StudentID,SI.StudentFirstName +' ' + SI.StudentLastName [Student Name] ,SI.StudentPhoneNo [Phone No],SI.StudentEmailId Email,
TI.TechName Course,FI.FeeAmount Fees,FI.FeeId FeeID,sum(isnull (FPI.FeePaidAmount,0)) Paid ,avg(FI.FeeAmount)-sum(isnull (FPI.FeePaidAmount,0)) Balance
FROM StudentInformation SI
INNER JOIN dbo.FeeInformation FI ON SI.StudentID=FI.FeeStudentId
left outer JOIN dbo.FeePaidInformation FPI ON FI.FeeId=FPI.FeeId
INNER JOIN dbo.TechnologyInformation TI ON FI.FeeTechnology=TI.TechId
where SI.StudentID={?StudentID} group by SI.StudentID,SI.StudentFirstName , SI.StudentLastName ,SI.StudentPhoneNo ,SI.StudentEmailId,TI.TechName ,FI.FeeAmount ,FI.FeeId


Click ok, ok, ok which will add Command in Database Field, if we expand it will Display the column we mention under Select statement.

Note: parameter fields are used to send values to report in runtime.

We can also add the parameter using Field Explorer Window, to create a parameter; right-click on the node parameter and select Add which will prompt for name of Parameter; enter name & click ok. Follow the process to cerate 2 parameters for example Company Name, Address and Website; drag & drop onto the report header & make the necessary alignments.

Design the report a as per your requirements as shown in the following figure.



Step 3: (launching the Report) Add new form and place Crystal Report Viewer Control on it set the Windows state property as Maximized.

Add a Reference of System. Configuration assembly also Add "Application Configuration File" i.e. application config & under it write the following:
 
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add name="Student.Properties.Settings.StudentConnectionString"
      connectionString="Data Source=.;Initial Catalog=Student;User ID=sa;Password=p@ssw0rd"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <appSettings >
    <add key="CName" value="XYZ Software Solution"/>
    <add key="Addr" value=" Address-"/>
    <add key="WebSite" value=" Website:"/>
    <add key="Y5Institute" value="Data Source=C1;Initial Catalog=Student;User ID=sa;Password=p@ssw0rd;"/>
  </appSettings>
</configuration>

In this case to launch a report and send the parameters to the report for execution we need to make use of the class
ReportDocument present under CrystalDecisions.CrystalReport.Engine Namespace.

Step 4: Write the following code under form:

using CrystalDecisions.CrystalReports.Engine;
using System.Configuration;
using CrystalDecisions.Shared;

namespace Student
{
    public partial class ProjectReport : Form
    {   
         Internal int Sno;
        public ProjectReport()
        {
            InitializeComponent();
        }

        private void ProjectReport_Load(object sender, EventArgs e)
        {
            ConnectionInfo CInfo = new ConnectionInfo();
            CInfo.ServerName = "Connection";
            CInfo.UserID = "sa";
            CInfo.Password = "p@ssw0rd";

            TableLogOnInfo tableInfo = new TableLogOnInfo();
            tableInfo.ConnectionInfo = CInfo;

            TableLogOnInfos tablelog = new TableLogOnInfos();
            tablelog.Add(tableInfo);
            crystalReportViewer1.ReportSource =            F:\\kiran1\\Institute\\Student\\Student\\StudentFees.rpt";//path of ur report
            crystalReportViewer1.LogOnInfo = tablelog;

            string cname = ConfigurationManager.AppSettings.Get("CName");
            string addr = ConfigurationManager.AppSettings.Get("Addr");          
            ReportDocument obj = new ReportDocument();
            obj.Load("F:\\kiran1\\Institute\\Student\\Student\\ StudentFees.rpt");
            crystalReportViewer1.ReportSource = obj;
            obj.SetParameterValue("CompanyName", cname);
            obj.SetParameterValue("Address", addr);

        }
    }
}

I attached database (tables & Stored Procedure) to execute it.