Null Testing

[JPray]

I am a very new user of Crystal. I want to show sales in the year 1996 for all customers in California. So I wrote the following selection formula:

{Customer.Region}="CA" and {Orders.Order Date} in Date (1996, 02,18) to Date(1996,12,31)

When I ran the report, I lost customers from California that didn't have any orders in that time period. I don't want that. I want all California customers to appear regardless of whether they had sales. Also, I want those customers without sales to display the text "No Sales."

The report includes a formula that groups the report by Customer Name with Customer ID in parentheses. The report also includes the Orders.Order Amount field with a summary and grand total thats sums the Order Amount field.

How can I edit my selection formula above to include the nulls and when the nulls are displayed, display the text No Sales?

Thanks for any light you can shed.

 

[AndrewBaines]

Fist make the join between customer and orders a left outer join.
Then change the selection formula to:
isnull({Orders.Order Date}) or {Orders.Order Date} in DateTime (1996, 02, 18, 00, 00, 00) to DateTime (1996, 12, 31, 00, 00, 00) and
{Customer.Region} = "CA"
Then create a formula for null sales:
if isnull(Sum ({Orders.Order Amount}, {Customer.Customer Name})) then "No sales"
Andrew Baines
Chase International

 

[seema3]

join of a table to left outer join in Crystal reports.

 

[AndrewBaines]

Pardon? Andrew Baines
Chase International

 

[seema3]

how do i change the join between any 2 tables in crystal reports.

 

[AndrewBaines]

In the Visual Linking expert, click on the join, to the bottom right of the window is a list of the join types, just click the radio button corresponding to the join type.
You'll need to be using ODBC or a SQL database (Oracle, sql server, sybase etc). If you're using database files (access, dbase, paradox) you won't get the option - change to ODBC. Andrew Baines
Chase International

 

[kenhamady]

I don't think you selection formula will inlcude a California customer with only one order placed in 1995. Putting selection criteria on the outer table nullifies the outer join.

You can either
1) Have a subreport for each customer to get their orders, or
2) Use suppression instead of selection to hide orders in other years. 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

 

[AndrewBaines]

The selection should work provided you test for null first - it worked for me anyway. Andrew Baines
Chase International

 

[kenhamady]

Andrew,

That is the problem. A California customer with only one order placed in 1995 will not have a null date. He will have a single record with a valid date, but a date that doesn't qualify for the report. That will cause this customer to drop off the report completely. It will work for customers that have NO orders, but not those that have orders outside your target range. 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