I have designed a crystal report in that design, i want to display the subject wise result details of seven students. it is showing me data with some subject missing.
For example, I have added the marks of three students in the following five subject i.e Urdu, English, Math, Science, Geography.
it is showing me result of only two subjects whereas the result of three subjects are missing.
here is sample Data
|
Exam
|
AdmissionNo
|
Subject
|
Max
|
Obtain
|
|
Test-1
|
R-01
|
Urdu
|
25
|
18
|
|
Test-1
|
R-01
|
English
|
25
|
19
|
|
Test-1
|
R-01
|
Math
|
25
|
21
|
|
Test-1
|
R-01
|
Science
|
25
|
20
|
|
Test-1
|
R-01
|
Geograpy
|
25
|
18
|
|
Test-1
|
R-02
|
Urdu
|
25
|
14
|
|
Test-1
|
R-02
|
English
|
25
|
16
|
|
Test-1
|
R-02
|
Math
|
25
|
18
|
|
Test-1
|
R-02
|
Science
|
25
|
15
|
|
Test-1
|
R-02
|
Geograpy
|
25
|
20
|
|
Test-1
|
R-03
|
Urdu
|
25
|
21
|
|
Test-1
|
R-03
|
English
|
25
|
22
|
|
Test-1
|
R-03
|
Math
|
25
|
18
|
|
Test-1
|
R-03
|
Science
|
25
|
21
|
|
Test-1
|
R-03
|
Geograpy
|
25
|
22
|
whereas my code is:
protected void DisplayResult()
{
CrystalCards crystalReport = new CrystalCards();
DSCards dsCustomers = GetData();
crystalReport.SetDataSource(dsCustomers);
crystalReport.Database.Tables["DTExams"].SetDataSource(dsCustomers.Tables["DTExams"]);
crystalReport.Database.Tables["DTPositions"].SetDataSource(dsCustomers.Tables["DTPositions"]);
this.crystalReportViewer1.ReportSource = crystalReport;
this.crystalReportViewer1.RefreshReport();
}
private DSCards GetData()
{
DSCards dsVoucher = new DSCards();
con = new SqlDbConnect();
con.SqlQuery(@"SELECT SPic,SessionName,ExamType,fa.AdmissionNo,ReferenceNo,SName,FName,FPhone,Address,AcademicName,ClassName,SectionName,SubjectName,Max,Mark
,Round((Mark) * 100/ (Max),1) as SubPer
,rank() over(partition BY SubjectName order by Mark DESC) AS SubPos
,(CASE
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 96 THEN 'A+ Gold'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B+'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'B'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 55 THEN 'C'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
ELSE 'FAIL' END ) AS Grade
,(CASE
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 96 THEN 'Superab'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
ELSE 'FAIL' END ) AS Remarks
FROM tblSetMarks as fa
inner join tblSession as ses on fa.SessionID=ses.SessionID
inner join tblSetExam as st on fa.SetExamID=st.SetExamID
inner join tblStdReg as sr on fa.AdmissionNo=sr.AdmissionNo
inner join tblAssignSubjects as fh on fa.SubjectID=fh.ASID inner join tblDefSubject as df on fh.SubjectID=df.SubjectID
inner join tblAcademicYear as ay on sr.YearID=ay.YearID
inner join tblDefClass as dc on sr.ClassID=dc.ClassID
inner join tblDefSection as ds on sr.SectionID=ds.SectionID
where fa.SessionID=@SesId and fa.SetExamID=@EId and fa.ClassID=@CId and fa.SectionID=@SId
group by SPic,SessionName,ExamType,fa.AdmissionNo,ReferenceNo,SName,FName,FPhone,Address,AcademicName,ClassName,SectionName,SubjectName,Max,Mark order by AdmissionNo,SubjectName desc;"); //
con.Cmd.Parameters.Add(new SqlParameter("@SesId", this.cmbSession.SelectedValue.ToString()));
con.Cmd.Parameters.Add(new SqlParameter("@EId", this.cmbExam.SelectedValue.ToString()));
con.Cmd.Parameters.Add(new SqlParameter("@CId", this.cmbClass.SelectedValue.ToString()));
con.Cmd.Parameters.Add(new SqlParameter("@SId", this.cmbSection.SelectedValue.ToString()));
paging.SelectCommand = con.Cmd;
paging.Fill(dsVoucher, "DTExams");
// sTable = dsVoucher.Tables["HeadDT"];
con.SqlQuery(@"SELECT * FROM (
SELECT AdmissionNo,sum(Max) 'Total',sum(Mark) 'Obtain'
,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS Position
,Round(sum(Mark) * 100/ sum(Max),1) as Percentage
,(CASE
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 96 THEN 'A+ Gold'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B+'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'B'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 55 THEN 'C'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
ELSE 'FAIL' END ) AS Grade
,(CASE
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 96 THEN 'Superab'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
ELSE 'FAIL' END ) AS Remarks
,(CASE
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 96 THEN 'Superab'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'His/Her performance has been outstanding.Keep it up.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Keep up the good work.All the best.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'He/She has the potential to do more.All the best.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'He/She has been doing good in studies.'
WHEN Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'He/She needs to work hard in order to improve grades.'
ELSE 'He/She is advised to pay attention towards studies.' END ) AS TRemarks
FROM tblSetMarks as sm
inner join tblSession as ses on sm.SessionID=ses.SessionID
inner join tblSetExam as st on sm.SetExamID=st.SetExamID
inner join tblDefClass as dc on sm.ClassID=dc.ClassID
inner join tblDefSection as ds on sm.SectionID=ds.SectionID
where sm.SessionID=@SesId and sm.SetExamID=@EId and sm.ClassID=@CId and sm.SectionID=@SId
GROUP BY AdmissionNo ) t
order by AdmissionNo asc;"); // //WHERE t.AdmissionNo = @Ad
con.Cmd.Parameters.Add(new SqlParameter("@SesId", this.cmbSession.SelectedValue.ToString()));
con.Cmd.Parameters.Add(new SqlParameter("@EId", this.cmbExam.SelectedValue.ToString()));
con.Cmd.Parameters.Add(new SqlParameter("@CId", this.cmbClass.SelectedValue.ToString()));
con.Cmd.Parameters.Add(new SqlParameter("@SId", this.cmbSection.SelectedValue.ToString()));
paging.SelectCommand = con.Cmd;
paging.Fill(dsVoucher, "DTPositions");
// sTable = dsVoucher.Tables["VoucherDT"];
return dsVoucher;
}
how to fix it????
