Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

No activity report

Status
Not open for further replies.

pmgatwork

Programmer
Joined
May 7, 2004
Messages
83
Location
GB
Hi all

I am trying to cerate a report which will show me customer that have had no activity( transactions) in a given period

I have linked the customer and transaction tables as a left outer join and this appears to work.

However if I then restrict the transactions to only contain transactions for a date range the report does not work.
Any ideas

Peter
 
Data source and connection method? Crystal version?

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
Sory Ido
Data source ODBC either sql or xbase
Crystal version 9
 
If you can create a command as a data source, you can use a NOT IN statement such as:
SELECT... FROM... WHERE customer.customer_id NOT IN (Select customer.customer_id FROM... WHERE your date range condition... )

A simpler option, provided your back end supports the ON condition, is to take your SQL query in the current (outer join) report and move the date criterion to the ON clause from the WHERE clause.

Another alternative is doing the same as a VIEW or SP on the back end.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
Ido
So far I have this

SELECT `customer`.`acnt_nbr`, `orders`.`or_ticknbr`, `orders`.`or_fromdat`
FROM `customer` `customer` LEFT OUTER JOIN `orders` `orders` ON `customer`.`acnt_nbr`=`orders`.`or_custacc`
WHERE `customer`.`acnt_nbr` NOT IN (Select `orders`.`or_custacc` FROM `orders` )

If I put a filter of orders.or_fromdat = {?dates} (my parameter) then I get nothing. How do I get this parameter into the command?

Peter
 
Create From_Date and To_Date parameters in the command.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
Ido Must be doing something wrong. I cannot seem to create any oparameters. If I select create then key the details the parameter appears in the parameter list but I cannot use it and If I click OK it dissappears

Peter
 
There is a known issue at:
but I'm not sure if applies because I don't know what you are doing exactly.

In general, are you following the procedure described at:

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
 
Thanks Ido
The patch fixed the parameneters

Peter
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top