Cross Tab - combine and count data from two fields

[sdzlkds]

Hi

I'm using Crystal 10

We have two fields on our database

{CAT1}
{CAT1_EXTRA}

Data entries would be as follows

Record Ref {CAT1} {CAT1_Extra}
1234 Billing Service Delay
1235 Payment made Billing
1236 Service Delay [no data entered*]

(*{CAT1_Extra} is not a mandatory field so may be null value)

We want to count these two tables combined, so the result we want to see is

Category Category Count
Billing 2
Payment made 1
Service Delay 2

I am not sure how I can combine the data from the two fields together and then summarise it

I'd be grateful for any advice

Thanks

 

[IanWaterman]

Do you know the full list of categories and are they static?

If yes you can create a running total for each category.

eg

#RT Billing
In the evalate formula

if not(isnull({CAT1_Extra})) and {CAT1_Extra} = 'Billing' then true
else
if {CAT1} = 'Billing then true else false

Repeat for other categories

Ian

 

[lbass]

How many categories are there? You could use conditional formulas like:

if "Service Delay" in [{cat1},{cat1_extra}] then 1

You would create one formula per category. Then insert sums (not counts) on these at the grand total level and add text boxes to label the results.

If there are many categories, you could use a command as your datasource where you merge the two fields in a union all statement like this:

select table.`cat1` as Cat
from table
union all
select table.`cat1_ref`
from table

Syntax/punctuation depends upon your datasource. Then you could insert a group on {command.Cat} and insert counts at the group level.

-LB