No activity report

[pmgatwork]

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

 

[IdoMillet]

Data source and connection method? Crystal version?

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[pmgatwork]

Sory Ido
Data source ODBC either sql or xbase
Crystal version 9

 

[IdoMillet]

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.

http://www.MilletSoftware.com

 

[pmgatwork]

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

 

[IdoMillet]

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.

http://www.MilletSoftware.com

 

[pmgatwork]

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

 

[IdoMillet]

There is a known issue at:

http://support.businessobjects.com/library/kbase/articles/c2017022.asp

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:

http://support.businessobjects.com/library/kbase/articles/c2017022.asp

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[pmgatwork]

Thanks Ido
The patch fixed the parameneters

Peter

 

[IdoMillet]

Excellent...

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com