crystal report duplicate data
Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Writing Code
Forum Discription: .NET programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=18079
Printed Date: 23 Apr 2024 at 9:15pm
Topic: crystal report duplicate data
Posted By: ChristianMuk
Subject: crystal report duplicate data
Date 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
|
Replies:
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ChristianMuk
Date 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 |
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: pgeorge33
Date 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
|
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ChristianMuk
Date 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 |
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ChristianMuk
Date 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....
|
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ChristianMuk
Date Posted: 10 Dec 2012 at 7:14pm
|
Yes The salesPerson and Likelihood can be different which means there are Multiple of salesPerson and Likelihood and i tried adding the word Distinct after Select in the query there is no difference.am really out of idea for this issue and many thanks for your concern and help into this.
|
Posted By: hilfy
Date Posted: 11 Dec 2012 at 3:36am
|
So, the question becomes, how do you determine which sales person and likelihood to display? Or is it just the first combination, whatever that is, that you need? -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ChristianMuk
Date Posted: 11 Dec 2012 at 6:53pm
|
My salesPerson and Likelihood are first select when the user is entering the Info into the DB then save to the DB and there is only one salesPerson and one likelihood per ETNUMBER
|
Posted By: hilfy
Date Posted: 12 Dec 2012 at 3:21am
|
I'm hearing conflicting information from you:
Yes The salesPerson and Likelihood can be different which means there are Multiple of salesPerson and Likelihood and
My salesPerson and Likelihood are first select when the user is entering the Info into the DB then save to the DB and there is only one salesPerson and one likelihood per ETNUMBER The first one means that there are multiple combinations and the second means there is only one. How you'll handle this is based on which statement is true. -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ChristianMuk
Date Posted: 12 Dec 2012 at 6:45pm
|
yes you are right i was confusing you, sorry for that. OK, here's the scenario there is a table called Sales and the salesPerson comes from there and there are Multiple SalesPerson in that table and there is another table Called Likelihood and the Likelihood comes from there again there are multiple of Likelihood in that table NOW, on the Tender table is where we found the ETNumber and one ETNumber can only have One SalesPerson and One Likelihood. and on the report all i want is to the ETNumber with its SalesPerson and its Likelihood respectively. SORRY again for the confusion
|
Posted By: hilfy
Date Posted: 13 Dec 2012 at 3:16am
|
Since the link from Tender to Sales and to Likelihood is one-to-one, that's not where your duplicate data is coming from. Can Tender have multiple rows per ETNumber? Or do you have other tables in the report that have a one-to-many relationship with ETNumber that would be causing duplicate data? -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ChristianMuk
Date Posted: 13 Dec 2012 at 8:00pm
|
No a T ender can only have one row per ETNumber.but now i am starting to think since i have a customers Table which is link to the Tender Table with a one-to-one relationship and the Contact Table is link to the Customers with a one-to-many relationship might be the problem Relationships Cardinalities: Contact Table(ContactID(PK),CustomerID(FK)) -->One-to-many-->Customers Table(CustomerID(PK)) Customers Table(CustomerID(PK)) -->One-to-One-->Tender Table(ETNumber(PK),CustomerID(FK)) Tender Table(ETNumber(PK),SalesID(FK))-->One-to-One-->Sales Table(SalesID(PK)) Tender Table(ETNumber(PK),LikelihoodID(FK))-->One-to-One-->Likelihood Table(LikelihoodID(FK))
|
Posted By: hilfy
Date Posted: 17 Dec 2012 at 4:56am
|
The one-to-many IS your problem. You need to determine how to limit the contacts to one record. -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ChristianMuk
Date Posted: 14 Jan 2013 at 11:52pm
|
HI Hilfysorry i wasnt around for quiet a long time i went to see my family. i understand what you are saying but can you help me on how to limit a contacts to only one record?
|
|