Icky Manual Crosstab Report

[kaymc]

I'm writing a report where I need to be able to count the number of unique instances of two fields broken down by month across a year. I have my data coming from a SQL Server 2000 view into a Crystal 8 report.

My report will hopefully look something like this...

           Jan            Feb            Mar
Salesman   #Sales #Custs  #Sales #Custs  #Sales #Cust ...etc
-------------------------------------------------------
John       5      1       7      2       4      2
Mary       9      3       6      2       7      4

So that you can see in Feb, John made 7 sales (7 unique Sales Order numbers), but they all went to just two customers (two unique CustomerIDs across his 7 sales in Feb).

Now the counting the number of sales is a doddle, but how do I keep track of UNIQUE CUSTOMER IDs? For the record, the number of customers is likely to be quite high.

If this all sounds too easy for words, I should point out that I'm a native English Speaker working on a German version of Crystal, which makes life very complicated.

Thanks in advance.

 

[IanWaterman]

Kay

You should be able to use the running total formula, one for each month condition, it can then be set to count for each new customer. You can also use an RT to count orders.

Hope this helps.

Ian

 

[lbass]

Insert a formula like the following at the detail level:

if month({sales.date}) = 1 then {table.custID}

Then insert a summary on it at the group level (salesperson), using distinct count. Then suppress the details.

-LB

 

[kaymc]

Thanks guys. Very helpful advice.