Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Listing Customers Just Once Post Reply Post New Topic
Author Message
bmurphywa
Newbie
Newbie


Joined: 07 Aug 2009
Online Status: Offline
Posts: 15
Quote bmurphywa Replybullet Topic: Listing Customers Just Once
     Posted: 07 Aug 2009 at 7:37pm
I have a CUSTOMER TABLE (name, address, cust id, etc) and an ORDER TABLE (cust id, product no, etc).  A customer may be listed many times in the order table as he may have ordered numerous products.
 
I have spent hours trying to think this out but my brain is twisted all up now and I'm lost.  I'm sure it has something to do with an OUTER JOIN but implementing that is apparently beyond me at the moment.
 
Here's the question:
 
Some customers order only Product A
Some customers order only Product B
Some customers order both product A and B (on different dates, hence different rows)
 
I can readily find the list of customers who ordered Product A
I can readily find the list of customers who order Product B
 
I want to create a list of all customers with each customer listed just once.  Those who ordered both A and B appear twice.
 
This should be easy. In SQL I can use "DISTINCT NAME" but that apparently isn't available in Crystal. My efforts have failed me.  Please help.
 
 
Thanks more than you can imagine.
Bill


Edited by bmurphywa - 07 Aug 2009 at 7:38pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Aug 2009 at 8:05am
A few ways to do this
 
1- just group on the customer name and suppress the details row and GF.
Or better use a distinct Customer ID and place the customer name on the GH
 
2 - sort alpha by name and if the name is all in one field then suppress duplicates by right clicking on the field and selecting propertires and it is there. If it is not all in one field you can make a formula field and add the name fields together and do it that way. Mkae sure to set your detail section to suppress if blank.
 
3. - Conditionally suppress your detail row if previous(namefield)=namefield
 
4.You can get a record count by doing a SUmmary Field as a DistinctCount.


Edited by DBlank - 08 Aug 2009 at 8:06am
IP IP Logged
bmurphywa
Newbie
Newbie


Joined: 07 Aug 2009
Online Status: Offline
Posts: 15
Quote bmurphywa Replybullet Posted: 08 Aug 2009 at 8:37am
Thanks.  I like suggestion 3 and will give that a try and report back.
IP IP Logged
bmurphywa
Newbie
Newbie


Joined: 07 Aug 2009
Online Status: Offline
Posts: 15
Quote bmurphywa Replybullet Posted: 08 Aug 2009 at 1:45pm

Having considered and attempted to implement the suggestions above, I think I can clarify a bit.  For purposes of satisfying the user (exec management), the report must be grouped by date (monthly since the beginning of this year).  Therefore, the entries for customers who order twice are often on different pages (and in different monthly groups) so the "previous" solution you proposed doesn't seem to fit.

More information:
 
88,000+ records, 2000+ customers, data since 2003
 
I wrote an "Add Command" (DISTINCTNAMEQUERY) in the "Database Expert" and browsed the resulting data in the virtual table shown in the CR Field Explorer.  As I had hoped, the results were 128 unique customer names.  So I ran the report using the selection expert pointed to DISTINCTNAMEQUERY.  UGH, the server ran full tilt for several minutes before I stopped it and saw that it was retreiving many more instances than 128.
 
Here's what is displayed at Database>Show SQl Query
 
SELECT "data21"."DATE", "data61"."TITLE", "data40"."FIRST_DATE", "data21"."NAME"
FROM   ("data40" "data40" INNER JOIN "data21" "data21" ON "data40"."CLIENT"="data21"."CLIENT") INNER JOIN "data61" "data61" ON "data40"."REF_CODE"="data61"."CODE"
WHERE  "data21"."DATE">{d '2009-01-01'}
SELECT DISTINCT NAME, CODE, DATE FROM DATA21 WHERE DATE>'01/01/2009' and (CODE='CAT' OR CODE='DOG' OR CODE='FISH')
 
