Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Too many records Post Reply Post New Topic
Author Message
GrisCorp
Groupie
Groupie
Avatar

Joined: 08 Mar 2013
Online Status: Offline
Posts: 64
Quote GrisCorp Replybullet Topic: Too many records
    Posted: 31 Dec 2014 at 5:39am
I am using Crystal Reports XI R2.  I have a report that pulls all the customer from our Customer Master database creates sheets of address labels.  This report only created one label per customer.

I was asked by Customer Support to add a parameter to only print customer labels with customer orders between a date range they choose.  I added the parameter and had to add another table for the order dates.  I linked the tables by the Customer ID.  I added a Record Selection formula (previously there was none) and ran the report.

I now get one label for every order instead of one label for every customer.  I get multiple labels for each customer; one for each order, I believe.  I changed the table link from Not Enforced and got it to drop about 350 records with both Enforced To and Enforced From.  How can I correct this so that I get only one label for each customer who placed an order between the specified dates?

Tables in report:
Customer Master
Order Master
Tables linked:
Customer Master.CustID  ---LOJ---> OrderMaster.CustID

Record Selection formula:
{Order Master.ORDERDATE} in {?Date Range}

I have all the fields for the label in a text box and I have formatted it to Suppress If Duplicated, but it does not have any effect since technically the records aren't duplicates.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Dec 2014 at 5:48am
a few options
- use a command or stored proc to use a "select distinct"
- group on customer and move everything to the header
- under Database try the 'Select Distinct Records option'
- conditionally suppress your detail section where next(customerid)=customerid
 
IP IP Logged
GrisCorp
Groupie
Groupie
Avatar

Joined: 08 Mar 2013
Online Status: Offline
Posts: 64
Quote GrisCorp Replybullet Posted: 31 Dec 2014 at 6:22am
DBlank,

I know a little about using commands but I have not learned how to use stored procedures. Learning SQL online as I have time through Microsoft's Virtual Training.

I could not move everything to the header as I needed the labels to print three across; the Details section is formatted with columns.

'Select Distinct Records' was already selected.

I got it to work by conditionally suppressing the Details section with:

     next ({OrderMaster.CustID}) = {OrderMaster.CustID}

Thank you so much for your help!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Dec 2014 at 6:35am
glad you got it to work
FYI-watch your last row in the report.
you may need to make sure that the the suppression formula is set to use defaults for nulls
IP IP Logged
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.