Supress Question 1

[Digitelpersona]

Hi,

If this has already been answered I apologize, couldn't find an answer while doing a search.

I'm trying to show customers that have purchased a specific item from us and never purchased anything else. but the report keeps showing ALL the customers that purchased the item even if they have purchased other items. I just need it to show the customer that purchased the one item and nothing else!

Here is an example:
CustName Item
Ted WPPX12
Ted WLRB51
Joe WPPX12
Joan WPPX12
Joan WLRB51

How can I get Crystal reports to show me just JOE?

Right now it keeps returning this:
Ted WPPX12
Joe WPPX12
Joan WPPX12

and what I need is this:
Joe WPPX12

Thanks for all your help!

 

[midearth]

Group your data by CustName then insert a summary performing a count of Item. Conditionally suppress the data where the count of group items is > 1 such as:

Count ({dbtable.ITEM}, {dbtable.CustName})>1

 

[Digitelpersona]

Hi,

The suggestion above worked somewhat, however in some cases the data repeats itself and is counted as 2 but it's the same exact information.

Any other ideas?

Thanks again.

 

[lbass]

I would use group selection. Go to report->selection formula->GROUP and enter:

distinctcount({table.item},{table.custname}) = 1 and
{table.item} = "WPPX12"

This assumes that you have inserted a group on {table.custname} and that you are not limiting the items in your record selection formula.

Be sure to use running totals, if you then want to do some calculations across the resulting groups, since inserted summaries will include the non-group selected records.

-LB

 

[Digitelpersona]

LB, thanks that seems to work.

Now I'm running into another issue with a second report I'm working on. In this case I'm looking for customers who purchased three or more specific items and nothing else. Again I seem to be running into the same problem where Crystal reports will bring in anyone who matches any one of these items.

For example:
Ted WPPX12
Ted WLRB51
Joe WPPX12
Joe WHGB10
Joan WPPX12
Joan WLRB51

Again I want to see Joe because he purchased the two (this could be more) items I'm looking for but not Ted or Joan.

Is it possible to tell Crystal that if a customer has two or more specific items, show them, but don't show anyone else that may have just one or more of other items?

Thanks again.

 

[lbass]

I'm a little confused. Are you saying you want to select a person if they have at least two out of a set n of specific items, but no other items, where the n could vary?

-LB

 

[Digitelpersona]

Yes! that seems to be a better way to explain it.

 

[lbass]

First create a formula {@null} by naming the formula, opening the formula editor and then saving the empty formula.

Then create this formula {@selitemsonly}:

if {table.item} = {?selecteditems} then {table.item} else {@null}

Then create a second formula {@others}:

if {table.item} <> {?selecteditems} then 1

Then go to report->selection formula->GROUP and enter:

distinctcount({@selitemsonly},{table.custname}) >= 2 and
sum({@others},{table.custname}) = 0

By using {@null} the distinctcount will not pick up an extra count for those values that don't meet the condition. You can't safely subtract 1 to allow for items not meeting a condition, since all items in a particular group could meet the condition. Using {@null} eliminates that issue.

I used a parameter above for the selected items, but you could show an array of values instead, if you don't plan to use a parameter.

-LB

 

[Digitelpersona]

Hi LB,

I've started to apply the instructions you gave to my report, but our items are stored as numbers and I keep getting an error when creating the second formula:

if {table.item} = {?selecteditems} then {table.item} else {@null}

it tells me "A Number is Required Here

 

[lbass]

Change the formula to:

if {table.item} = {?selecteditems} then {table.item} else tonumber({@null})

-LB