How do you show records that DON'T match the Criteria?

[narmi68]

Hello all,

I'm trying to design a report that lists items we have not sold in 6 months, and yet have stock of. I have three tables at play here, two that link to give me the sales data, and a third which has the inventory amount I need. I can very easily do this report to show stock of what has sold in the last 6 months, but how would I do the reverse?

Thanks in advance!

 

[pmax9999]

Try selecting the data based on those products where the latest (Maximum) sale date is prior to 6 months ago.

To do this, group on Product and use the following in the Group Selection formula:

Maximum({Table.SALES_DATE},{Table.Product}) < DaDateAdd('m', -3, CurrentDate)

Hope this helps.

Cheers
Pete

 

[Madawc]

Do it by stages. First link your tables so that everything on the inventory will be shown, regardless of sales. This can be done by starting at the inventory and doing left-outer links.

Note that tests on the tables that may be missing must start with a test for null, otherwise the formula will fail.

Once you have this, group by inventory item. Do some sort of count or sum that will identify the type you want. If you're not already familiar with Crystal's automated totals, see FAQ767-6524. And note that it's easy to get null results. One solution is to have a formula field that tests the total for being null and sets zero if it is, while using the total value when it is not null.

Having got this far, you should be able to use Group Selection to get the details you want.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP