Formula that makes some results anonymous 1

[sdzlkds]

Hi

I run the following report

Top 4 count of cases

Company ID Count of cases
Acme Ltd 100
ABC Ltd 80
DEF Ltd 40
Blah Ltd 25
Other 300

What I want to do is make some of the results anonymous - so

Company ID Count of cases
Acme Ltd 100
Company A 80
Company B 40
Company C 25
Other 300

So what I'm after is a formula that is along the lines of

Company ID is not "Acme Ltd", and where the count of case is Top N = 1 then "Company A" else where the count of case is Top N = 2 then "Company B" else where the count of case is Top N = 3 then "Company C" else count all remaining cases

I'd be grateful for some help to write this formula

 

[Madawc]

Can you see the 'Top N' result from a formula? I'd have thought it was calculated after everything else and therefore would not be usable the way you want to use it.

You could do summary counts for groups and make the company anonymous when they were below a specified value.

It always helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. My own view is based on Crystal 10.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IanWaterman]

Formula like this placed in companyid group header should work

@Company

whileprintingrecords;
global numbervar cocount;
global stringvar coname;

If CompanyID ="Acme Ltd" then CompanyID;

If cocount = 1 then coname:='Company A' else
If cocount = 2 then coname:='Company B' else
If cocount = 3 then coname:='Company ' else
If CompanyID ="Acme Ltd" then coname:=CompanyID
else 'Other';

cocount:= cocount+1;

coname;

You may have to place a reset formula in report header

@reset

whileprintingrecords;
global numbervar cocount:=0;

Ian

 

[IanWaterman]

Just in case not clear, you do not group on this formula, keep grouping as CompanyID just remove groupname field from header and replace with my formula.

Ian

 

[sdzlkds]

Thanks Madawc, I'm also on Version 10

Thanks Ian - I can't quite get this formula to do what I'm after. Following your suggestion I get something that looks like this

Company ID @Company Count of cases
ABC Ltd 80
DEF Ltd Company A 40
Blah Ltd Company B 25
AAAA LTd Company C 15
ABCABC Ltd Company C 12
XXXX Ltd Company C 11
XYZ Ltd Company C 10

(Company ID just included to illustrate what is happening, would not be visible in finished report)

So the company with most complaints appears as a blank in the formula results

Company C repeats for each company outside the top 3, and the "else Other" does not appear in the formula result

If you are able to offer any further advice it would be appreciated

 

[Madawc]

Maybe adapt IanWaterman's formula

If cocount =  then coname:=CompanyID else

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IanWaterman]

Sorry in your example you only had a top 4 with all the others showing as Others.

If you want to do a Top 7 then formula @company would need to change accordingly.

Add the reset formula to report header and change

@Company

whileprintingrecords;
global numbervar cocount;
global stringvar coname;

if cocount = 0 then coname:=CompanyID else
If cocount = 1 then coname:='Company A' else
If cocount = 2 then coname:='Company B' else
If cocount = 3 then coname:='Company C' else
else 'Other';

cocount:= cocount+1;

coname;

Ian