Can Crystal report on data not in the database ?? 2

[AJANETA]

Is it possible to produce a report that will show me all the customers who HAVE NOT placed an order, OR have not placed an order in the past 2 years.

I have a companies table, with a left join to Orders table.

It shows me all the companies and prints the date of the order(s) or leaves it blank if they have not ordered.

I have done a maximum formula to pick out the latest date of the order, but now I am stuck, I cannot think of a way to use this formula to only select customers with orders older than 2 years, and cannot work out a way of getting the list of customers who have not placed an order at all

Hope this makes sense

Thanks, Janet (ajaneta)
Management Information

 

[Naith]

You can very easily get the information you require based on the tables and joins you're using.

To get customers who have never placed an order, use a formula along the lines of:

if isnull({order.date}) then {customer.name} else '';

To get customers who haven't ordered anything for a couple of years, use a formula like:

if {order_date} < currentdate-730 then {customer.name} else '';

You can clean these formulae up as you see fit, but in principle they should do you.

Naith

 

[IdoMillet]

Your record selection formula should be something like:

IsNull({orders.date}) // no orders
OR
DateDiff (&quot;yyyy&quot;,
Maximum({orders.order_date}, {orders.CUSTOMER ID}),
CurrentDate)>1

hth,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[kenhamady]

Ido,

You can't use the Maximum in your record selection formula. You could put this in your group selection formula Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[IdoMillet]

Ken is 100% right; the expression should be entered as a &quot;Group&quot; (not &quot;Record&quot Selection formula.

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[chelseatech]

Download the back issues of Crystal Clear from my web site. There is a series of articles on how to report on data that is not there.

It includes customers with no transactions, missing transactions, empty subreports, etc. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm