locked
Crystal report RRS feed

  • Question

  • hello,


    I have 3 table(CourseRegResult_Tb,Outstanding,Student_Tb) from which i want to populate a crystal report(crystalReport1) .



    these 3 sql statements are just what i needed to get the data i wanted from mssql server:

    (1.) select   result.coursecode,result.coursetitle,result.unit,result.score,result.session,result.grade,result.point,result.semester from CourseRegResult_Tb Result where matricno='00002'
          
    (2.) select  outd.coursecode from outstanding_tb OUTD where matricno='00002'
          
    (3.) select  STUD.Surname,  STUD.firstname, STUD.secondname from Student_Tb STUD where STUD.MatricNo ='00002'
    I Like to populate data from these table to a crystal report.


    Here's what i've tried:

     

      Public Function Ds(ByVal SQLs As String) As Data.DataSet
            Dim ConnStr As String = "Server=localhost\sql2008;Database =Spruce_UPSHOT;integrated security=true;User Id=sa;Password=spruce"
            Dim Conn As SqlConnection = New SqlConnection(ConnStr)
            Dim Cmd As SqlCommand = New SqlCommand(SQLs, Conn)
            Dim Adapter As New SqlDataAdapter(Cmd)
            Dim MyDS As New Data.DataSet
            Adapter.Fill(MyDS)

            Return MyDS
        End Function


        Private Sub Butt_Display_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Butt_Display.Click

    Dim SQL1 As String = "select   result.coursecode,result.coursetitle,result.unit,result.score,result.session,result.grade,result.point,result.semester from CourseRegResult_Tb Result where matricno='00001'"

            Dim SQL2 As String = "select  STUD.Surname,  STUD.firstname, STUD.secondname from Student_Tb STUD where STUD.MatricNo ='00001'"
            Dim SQL3 As String = "select  outd.coursecode from outstanding_tb OUTD where matricno='00001'"
            Dim MyReport As New CrystalReport1



            RptVw.CrystalReportViewer1.ReportSource = MyReport
            MyReport.SetDataSource(Ds(SQL1))




            '  MyReport.SetDataSource(Ds(SQL1).Tables("outstanding_tb"))
            RptVw.Show()


        End Sub      

    i need a way to get data from the 3tables onto the report.

    Pls i need an help on this issue. Thanks a lot.

    Friday, February 18, 2011 3:12 PM

Answers

  • The answer is, it depends on the what the data is in the tables.  Let me explain further. 

    If it is a 1 for 1 match then you'll likely want to use a database join and not have a sub-report (i.e. the main table, let's say "people" is joining on a code table that they'll only have one of).  This will be more efficient because you will have 1 record per person and can lay that info out.  Pseudo-code:

    Select * From Person Inner Join PersonStatus On Person.StatusCode = PersonStatus.StatusCode

    Now, if it's a match where 1 person record may have multiple entries in the foreign table, you will want to use a sub report.  This might be, a person who has multiple addresses in an address table.  You would then right click on the sub report and link the sub report to the main report probably on the person's id. 

    Note, in Crystal Reports, all sub reports are run as sub queries.  So, for each main record (in my example for each person) it will run a query to get the addresses seperate.  That means, if there are 1000 people, it will run 1000 sub queries to get the addresses.  Obviously if the report is large, it can be cumbersome very quickly.  If you're output is PDF or Word for viewing or hard copy, etc. then you will have to run the entire report at once.  If you're output is being viewed through Crystal or through a Report Viewer on the web then you can have the sub report load on demand when it's clicked on which will save drastically on performance by only loading the main entries and then having the user select the sub entries they want.  This isn't always acceptable though based on a client's needs.

    • Edited by bpell Friday, February 18, 2011 3:57 PM more info
    • Proposed as answer by Calvin_Gao Monday, February 21, 2011 2:35 AM
    • Marked as answer by Calvin_Gao Friday, February 25, 2011 6:38 AM
    Friday, February 18, 2011 3:55 PM

