I'm making a printable Purchase Order form using Crystal Report. This report retrieves data from two tables. Example, I have fields PONumber,DateofPO,Airline,DateofFlight,ETA,ETD from a table called tblFlight. I have fields PassengerName and Rate on another table called tblPassenger. Each PONumber can have multiple passengers. How can I get it so that I'll just be generating one PO that shows the multiple passengers while the rest of the items show only once? I've placed the PONumber and DateofPO on the PageHeader and the rest of the items on the Details section. But right now, on the Details section, if I have 3 passengers, the other fields also show 3 times on the report. Also, I'm considering to make a query but I have a conditional statement on my query, a WHERE clause that retrieves data dynamically. My problem is I'm not sure where to put the query, will it be on the .rpt or just on the Form? NOTE: I'm coding my SQL queries on the application itself, not inside the server, so no stored procedures/functions for me here.

Would greatly appreciate any help. Thanks in advance.

6 Years
Discussion Span
Last Post by Chair

WELL, I've solved this (again) :=) Just in case, someone has the same problem: The solution is putting tblPassenger on a Crystal Report sub-report, and then link that to a primary field in the main report (in this case, PONumber is my primary field); you can do this linking through the Crystal Report Sub-report wizard. Then place this code on Form_Load of your report:

Dim con as New SqlConnection("your connection string")
Dim com as New SqlCommand
Dim adapt as New SqlDataAdapter
Dim ds as New DataSet
Dim rptdocument as New ReportDocument
Dim viewer as CrystalDecisions.Windows.Forms.CrystalReportViewer

        com.Connection = con
        com.CommandText = "yourquery"
        adapt.SelectCommand = com
        adapt.Fill(ds, "yourtable")
        viewer.ReportSource = rptdocument
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.