Print Page | Close Window

Too many records

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21245
Printed Date: 28 Apr 2024 at 11:38am


Topic: Too many records
Posted By: GrisCorp
Subject: Too many records
Date 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.


Replies:
Posted By: DBlank
Date 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
 

Posted By: GrisCorp
Date 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!

Posted By: DBlank
Date 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


Print Page | Close Window