multiple records in crystal report

elianeasmar

Well-known member
Joined
Oct 3, 2013
Messages
76
Programming Experience
Beginner
Hello. I am making a crystal report. i have a member that have three cars. I need to load the informations about the three cars and the informations aboout the member in the crystal report. How can i do that?
the crystal report should look something like this:


Name Name
FamilyName FamilyName
Space space
Address Address




Cars: (this is a text object)
-----
Make Model Color Plate


Make1 Model1 Color1 Plate1
Make2 Model2 Color2 Plate2
Make3 Model3 Color3 Plate3




Is that possible to do?
Thank you for your help
visual basic 2010
 
Hi,

Here is a quick demo on the BASIC steps to create a Crystal Report.

1) Design your Data Source however you want. Here I use the Northwind database to create a query of a single Order with multiple Order Details. Notice that the actual Order Info is displayed for each record of the Order Details:-
Pic1.png

2) Add that Data Source to your project and preview your information:-
Pic2.png

3) Add a Crystal Report to the project. The easiest way is to use the Wizard provided. Notice that the Order Information goes in the Report Header or Page Header depending on when you want to show the Order Information and the individual Order Details go in the Details Section:-
Pic3.png

4) Run the report to get your desired results:-
Pic4.png


Hope that helps.

Cheers,

Ian
 
Thank you. It worked. But i have another problem now. i am getting multiple records in my report. because the view i am reading from contains duplicate records. but how can i display only one record in the report?
Honda Accord Dark Blue 123456 2
Honda Accord Dark Blue 123456 2

I need only one line.
i tried to do this. but it didn't work.
strsql = "select serial,name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,month,year,amount,vat,expirydate,timezone,timezonename,Passback from membersvw where model = '" & cmbCarModel.Text & "' and serial = '" & cmbMembers.SelectedValue & "' group by serial,name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,month,year,amount,vat,expirydate,membercar,timezone,timezonename,Passback order by model,serial ;"
 
Hi,

Assuming that you have followed what I have already shown and that you are fully aware and understand why you have duplicate car details in your underlying data source then you can use the SQL Distinct Keyword to eliminate duplicates within a returned data view. Have a look at this for more information:-

Eliminating Duplicates with DISTINCT

Hope that helps.

Cheers,

Ian

BTW, two other things for you:-

a) Get rid of your Group clause. Using the Group clause in this manner is the same as returning all records in a dataset. The Group clause is used to summarise data by whatever fields you want and then typically aggregate additional information.
b) You should always use Parameters when creating SQL Queries rather than string concatenation. To understand how and why find one of jmcilhinney’s posts and have a read of his blog about using Parameters to construct your SQL queries.
 
I wrote this query:
VB.NET:
select name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback
from (select membersvw.*,
             row_number() over (partition by name order by serial) as seqnum
      from membersvw
      where familyname='Eliane El Asmar'
     ) membersvw
where seqnum = 1;


it gave me what i wanted. But once i put it in my vb code. it gives me an error: incorrect syntax near the keyword select.
This is the code:

  strsql = "select serial,name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback"
                strsql = strsql & "from (select name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback,"
                strsql = strsql & " row_number() over (partition by name order by serial) as seqnum"
                strsql = strsql & "from(membersvw)"
                strsql = strsql & "where familyname='Eliane El Asmar'"
                strsql = strsql & "  ) membersvw"
                strsql = strsql & "where seqnum = 1;"



And if i put * insted of : serial,name,familyname,address,mobile,phone,fax,email,website,space,make,model,color,plate,tag,memberpayment,month,year,amount,vat,expirydate,membercar,timezone,timezonename,passback
I get another error: Incorrect syntax near '('
 
Last edited:
Hi,

You have let this Thread wander completely off the original topic now (being Crystal Reports) so if you need to ask further questions on this issue then please create a new Thread to continue the conversation. However, have a look at the first two lines of your “strsql” variable assignment. What is missing from the End of the First line or the Start of the Second line? (it’s not the only one too!)

Hope that helps.

Cheers,

Ian
 
Back
Top