Writing Code
 Crystal Reports Forum : Crystal Reports .NET 2005/2008 : Writing Code
Message Icon Topic: crystal report duplicate data Post Reply Post New Topic
Page  of 2 Next >>
Author Message
ChristianMuk
Newbie
Newbie
Avatar

Joined: 27 Sep 2012
Location: South Africa
Online Status: Offline
Posts: 15
Quote ChristianMuk Replybullet Topic: crystal report duplicate data
    Posted: 27 Nov 2012 at 12:18am
Hi, i am having a problem with crystal report duplicating records
first i was getting all the data duplicatingon my report but i have found out something to solve that and now they don't duplicate anymore but now the problem i am experiencing is that the data from other tables are repeating themselves
i Guess this has something to do with my joining type but i have spent a night just trying to solve this issue but with no luck.
cna anyone helps me....all my relationship are one to many and i am using INNER JOIN in my command to join tables and pull out data in my DB
 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3275
Quote hilfy Replybullet Posted: 28 Nov 2012 at 4:01am

How does your report look now and how do you want it to look?  I have some thoughts about how to do this, but I don't want to give you bad information.

 
-Dell
DecisionFirst Technologies
7-Time SAP BusinessObjects Service Partner of the Year.
www.decisionfirst.com
IP IP Logged
ChristianMuk
Newbie
Newbie
Avatar

Joined: 27 Sep 2012
Location: South Africa
Online Status: Offline
Posts: 15
Quote ChristianMuk Replybullet Posted: 03 Dec 2012 at 10:18pm
i managed to remove the duplicate items but now the problem i am having is:
Here's how to suppose to be
ETnumber   Enquiry Date    SalesPerson  Likelihood  CompanyName Contacts
ET 2541      2010-06-12     Chris            3A              HBA                George
ET 8741      2009-12-14     Tommy         3B              RCB                Danny
ET 1000      2009-07-06     Oscar           6C               Evapco            Jacques
 
and here's what am getting
ETnumber Enquiry Date SalesPerson Likelihood CompanyName Contacts
ET 2541    2010-06-12   Chris          3A             HBA               George
ET 8741    2009-12-14   Chris          3A             HBA               George
ET 1000    2009-07-06   Chris          3A             HBA               George
 
The sales person(saleperson Table) Likelihood(Likelihood table) company name(Customers table) and contacts(Contact table)
respectively come from their own table and i do not know how to fix this issue can you help me
below is my code:

Private Sub BtnTenderOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnTenderOk.Click

TenderReportForm.Show()

       

Dim con As New SqlClient.SqlConnection(My.Settings.NewEvapcoDBConnectionString.ToString)

Dim cmd As New SqlClient.SqlCommand("Select Contacts.Name,Customers.CompanyName,Likelihood.LikelihoodLevel,sales.SalesPerson, Tender.ETNumber, Tender.Enquirydate,Tender.Projectname,Tender.UnitType,Tender.TenderValue,Tender.ReqDate,Tender.NumberUnits FROM Contacts INNER JOIN Customers ON Contacts.CustomerID=Customers.CustomerID INNER JOIN tender ON Customers.CustomerID=Tender.CustomerID INNER JOIN Likelihood ON Tender.LikelihoodID=Likelihood.LikelihoodID INNER JOIN Sales ON Tender.SalesID=Sales.SalesID") ' WHERE sales.salesperson LIKE '%" + TextBox2.Text + "%' AND Likelihood.LikelihoodLevel LIKE '%" + TextBox1.Text + "%'") ' Sales.SalesPerson LIKE 'Ivan%' AND Likelihood.LikelihoodLevel LIKE '%A'")

Dim da As New SqlClient.SqlDataAdapter(cmd)

Dim ds As New TenderDataSet

Dim strReportName As String = "TenderReport"

Dim rptdoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument

Dim strReportpath As String = ("D:\New EvapcoDB\New EvapcoDB\TenderReport.rpt")

Try

cmd.Connection = con

con.Open()

ds.Clear()

da.Fill(ds,

"Tenderdataset")

If cmd.ExecuteNonQuery = False Then

MsgBox(

"An error has occured while creating the report", vbCritical, "Error")

End If

If Not IO.File.Exists(strReportpath) Then

MsgBox(

"The report was not found", vbCritical, "Error")

con.Close()

Exit Sub

End If

rptdoc.Load(strReportpath)

rptdoc.Database.Tables(

"Tender").SetDataSource(ds.Tables(1))

rptdoc.Database.Tables(

"Sales").SetDataSource(ds.Tables(1))

rptdoc.Database.Tables(

"Likelihood").SetDataSource(ds.Tables(1))

rptdoc.Database.Tables(

"Customers").SetDataSource(ds.Tables(1))

rptdoc.Database.Tables(

"Contacts").SetDataSource(ds.Tables(1))

rptdoc.Refresh()

TenderReportForm.TenderCrystalReportViewer.ReportSource = rptdoc

TenderReportForm.TenderCrystalReportViewer.Refresh()

Catch ex As Exception

MsgBox(

"An error has occured while creating the report", vbCritical, "ERROR")

'System.Windows.Forms.MessageBox.Show(ex.Message)

End Try

End Sub

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3275
Quote hilfy Replybullet Posted: 04 Dec 2012 at 4:01am
This is probably a linking error between your tables.  How are the table links set up in Crystal?  Or, if you're using a command, what is the SQL?
Also, how did you resolve the duplicate data issue?  That may have a bearing on why you're seeing what you're seeing.
 