The last two lines are the code from my DISTINCTNAMEQUERY "Add Command".  When I run the last two lines directly on the SQl server, I instantly get 128 unique results, just like when I "browse data" in the CR Field Explorer.
 
The Selection Expert formula is:
 
{DISTINCTNAMEQUERY.DATE} > Date (2009, 01, 01) and
{DISTINCTNAMEQUERY.CODE} in ["CAT", "DOG", "FISH"]
 
Despite the DISTINCTNAMEQUERY command yielding only 128 unique names, when I run the report it wants to print 100s if not 1000s of entries many of which are duplicates.
 
I don't know where my logic is flawed but, obviously, I'm not understanding something here.  Can you see where my error is?
 
Thanks.
Bill Murphy
 
EDIT: I just realized my original question concerned only products A and B  yet my code shows 3 products.  One can be ignored.  It is an exclusive code so it is easy to pull out the "Fish" customers from the database.  It is the "Cat" and "Dog" customers that overlap and cause multiple entries from the same customer.  It is not important for the report to list what they ordered so I just want a customer list without the overlap.
 
 


Edited by bmurphywa - 08 Aug 2009 at 5:25pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Aug 2009 at 7:35pm
This is a different question...and you need to get more clarity from the exec. They want it grouped but a distinct customer list. Is this distinct by month, distinct by max sale month or distinct by first sale in the report or what. The answer to that will define how you handle this.
In your SQl your distinct query is fine when counting because there are no conflicting data items. Once you started bringing in the other elements you did not do any grouping nor handle it as a MAX or MIN for the elements that had more than one possible value. Hence it returning more than 128...
My guess will be they want "both" a unique count/view per month of customers and also a unique count for the whole date period Which is easy to handle with crystal without any SQL issues.
Post an update on your design need and we can hopefully guide you from that.Thumbs%20Up


Edited by DBlank - 08 Aug 2009 at 7:38pm
IP IP Logged
bmurphywa
Newbie
Newbie


Joined: 07 Aug 2009
Online Status: Offline
Posts: 15
Quote bmurphywa Replybullet Posted: 08 Aug 2009 at 8:52pm
Thanks for pondering through this.  My apologies for not providing enough info.  The required report is as follows:
 
Group by Month (January 2009, etc)
    Sub-Group 1 by Referral Source 1 (Ref_Code 1)
               List of Customers Referred by 1
    Sub Group 2  by Referral Source 2 (Ref_Code 2)
                List of Customers Referred by 2
    Sub Groups x through z follow for each referral source showing the list of customers referred by each referral source
 
etc., etc. for each month.  The referral sources can change each month and the customers are almost always different each month.
 
A customer can appear in January because he purchased item A.  He can then appear in March, for example, when he purchases Item B. (He is listed once in the Customer table and each time he orders in the Order table.)  However, only his first purchase qualifies as a referral, from then on he is our customer so we do not want him appearing again on this list in March.  While I might want to create a field to distinguish referrals customers from repeat customers, I do not have that authority so I have to work with what we have.
 
This is not a numbers question per se.  The execs want to know where their referral business is coming from - more of a qualitative report than quantitative.
 
Thanks again.
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Aug 2009 at 9:54am

Given this description and since a cyustomer may already be a customer prior to the date ranges of a report I assume they should be excluded altogther which means you may have less than the 128 distinct customers.

You have a few options here how you want to approach this. If your system / environment allows for you to create sutom views or stored procedures you can do thet there otherwise you will have to use the Commnad option.
You will need to create an data source from one of the options mentioned above that collapses your Sales table into one row per customer where the sales date is the min value per cusomer. YOu can then join that back into the tables as your filter. You can do this via grouping in the SQL statment and then using the MIN value of the SAles date. DOn't filter by date yet or you will get false positives as the min value in that range poer customer. Filter via crystal for your date range. I would also make those paramters so your sustmer can choose their own range at run time.
From there the report will be easy because it will just be a matter of grouping the data that is already filtered.
Hope that helps.


Edited by DBlank - 09 Aug 2009 at 9:54am
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.