[b]Distinct Conditional Count with R# Total gives Wrong Result [/b]

[Nassy]

Hi

I have been designing a Property Sales report on which I intend to display the following by Branch:

[ul square]
[li]Number of Viewings[/li]
[highlight #FF99FF][li]Number of Accompanied Viewings[/li][/highlight]
[highlight #FF99FF][li]Number of Unaccompanied Viewings[/li]
[/highlight][li]Number of Offers[/li]
[/ul]

The report displays the Number of Viewings and the Number of Offers correctly but I don't seem to be having much luck getting Crystal to do a conditional count to show the Number of Accompanied and Unaccompanied Viewings.

The data is grouped by Branch.

What I am hoping to achieve:

Two of the fields in the table ViewingDetails which I am reporting on are:

[ul square]
[li]Viewing ID[/li]
[li]Viewing Type[/li]
[/ul]

For each Branch I want to do two Distinct Counts on Viewing ID, one where the Viewing Type = 'Accompanied' and one where the Viewing Type = 'UnAccompanied'.

How I have tried to achieve this:
To resolve this I created two running total fields RTotal1 and RTotal2. The RTotal1 field is set with the following options:

Summary
Summary Field:{ViewingDetail.ViewingID}
Summary Type :Distinct Count
Evaluate
Formula :ViewingDetail.ViewingType='Accompanied'
Reset
Group :ViewingDetail.BranchName

RTotal2 was created in exactly the same except for the fact that the formula is set to 'Unaccompanied'

These running totals however display completely the wrong figures - they don't count all the items in each group for one thing - no count greater than 1 - and to be honest I don't get Crystal's logic. I see no obvious pattern. I have also tried all the running total options but the values don't seem to change.

I am really stuck on this as I have done something that I believe should work but doesn't. I use Crystal Advanced 9 if that helps.


Nassy

 

[Nassy]

I have now found that if I place the RTotals in the Report Footer it works. I don't get this when I am resetting the Running Total to 0 on each Group but I guess this is how it works. Any explanation of why this is would be great.

Thanks.

 

[kenhamady]

Running total fields do their work as the report is printing, so they are only complete at the footer level (group footer or report footer). If you are only getting 1 for each you may have them in the header. Or you may be using the wrong reset.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[Nassy]

Thanks for the reply. I guess this is a fundamental difference then between using a normal summary field and a running total. I don't use running totals that often you see.

Thanks again

Nassy