All replies

  • Hello, Pls i need  to display data on a crystal report from 3 table in the database, i like to know if  using sub report is the best way to go about it, if yes how do i populate each report with data.
    • Merged by Calvin_Gao Monday, February 21, 2011 2:31 AM Merge them to keep in the same topic
    Friday, February 18, 2011 3:08 PM
  • The answer is, it depends on the what the data is in the tables.  Let me explain further. 

    If it is a 1 for 1 match then you'll likely want to use a database join and not have a sub-report (i.e. the main table, let's say "people" is joining on a code table that they'll only have one of).  This will be more efficient because you will have 1 record per person and can lay that info out.  Pseudo-code:

    Select * From Person Inner Join PersonStatus On Person.StatusCode = PersonStatus.StatusCode

    Now, if it's a match where 1 person record may have multiple entries in the foreign table, you will want to use a sub report.  This might be, a person who has multiple addresses in an address table.  You would then right click on the sub report and link the sub report to the main report probably on the person's id. 

    Note, in Crystal Reports, all sub reports are run as sub queries.  So, for each main record (in my example for each person) it will run a query to get the addresses seperate.  That means, if there are 1000 people, it will run 1000 sub queries to get the addresses.  Obviously if the report is large, it can be cumbersome very quickly.  If you're output is PDF or Word for viewing or hard copy, etc. then you will have to run the entire report at once.  If you're output is being viewed through Crystal or through a Report Viewer on the web then you can have the sub report load on demand when it's clicked on which will save drastically on performance by only loading the main entries and then having the user select the sub entries they want.  This isn't always acceptable though based on a client's needs.

    • Edited by bpell Friday, February 18, 2011 3:57 PM more info
    • Proposed as answer by Calvin_Gao Monday, February 21, 2011 2:35 AM
    • Marked as answer by Calvin_Gao Friday, February 25, 2011 6:38 AM
    Friday, February 18, 2011 3:55 PM
  • I responded in your other duplicate thread. :D
    Friday, February 18, 2011 3:57 PM
  • I did this, using a sub report, and i got it right but to get for all student is the issue, the subreport is always blank.

     

     

        Private Sub Butt_Prt_Std_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Butt_Prt_Std.Click
            Dim SQLIgbo As String = "select coursecode from outstanding_tb where matricno='" & Cmb_MatricNo.Text & "'"
            Dim SQLcos As String = "select courseregresult_Tb.matricno,courseregresult_Tb.coursecode  ,courseregresult_Tb.coursetitle  ,courseregresult_Tb.unit  ,courseregresult_Tb.score  ,courseregresult_Tb.session,courseregresult_Tb.grade,courseregresult_Tb.point,courseregresult_Tb.semester from courseregresult_Tb where courseregresult_Tb.matricno='" & Cmb_MatricNo.Text & "'and courseregresult_Tb.semester='" & Cmb_Sms1.Text & "' and courseregresult_Tb.session='" & Cmb_Session1.Text & "'"
            Dim SQLstud As String = "select student_tb.surname,student_tb.firstname,student_tb.secondname,student_tb.updatablelevel from student_tb where student_tb.matricno='" & Cmb_MatricNo.Text & "'  "


            Dim MyReport As New MainRPT
            Dim IgboRpt As New SubRPT

            MyReport.Subreports(0).SetDataSource(MyDsIgbo(SQLIgbo))
            RptVw.CrystalReportViewer1.ReportSource = MyReport

            MyReport.SetDataSource(MyDs(SQLcos, SQLstud))
            RptVw.Show()
        End Sub


    Spruce Integrated
    Monday, April 4, 2011 3:22 PM
  • I did this, using a sub report, and i got it right but to get for all student is the issue, the subreport is always blank.

     

     

        Private Sub Butt_Prt_Std_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Butt_Prt_Std.Click
            Dim SQLIgbo As String = "select coursecode from outstanding_tb where matricno='" & Cmb_MatricNo.Text & "'"
            Dim SQLcos As String = "select courseregresult_Tb.matricno,courseregresult_Tb.coursecode  ,courseregresult_Tb.coursetitle  ,courseregresult_Tb.unit  ,courseregresult_Tb.score  ,courseregresult_Tb.session,courseregresult_Tb.grade,courseregresult_Tb.point,courseregresult_Tb.semester from courseregresult_Tb where courseregresult_Tb.matricno='" & Cmb_MatricNo.Text & "'and courseregresult_Tb.semester='" & Cmb_Sms1.Text & "' and courseregresult_Tb.session='" & Cmb_Session1.Text & "'"
            Dim SQLstud As String = "select student_tb.surname,student_tb.firstname,student_tb.secondname,student_tb.updatablelevel from student_tb where student_tb.matricno='" & Cmb_MatricNo.Text & "'  "


            Dim MyReport As New MainRPT
            Dim IgboRpt As New SubRPT

            MyReport.Subreports(0).SetDataSource(MyDsIgbo(SQLIgbo))
            RptVw.CrystalReportViewer1.ReportSource = MyReport

            MyReport.SetDataSource(MyDs(SQLcos, SQLstud))
            RptVw.Show()
        End Sub


    Spruce Integrated
    Monday, April 4, 2011 3:23 PM