-Dell
DecisionFirst Technologies
7-Time SAP BusinessObjects Service Partner of the Year.
www.decisionfirst.com
IP IP Logged
pgeorge33
Newbie
Newbie


Joined: 04 Dec 2012
Online Status: Offline
Posts: 8
Quote pgeorge33 Replybullet Posted: 04 Dec 2012 at 7:45am
I'm experiencing the same problem. Where can I find information on how the tables should be linked? It's quite different to the way I am used to linking in Access.
Peter
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3275
Quote hilfy Replybullet Posted: 04 Dec 2012 at 8:08am
Give me some info about your tables - what are the tables, what fields are you linking on, what information you're trying to show in the report.  I should be able to walk you through it from there.
 
-Dell
DecisionFirst Technologies
7-Time SAP BusinessObjects Service Partner of the Year.
www.decisionfirst.com
IP IP Logged
ChristianMuk
Newbie
Newbie
Avatar

Joined: 27 Sep 2012
Location: South Africa
Online Status: Offline
Posts: 15
Quote ChristianMuk Replybullet Posted: 04 Dec 2012 at 7:56pm
To resolve my first problem i grouped my report base on the ETnumber then i put all my parameters which were suppose to be in section details under group Header section then i suppress the section details and there was no duplication anymore but only the parameter coming from different tables were duplicating as shown in my previous post
Yes,My tables are link in CR like this:
 
*Table Tender contains:ETNumber(PK),SalesID(FK),LikelihoodID(FK),CustomerID(FK)
*Table SalesPerson contains:SalesID(PK),SalesPerson and this table is link to the Tender table on SalesID
*Table Likelihood conatins:LikelihoodID(PK),Likelihoodlevel and this table is link to the tander table on LikelihoodID
*Table Customers Contains:CustomerID(PK),CompanyName and this table is link to the tender table on CustomerID
*Table Contact Contains:ContactID(PK),Customer(FK) and this table is link to customer table on customerID
 
and i am using INNER JOIN on my command to retrieve data as follow:

Dim

cmd As New SqlClient.SqlCommand("Select Contacts.Name,Customers.CompanyName,Likelihood.LikelihoodLevel,sales.SalesPerson, Tender.ETNumber, Tender.Enquirydate,Tender.Projectname,Tender.UnitType,Tender.TenderValue,Tender.ReqDate,Tender.NumberUnits FROM Contacts INNER JOIN Customers ON Contacts.CustomerID=Customers.CustomerID INNER JOIN tender ON Customers.CustomerID=Tender.CustomerID INNER JOIN Likelihood ON Tender.LikelihoodID=Likelihood.LikelihoodID INNER JOIN Sales ON Tender.SalesID=Sales.SalesID")

how can i insert an image on how my tables are linked so you can see it in case you do not understand my explaination on how my tables are linked.
please help me
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3275
Quote hilfy Replybullet Posted: 05 Dec 2012 at 3:27am
Have you tested the query in a tool outside of Crystal to see what it's returning - for example Toad or SQL Server Management Studio?
 
I'm also assuming that the "duplicates" are because there is more than one contact for some customers.  You could also try using SQL similar to this:
 
Select
from Customers
  inner join (
--gets the first contact - change to Max if you want the most recent contact
--or use additional filters to get the specific contact you need.
    Select Contacts.CustomerID, min(Contacts.ContactID) as ContactID
    from Contacts
    group by Contacts.CustomerID) contactFilter
  on Customer.CustomerID = contactFilter.CustomerID
  inner join Contacts on contactFilter.ContactID = Contacts.ContactID
  inner join Tender on Customers.CustomerID = Tender.CustomerID
  inner join Likelihood on Tender.LikelihoodID = Likelihood.LikelihoodID
  inner join Sales on Tender.SalesID = Sales.SalesID
 
The contactFilter sub-query will limit the result set to a single contact so that you shouldn't have duplicates in the data set to begin with.
 
-Dell
DecisionFirst Technologies
7-Time SAP BusinessObjects Service Partner of the Year.
www.decisionfirst.com
IP IP Logged
ChristianMuk
Newbie
Newbie
Avatar

Joined: 27 Sep 2012
Location: South Africa
Online Status: Offline
Posts: 15
Quote ChristianMuk Replybullet Posted: 05 Dec 2012 at 7:55pm
That's great Hilfy i am actually able to specify the contact person that i want but now what about the salesPerson, the Likelihood and the Company they stil duplicating as shown in my previous post.
How can i retrieve the right salesperson,Likelihood and Companyname for a particular ETNumber
You are right 1 customer can have many contact so if am getting you right i must specify which contact i want to retrieve
but 1 ETNumber can only have 1 salesPerson,1 likelihood....
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3275
Quote hilfy Replybullet Posted: 06 Dec 2012 at 3:40am
Does the data only have one salesPerson and one likelihood or are there multiples of those as well?  If there is only one record per ETNumber in each, they you shouldn't see duplicate data.  If there can be multiples of either, you'll need to do something with them that is similar to how the contact piece is set up.
One other thing you can try is to add the work "Distinct" after the Select in the query.  I would run the query outside of Crystal so that you can see the raw data results and get the query running correctly before setting it up in the report.
 
-Dell
DecisionFirst Technologies
7-Time SAP BusinessObjects Service Partner of the Year.
www.decisionfirst.com
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.

FREE ADVANCED
Crystal Reports Training!

Plus, free bonus PDF cheat sheet! Advanced Crystal Reports Training Give Me Advanced Training!