Count Status only if No Other Status 2

[FireGeek21]

I need to find out the number of quotes we have - where the quote doesn't go further - into an order, ship, invoice or paid status.

Sample of data:

Grp: Sales Person 1592648
CustID Status Date
321 Quote 01/02/2011
321 Order 01/03/2011
321 Ship 01/06/2011

654 Quote 12/27/2010

987 Quote 11/01/2010
987 Order 11/10/2010
987 Ship 11/12/2010
987 Invoice 11/16/2010
987 Paid 12/01/2010

In the above sample, although I have 3 quotes, I need to know how many CustID's never went beyond a quote. The data is grouped by Sales Person and then by CustID. In the footer of the Sales Person I am hoping to see "Quotes Only = 1"

Any ideas?

Thanks!


FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[briangriffin]

I think you'll need five formulas:

Group Header for Employee:

whileprintingrecords;
numbervar v_total := 0;

Group Header for Customer:

whileprintingrecords;
numbervar v_quotes := 0;
numbervar v_status := 0;

Detail Section:

whileprintingrecords;
numbervar v_quotes;
numbervar v_status;

if {Sheet1_.Status} = "Quote" 
then v_quotes := v_quotes + 1;
v_status := v_status + 1;

Group Footer for Customer:

whileprintingrecords;
numbervar v_quotes;
numbervar v_status;
numbervar v_total;

if v_quotes = v_status then v_total := v_total + 1;

Group Footer for Employee:

whileprintingrecords;
numbervar v_total;
"Quotes Only: " + totext(v_total,"#",0)

We could probably streamline this a bit, but it works and it's 5:00 on a Friday

 

[lbass]

Another approach would be to add an inner group on Customer ID and then insert a running total that does a distinctcount of customer ID, evaluates using a formula:

distinctcount({table.status},{table.customerID}) = 1 and
{table.status} = "Quote"

Resets on change of group: salesperson.

Place this in the salesperson group footer.

Either approach should work.

-LB

 

[FireGeek21]

Thank you Brian and LBass for the help.

Brian, although long, your approach is perfect. We actually need to look at a combination of order statuses and the Detail Section allows me to expand on this such as IF {Sheet1_.Status} in ["Quote", Waiting"].

Again, THANK YOU BOTH!!!


FireGeek
(currently using Crystal Reports XI with Lawson 8.03)