Populate (Bind) Crystal Report from multiple tables in ASP.Net

Last Reply 4 months ago By dharmendr

Posted 4 months ago

Fetching data problem from multiple tables in Crystal Report

I have a following table in the database

tblFeesGenerate

GFID Year Month Class Section AdmissionNo Fees HeadName Price Sname
1 2019 10-Oct One A R-01 1300 Sports Fee 500 A
2 2019 10-Oct One A R-01 1300 Library Fee 600 A
3 2019 10-Oct One A R-01 1300 Exam Fee 700 A
4 2019 10-Oct One A R-02 1300 Sports Fee 500 B
5 2019 10-Oct One A R-02 1300 Library Fee 600 B
6 2019 10-Oct One A R-02 1300 Exam Fee 700 B
7 2019 10-Oct One A R-03 1300 Sports Fee 500 C
8 2019 10-Oct One A R-03 1300 Library Fee 600 C
9 2019 10-Oct One A R-03 1300 Exam Fee 700 C

This fees is generated first time so there is no record in the tblFeesCollection and table is like this.

tblFeesCollection

FID AdmissionNo FeesMonth Recievable CDiscount Arrears NetBal Paid RemBal
                 

Now I want to generate fees voucher in crystal report like this

AdmissionNo: R-01     Sname: A   Month: 10-Oct

Sr         HeadName   Price

1          Fees             1300

2          Sports Fees    500

3          Library Fees    600

4          Exam Fees      700

Cdiscount: 0

Arrears: 0

Recievable: 3100

Net Balance: 3100

I have created the following dataset

https://i.imgur.com/8iViyWW

and designed the following report like this

https://i.imgur.com/SwM4qUM

and my code to fetch data from multiple tables is 

private void btnVoucher_Click(object sender, EventArgs e)
{
    DisplayReports(); //https://www.aspforums.net/Threads/334444/Populate-Crystal-Report-using-dataset-with-multiple-tables-using-C-in-ASPNet/
}
protected void DisplayReports()
{
    rptVouch crystalReport = new rptVouch();
    DSVoucher dsCustomers = GetData();
    crystalReport.SetDataSource(dsCustomers);

    this.crystalReportViewer1.ReportSource = crystalReport;
    this.crystalReportViewer1.RefreshReport();

}
private DSVoucher GetData()
{
    string constr = @"Data Source=.;Initial Catalog=Name;uid=sa;pwd=2005;";
    using (SqlConnection con = new SqlConnection(constr))
    {
        // where sm.AdmissionNo=@AdmissionNo OR @AdmissionNo IS NULL
        using (SqlCommand cmd = new SqlCommand(@"select Year,Month,IssueDate,DueDate,AcademicName,ClassName,SectionName,fg.AdmissionNo,SName,FName,RollNo,FPhone,Fees,HeadName,Price from tblFeesGenerate as fg
            inner join tblStdReg as sr on fg.AdmissionNo=sr.AdmissionNo
            inner join tblAcademicYear as ay on ay.YearID=fg.YearID
            inner join tblDefClass as dc on fg.ClassID=dc.ClassID
            inner join tblDefSection as ds on fg.SectionID=ds.SectionID
            inner join tblFeesHead as fh on fg.HeadID=fh.HeadID order by AdmissionNo asc
            select fg.AdmissionNo,fg.Year,Fees,sum(Price) 'Others',ISNULL(Fees,0) + ISNULL(sum(Price),0) 'Recievable',RemBal 'Arrears'
            ,isnull(( ISNULL(Fees,0) + ISNULL(sum(Price),0)),0) + isnull((RemBal),0) 'NetBal',fg.Month
            from tblFeesGenerate as fg left join tblFeesCollection as fc on fg.AdmissionNo=fc.AdmissionNo inner join tblStdReg as sr on fg.AdmissionNo=sr.AdmissionNo
            inner join tblDefClass as dc on fg.ClassID=dc.ClassID inner join tblDefSection as ds on fg.SectionID=ds.SectionID
            where fc.AdmissionNo is null or fc.AdmissionNo is not null and
            fg.Year = '2019' and fg.Month='10-Oct' and fg.YearID='23' and
            fc.AdmissionNo not IN(SELECT AdmissionNo FROM tblFeesCollection WHERE FeesMonth = '10-Oct' AND Paid >= 0 )
            and FeeID IN (SELECT MAX(FeeID) FROM tblFeesCollection group by AdmissionNo)
            Group by fg.AdmissionNo,fg.Month,RemBal,fg.Year,Fees order by AdmissionNo asc;"))       // where fa.SessionID=@SesId and fa.SetExamID=@EId and fa.ClassID=@CId and fa.SectionID=@SId
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                //cmd.Parameters.AddWithValue("@AdmissionNo", txtAdNo.Text.Trim());
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DSVoucher dsCustomers = new DSVoucher())
                {
                    sda.Fill(dsCustomers, "VoucherDT");
                    return dsCustomers;
                }
            }
        }
    }
}

how to show the Arrears, Recievable and NetBalance?

Posted 4 months ago

Hi smile,

Since you are returning two section it is better to add a DataTable for returning Head Name.

Then based on your database record you need to return data to dataset with filter condition.

Check the below screenshot.

Then add record to DataSet. In below code i have hard coded the value. You need to fetch the values from Database.

Code

private void Form1_Load(object sender, EventArgs e)
{
    CrystalReport1 crystalReport = new CrystalReport1();
    DSVoucher dsCustomers = GetData();
    crystalReport.Database.Tables["VoucherDT"].SetDataSource(dsCustomers.Tables["VoucherDT"]);
    crystalReport.Database.Tables["Head_Name"].SetDataSource(dsCustomers.Tables["Head_Name"]);
    crystalReportViewer1.ReportSource = crystalReport;
    crystalReportViewer1.RefreshReport();
}

private DSVoucher GetData()
{
    DSVoucher dsVoucher = new DSVoucher();
    dsVoucher.Tables["VoucherDT"].Rows.Add("1", "2019", "Oct-10", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortDateString(),
        "Name 1", "Class 1", "Sec 1", "R-01", "A", "F", "R-1", "111", "1300", "1300", "0", "0", "1300");
    dsVoucher.Tables["Head_Name"].Rows.Add("Sports Fees", "500");
    dsVoucher.Tables["Head_Name"].Rows.Add("Library Fees", "600");
    dsVoucher.Tables["Head_Name"].Rows.Add("Exam Fees", "700");
    return dsVoucher;
}

In report design add the Head_Name DataTable in the details section.

Since this section record will repeated.


Posted 4 months ago

You should place the Header (name) in the Header section.

You have placed both in the detail section.

Place the header title in the Header section.