Distinct Count question or maybe more

[Cort]

Hello all again.

Here's my report

Grouped on Service Rep (SR)

SR Contr open - New Contr - Closed Contr - Amt paid

bob 121 12 17 9,250
bill 97 14 19 7,000

total 218 26 46 16,250

That is what I want the report to look like. I can create the report easily in Access by using 3 base queries and 1 master query but am a bit stumped on how to recreate it in CR 8.5.

I use no selection criteria as I have to examine all fields. I use 2 tables to get this information. Table 1 contains all information except payments which is on table 2. The common field between the 2 tables is [contract number].

Contract open is true when [contract closed] is ""

New contract is true when [contract recd] is {within parameter request}

Contract closed is true when [contract closed] is {within parameter request}

Amt Paid is true when [transaction date] is {within parameter request}

Here is what I tried to do:

Open contract formula:

If [closed date] is "" then [contract number] else "0"

Variations on recd and close like above with [contract number] printed if the true value is returned "0" if not.

Amt paid formula:
if [transaction date] >= (Minimum({?Date Range for Report})) and [transaction date] <= (Maximum({?Date Range for Report}))
then [transaction amount] else 0

So, here's my problem:

I figured to get an accurate count of open, closed, and recieved I could do a distinct count formula and subtract 1 from the total to get an accurate count since it would post the [contract number] or a 0. Distinct Count -1 (to eliminate zeros) should work but it doesn't. On short term counts with few reps it works fine. However, for long term counts it finds something extra in there and the distinct count is off by 2 instead of 1. This error only appears in the total field and not in the rep field.

I had also thought to use 1 and 0 and just sum the columns but since a contract can have several transactions in the transaction table that leads to multimpl 1's being added for the same rep. i.e. if bob had 3 transactions on a closed contract using 1's and 0's would count that as 3 instead of just 1.


Sorry for the long post but I wanted to accuratly explain my problem. So, any help? Thanks.

 

[IdoMillet]

You can use the summing of 1/0 approach and avoid the multiple counts for same rep by creating a Running Total
with an Evaluate option of
&quot;On Change of Group&quot;: {Sales_Rep}

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[Cort]

Yeah, that's what I thought I could do also but it's not working. Perhaps I have an error in my running total:

Group 1 = Rep
Group 2 = Contract number

On the running total I sum based on change of record and reset on change of group 1. I get errors and here is why:

Detail of group 2:

Contract - Open - Closed - New - Amt paid
1234 1 0 0 50.00
1234 1 0 0 125.00
1234 1 0 0 75.00

total 3 0 0 250.00

What it is doing is even though contract 1234 is only 1 open contract it counts it as 3 because there have been 3 transactions for that contract. I've worked with the running totals some but seem to only be able to get the incorrect count of 3 for the individual contract or an incorrect count of 1 for the entire rep.

Thank You for your efforts.

 

[IdoMillet]

You said you &quot;sum based on change of record and reset on change of group 1&quot;

Instead, you should sum &quot;On Change of Group&quot;: Contract Number (and reset on change of group 1 as you do now).

Cheers,
- Ido

CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[Cort]

Thanks. Working great now. I need to get out of the habit of using sums and use running totals a lot more.

 

[Cort]

Update on this one. really, really odd.

Can a running total not add correctly.

In the closed contracts field one rep is off by 1 closed contract. If you reveal the details of group 2 and add up the 1's you end up with 15 but the total for group 1 shows 14. It is only for this rep. All the other reps are correct. The final total is off by 1 75 when if you count all the group 2 1's it is 76.

Also if you use selection criteria to select only those contracts that are closed (i.e. have a 1 as their value) then all the numbers are correct. 15 and 76. What would cause this to happen that the running total would miss one number?

Any Idea?

 

[IdoMillet]

Cort,

Are you using On 'Change of field' or On 'Change of Group' in the Evaluate?

Be sure to use the On Change of Group.

If this is not the issue, please provide the full dretail of how your Running Total is designed and what the data looks like (a simplified layout of a few records with only the key fields/formulas).

Cheers,
- Ido
CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[Cort]

Nevermind. I'm a moron.

Had one of the running totals doing a count instead of a sum. That was the problem.

Now, back to our regularly scheduled programming ...