Sum (distinct count)

[gusc999]

Need some help with Summing (distinctCount)
Formula 1: Sum (DistinctCount ({LABEL.LBSTOP}, {TRKEXTF.RHRTE}))

Formula 2: Sum (DistinctCount ({LABEL.LBSTOP}))
Error message "This Field Cannot be summarized"

(PH) DPT Route Trn# Item# Pcs Stops

(GH1) [Route]

(D) DRY 101 12333 123213 3 5
(D) DRY 101 12334 123313 4 4
(D) DRY 101 12335 124513 1 5
(D) DRY 101 12336 123213 3 1

(GF1) 5 <- [DistintCount of label.lbstop]
(PH) DPT Route Trn# Item# Pcs Stops

(GH1) [Route]

(D) DRY 101 12333 123213 3 5
(D) DRY 101 12334 123313 4 4
(D) DRY 101 12335 124513 1 5
(D) DRY 101 12336 123213 3 1

(GF1) 5 <- [DistintCount of label.lbstop]
--10 stops---


Any help would be greatly appreciated.

 

[lbass]

This is confusing because you are showing only four rows of data, and you have not identified which field is the lbstop field. If it is the one called "Stops", the results would not be 5, but instead, 3. It is also unclear what value you are trying to achieve--the 10 you show (really 6, if you add both group distinctcounts).

-LB

 

[Madawc]

It's much easier to use Crystal's automatic totals - see FAQ767-6524 if you're not already familiar with them. If you want the same sort of totalling at several different levels, the best way is to do separate totals for each level.

If you have Crystal 11, you'll be able to copy existing totals and then modify them. It always helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[gusc999]

LB,

I aplogize... I did not add the extra line to equal five stops. However it should be five for both groups it was just a quick example. The end result is the total of 10 stops.

Madawc,

Thank you for the link going to review in a few and the version I run is 11.5

 

[lbass]

Well, that addresses one of the issues. If you are using distinctcount the results would be less than 5. Anyway, you can add whatever it is (count or distinctcount), by using a variable:

//{@Place this in the RHRTE group section and suppress it:
whileprintingrecords;
numbervar sumcnt := sumcnt + distinctcount({LABEL.LBSTOP}, {TRKEXTF.RHRTE});

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar sumcnt;

-LB

 

[gusc999]

LB,

I'm getting the following error message after I place the first code in the RHRTE group section.
"The Formula result must be a boolean" What do I need to change?

 

[lbass]

Create the formula in the field explorer->formula->new and then drag it into the group section. If it is not working, please show the actual formula content here.

-LB

 

[gusc999]

Here are the steps I took:

1) Created the new formula named it "Stops3"
"whileprintingrecords;
numbervar sumcnt := sumcnt + distinctcount({LABEL.LBSTOP}, {TRKEXTF.RHRTE});

2) Click on Reports > Section Expert > group Header #1 TRKEXTF.RHRTE -A > SUPPRESS (No Drill-Down) > "X2" icon > dragged the "@stops" field into it > then got the following error "The formula result must be a boolean"

please advice...

 

[lbass]

No. Place the formula directly in the group header section--NOT in the section expert.

-LB