Crystal Reports - Get data between two dates

Crystal Report to display records between fromdate and todate

1) First step is to make new parameters by right clicking on paramaters fields in field explorer.

Then click on New.

2) Now create a parameter called fromdate for start date

3) Similarly create a parameter called todate for end date

4) On form I have taken two datetimepicker controls for selecting from and to date

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.Odbc;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.ReportSource;

using CrystalDecisions.Shared;

using CrystalDecisions.Windows.Forms;

namespace sampletest

{

public partial class frmReturnReceipt : Form

{

DataSet ReportDB;

String str = "";

String FromDate, ToDate;

private void frmReturnReceipt_Load(object sender, EventArgs e)

{

}

public void Set_Report_Dataset()

{

try

{

RR rptRR = new RR();

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 = dateTimePicker1.Value.ToString("yyyy-MM-dd");

crParameterFieldDefinitions = rptRR.DataDefinition.ParameterFields;

crParameterFieldDefinition = crParameterFieldDefinitions["fromdate"];

crParameterValues = crParameterFieldDefinition.CurrentValues;

crParameterValues.Clear();

crParameterValues.Add(crParameterDiscreteValue);

crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);

crParameterDiscreteValue.Value = dateTimePicker2.Value.ToString("yyyy-MM-dd");

crParameterFieldDefinitions = rptRR.DataDefinition.ParameterFields;

crParameterFieldDefinition = crParameterFieldDefinitions["todate"];

crParameterValues = crParameterFieldDefinition.CurrentValues;

crParameterValues.Add(crParameterDiscreteValue);

crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);

crConnectionInfo.ServerName = "servername";

crConnectionInfo.DatabaseName = "database_name";

crConnectionInfo.UserID = "username";

crConnectionInfo.Password = "password";

CrTables = rptRR.Database.Tables;

foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)

{

crtableLogoninfo = CrTable.LogOnInfo;

crtableLogoninfo.ConnectionInfo = crConnectionInfo;

CrTable.ApplyLogOnInfo(crtableLogoninfo);

}

crystalReportViewer1.ReportSource = rptRR;

crystalReportViewer1.Refresh();

}

catch (Exception e1)

{

MessageBox.Show(e1.ToString());

}

}

private void btnpreview_Click(object sender, EventArgs e)

{

Set_Report_Dataset();

}

}

}

Add Group Count in Crystal Report - Using Summary Field

The following C# - Crystal Reports section describes how to add a summary field in the Crystal Reports .

Here in this section we are calculating the count of the records in group.

1. In the Crystal Reports designer view window, right click on the Report Footer , just below the Total field and select Insert -> Summary .
















2.Then you will get a screen , select the tblrrr.Receiptno from the combo box and select Count from next Combo Box , and summary location Group #2:tblrrr.refund(In my case here which is my group name). Click Ok button

3. Now you can see CountofReceiptno1(which is circled) in the group section









Sample output :

Add Serial number in Crystal Report - Not through code

Crystal Report :

Adding a serial number in grouping without any code. I have placed the step by step screenshots which will explain easily how to do it.

1) Add a new field in Running Total Fields in Field Explorer

2) Give a proper name to your Running total name. For example I have choosen here RTotal1. Now categorize based on which field in database you want to summarize it. Next is as we want serial number select count. If you want your serial number to start again from the start when the value in the group changes then select on change of group and specify the grouping name else keep the default value.

3) Smaple Preview of the Design View of Report where I Have added SRNo column at the start in grouping section.