Need to count values shared by multiple fields in Crystal XI

[RyanFish]

My problem is nearly identical to the one described here:
thread767-1554066

The main difference is that I don't have a SQL connection, only a recordset.

To recap, I have several fields in my recordset that can hold the same value. I need these fields to be treated separately in the details of the report, but I also need a count of how many times each value appears, like so:

-------The details display as so--------[tt]
Invoice | Code 1 | Code 2 | Code 3
127 | A | - | -
357 | B | A | B
768 | B | C | -[/tt]

---------In the group footer, I use a crosstab------
[tt] Total
A 2
B 3
C 1[/tt]


I can change the SQL query that supplies the recordset in the first place, but I can't seem to find a way to get the information I need into a form I can use. The code fields can contain an arbitrary number of distinct values, so I can't hardcode a running total.

 

[N22S]

Have you tried using variables? While they wouldn't work within a crosstab you can get the counts you need easily.

 

[chelseatech]

If it is only a A, B or C you are looking for then write a formula for each.

If {table.Code1}="A" or {table.code2}="A" or {table.Code3}="A" then 1 else 0

Similar for B and C.

Then do a sum of each formula.

Slightly more complicated if Code1, Code2 or Code3 can each have an "A" and you want to count all of them.
Local Numbervar CodeCount := 0
If {table.Code1}="A" then CodeCount :=CountCount + 1;
If {table.Code2}="A" then CodeCount :=CountCount + 1;
If {table.Code3}="A" then CodeCount :=CountCount + 1;
CodeCount

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm