Crystal Report Between Two Dates using a Stored Procedure


Step 1: Open a Windows Forms Application & place 2 DateTimePicker controls and a Button control in the form.

As shown in the form below:

CrystalRepo1.gif

Write this code under a Form.

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.Configuration;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Shared;
using CrystalDecisions.Windows.Forms;

namespace Student
{
    public partial class DateWise : Form
    {

        public DateWise()
        {
            InitializeComponent();
        }
        private void DateWise_Load(object sender, EventArgs e)
        {
 
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DateReport datereport = new DateReport();
            datereport.toDate = Convert.ToDateTime(cbTodate.Value);
            datereport.FromDate = Convert.ToDateTime(cbFromDate.Value);
            datereport.ShowDialog(); 
        }      
    }
}

Step 2: Creating a report: go to the Solution Explorer and add a new item in LHS Categories choose Reporting 7 in RHS choose Cryatal Rerport Template & give a name for it then click thge Add Button then open a Galary and choose a Blank Report then open a window and go to Field Explorer, choose Database field then right click and select Database Expert.

Wizard choose Create New Connection, explore it choose OLEDB (ADO) for the Provider and select the provider named Microsoft OLEDB Provider For SQL Server / SQL Native Client (only for SQL Server) then click Next and fill in Connection Information & click Finish.

Then the Report Creation Wizard is opened.

Now in the Report Wizard choose Database; explore it 7 choose the Stored Procedure that your want as shown below.

CrystalRepo2.gif

Step 3: Click ok then go to Field Exploorer & choose Stored Procedure; explore it & drag & drop field in Report Sesion (Details) & design it as per your requirements.

Step 4:

If you want to a parameter to a report then right-click on Parameters Field in field Explorer. Then click on New.

For e.g.

CrystalRepo3.gif

Now Create a parameter called fromdate as the start date.

CrystalRepo4.gif

Similarly create a ToDate parameter.

CompanyName,Address,Website Follow the above procedure

Step 5: Design the Report as shown in the screen below.

cr1.gif

Step 6:

(launching the Report) Add a new form and place a Crystal Report Viewer Control on it; set the Windows state property to Maximized.

Add a reference for System. Configuration assembly also add "Application Configuration File" i.e.app 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;word=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;word=p@ssw0rd;"/>
  </appSettings>
</configuration>

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.Configuration;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace Student
{
    public partial class ReportSelection : Form
    {

        internal DateTime ToDate;
        internal DateTime FromDate;
        public ReportSelection()
        {
            InitializeComponent();
        }

        private void ReportSelection_Load(object sender, EventArgs e)
        {
            ConnectionInfo CInfo = new ConnectionInfo();
            CInfo.ServerName = "Connection"// This Should be the same name What we have Configured under Oledb(ADO)
            CInfo.UserID = "sa";
            CInfo.word = "p@ssw0rd";

            TableLogOnInfo tableInfo = new TableLogOnInfo(); // providing table Details  use the Seperate object of TableLogOnInfo Class for each table we use in the Report
            tableInfo.ConnectionInfo = CInfo;

            TableLogOnInfos tablelog = new TableLogOnInfos();
            tablelog.Add(tableInfo);
            crystalReportViewer1.ReportSource = "F:\\kiran\\Student\\Student\\StudentFees.rpt"; //<"Path of the report">;
            crystalReportViewer1.LogOnInfo = tablelog;

          // Using Above Coding to Skip/Elliminate user Id,aword i.e.Connection Datails

            string cname = ConfigurationManager.AppSettings.Get("CName");
            string addr = ConfigurationManager.AppSettings.Get("Addr");
            string Website = ConfigurationManager.AppSettings.Get("WebSite");          
            ReportDocument obj = new ReportDocument();
            obj.Load("F:\\kiran\\Student\\Student\\StudentFees.rpt");
            crystalReportViewer1.ReportSource = obj;
            obj.SetParameterValue("CompanyName", cname);
            obj.SetParameterValue("Address", addr);
            obj.SetParameterValue("Company Website", Website);
            obj.SetParameterValue("@StattDate",ToDate);
            obj.SetParameterValue("@EndDate",FromDate); 
            
        }
    }
}

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

Note: Please insert data on tables.


Similar Articles