Display GroupWise data and Total in Crystal Report in Windows Application

Last Reply 2 months ago By smile

Posted 2 months ago

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????


Posted 2 months ago

I solved my issue using Crystal Report Formula. I am even poor in thanks for your kind co operation.