| Author |
Message |
ChristianMuk
Newbie
Joined: 27 Sep 2012
Location: South Africa
Online Status: Offline
Posts: 15
|
   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 Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
|
|
|
|
IP Logged |
|
ChristianMuk
Newbie
Joined: 27 Sep 2012
Location: South Africa
Online Status: Offline
Posts: 15
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
|
|
|
|
IP Logged |
|
pgeorge33
Newbie
Joined: 04 Dec 2012
Online Status: Offline
Posts: 8
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
|
|
|
|
IP Logged |
|
ChristianMuk
Newbie
Joined: 27 Sep 2012
Location: South Africa
Online Status: Offline
Posts: 15
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
|
|
|
|
IP Logged |
|
ChristianMuk
Newbie
Joined: 27 Sep 2012
Location: South Africa
Online Status: Offline
Posts: 15
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
|
|
|
|
IP Logged |
|
|
|
