Selection Formula for Date 1

[elf1]

Hi Tek-Tips,

I need a report that will give me a list of customers who have not made any purchases (for a specified period of time) but have at least 1000.00 in Sales for the year.

If a purchase has not been made I want the date of their most current invoice displayed.

In the details section I am capturing the Invoice Data.

I have a parameter in place that allows you to specify a beginning and ending invoice date rage.

The Selection Formula in place for this report is
Invoice Dates = Invoice Date Parameter
Total of Invoices = 0
and YTD Sales =>1000

I need to come up with a formula that will be placed in the Customer Number Group Header that will display their most current invoice date.

So if a date range is entered and no invoices are pulled into the detail, what would you do to tell Crystal to print the date of the most current invoice?


Thank You,
Lisa

 

[hilfy]

There are a couple of ways I can think of to do this -

1. Use a subreport to display the date. This is fairly easy to do, but subreports will dramatically slow the report processing.

2. Add a second reference to your invoice table - Crystal will ask you to provide and alias for it - and link to it as you have to your original reference to the invoice table. Add a selection criteria that specifies that the invoice date that you want is the max of the invoice date for that customer. Then place the field on your report from this second reference to the table and selectively suppress the field based on whether or not they have invoices in the selected date range.

-D

 

[dgillz]

Are your fields 'Total of Invoices' and 'YTD Sales' a single database field or are they a summary field from detail data?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[elf1]

The Invoice Total is a summary of the detail records
The YTD sales is a single field

Thank you for helping

 

[lbass]

One approach would be to use a record selection based on the entire year to date as in:

{table.date} in YearToDate

Then group on {table.customerID} and then create a formula {@salesinperiod}:

if {table.date} in {?begindate} to currentdate then 1 else 0

Next go to report->edit selection formula->GROUP and enter:

sum({@salesinperiod},{table.customerID}) = 0 and
sum({table.salesamount}, {table.customerID}) >= 1000 and
{table.date} = maximum({table.date}, {table.customerID})

This should return those customers with no sales during the parameter period, but with sales >= 1000, and will only show the most recent invoice date which you could drag into the group header, if you wish. Depending upon how your YTD field works, you might be able to substitute it for the summary in the second clause in the group selection formula. I'm also assuming that currentdate makes the most sense for the end of your parameter period rather than a user selected date, since it looks like you are trying to identify declining sales from major customers.

-LB