Thursday 1 December 2011

C# Crystal Reports - Date to Date

The following program describes how to generate a Date to Date report from Crystal Reports in C#
All C# Crystal Reports Tutorial in this website is based on the following database - crystaldb. So before you begin this section , please take a look at the database structure of crystaldb - Click Here C# crystaldb
If you are new to Crystal Reports and do not know how to create Crystal Reports from C# , please take a look at the section step by step tutorial for creating a Crystal Reports from C#.
In the previous sections explains How to pass C# Crystal Reports Date Paramater and also explain C# Dynamic logon parameters in Crystal Reports. So before we start this section take a look at the previous sections because we are here using sections from these tutorial.
Hope you understand the previous two sections well (1 - C# Crystal Reports Date Paramater, 2 - C# Dynamic logon parameters in Crystal Reports). Here we are creating two date parameter and pass it to Crystal Report at run time also the Crystal Report using dynamic logon parameter to access database.
Createting two date parameters (dateFrom and dateTo).
Date From parameter.
csharp-crystal-reports-datefrom
Date To Parameter
csharp-crystal-reports-dateto
Here we have three tables selected for report (ordermaster , orderdetails and product ) and we are making the formula like , select all records from the tables whose order date is between fromDate parameter and toDate paramater . For doing this you have to select from date as Ordermaster.orderdate, to date as Ordermaster.orderdate , comparison operators , boolean operator (AND) and date parameters fromdate and todate fields from selection list of Formula Editor and make the formula.
The following picture shows how to select the fields from formula editor and make the formula. Double click each field then it will automatically selected .
csharp-crystal-reports-datetodate-formula
After the creation of selection formula you can close that screen .
Now the designing part is over and the next step is to call the Crystal Reports in C# and view it in Crystal Reports Viewer control .
Select the default form (Form1.cs) you created in CSharp and drag two Textboxs (from date entry and to date entry) , a button and CrystalReportViewer control to your form.



using System;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ReportDocument cryRpt = new ReportDocument();
            cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt");

            TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
            TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
            ConnectionInfo crConnectionInfo = new ConnectionInfo();
            Tables CrTables;

            ParameterFieldDefinitions crParameterFieldDefinitions ;
            ParameterFieldDefinition crParameterFieldDefinition ;
            ParameterValues crParameterValues = new ParameterValues();
            ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue();

            crParameterDiscreteValue.Value = textBox1.Text;
            crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields;
            crParameterFieldDefinition = crParameterFieldDefinitions["fromDate"];
            crParameterValues = crParameterFieldDefinition.CurrentValues;

            crParameterValues.Clear();
            crParameterValues.Add(crParameterDiscreteValue);
            crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);

            crParameterDiscreteValue.Value = textBox2.Text;
            crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields;
            crParameterFieldDefinition = crParameterFieldDefinitions["toDate"];
            crParameterValues = crParameterFieldDefinition.CurrentValues;

            crParameterValues.Add(crParameterDiscreteValue);
            crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);

            crConnectionInfo.ServerName = "YOUR SERVERNAME";
            crConnectionInfo.DatabaseName = "DATABASE NAME";
            crConnectionInfo.UserID = "USERID";
            crConnectionInfo.Password = "PASSWORD";

            CrTables = cryRpt.Database.Tables;
            foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
            {
                crtableLogoninfo = CrTable.LogOnInfo;
                crtableLogoninfo.ConnectionInfo = crConnectionInfo;
                CrTable.ApplyLogOnInfo(crtableLogoninfo);
            }

            crystalReportViewer1.ReportSource = cryRpt;
            crystalReportViewer1.Refresh(); 
        }
   }
}

cryRpt.Load(PUT CRYSTAL REPORT PATH HERE\\CrystalReport1.rpt");
The Crystal Reports file path in your project files location, there you can see CrystalReport1.rpt . So give the full path name of Crystal Reports file like c:\projects\crystalreports\CrystalReport1.rpt
crConnectionInfo.ServerName = "YOUR SERVERNAME";


  crConnectionInfo.DatabaseName = "DATABASE NAME";


  crConnectionInfo.UserID = "USERID";


  crConnectionInfo.Password = "PASSWORD";

You have to pass the necessary database connection information.

Now you can run the program . Enter from date and to date , then you can see the report whose order date is between from date and to date you passed to Crystal reports.

csharp-crystal-reports-date-to-date

No comments :