Get sum of distinct values in a group

[lsauser]

Hi all,

This one is really doing my head in.

The following image is from the group details section of my report and at the top of my report in the report header I have a summary subreport that has totals for each technician.

Getting the summaries for service hours/travel hours/hours worked is no issue because each one of the values in the columns are meant to be SUM, which i can just grab from the group footer.

HOWEVER, the hours charged column doesnt need to be a sum, I only need the distinct value from each service call which in the below case is just 2 (not 6).

Unfortunately i dont know how to get this value because normally you would just move the details summary from the group footer into the group header like the below. If I do this it gives me a SUM of all the values from each service call associated with that customer though.

So if i have the following;

Service call Tech Date Hrs Charged

11111 Bob 1/2/20 2

11111 Bob 2/2/20 2

11111 Bob 3/2/20 2

2222 Bob 5/3/20 5

3333 Bob 8/4/20 7

It would give me a SUM of 18, whereas i need it to give me a SUM of 14

Apologies if i have explained this poorly, my brain has been fried trying to get it to work today.

Regards,

Nick

EDIT:

I think i might be on the right track but i could be wrong. Below is the code im using for my HrsCharged formula;

IF(ISNULL({Command.Subscription Hours})) THEN
    IF(NOT ISNULL({Command.Warranty Hrs})) THEN
    0.0
        ELSE
            {Command.Hrs Charged} 
                ELSE
                      If Not isNull({Command.Subscription Hours}) Then
        If isNumeric({Command.Subscription Hours}) Then
            ToNumber({Command.Subscription Hours})
    Else 0.00

With it like this I can do a summary of the HrsCharged formula, however if i modify the code to the below;

IF({Command.U_ObjEntry}=previous({Command.U_ObjEntry})) THEN
0.00
ELSE
IF(ISNULL({Command.Subscription Hours})) THEN
    IF(NOT ISNULL({Command.Warranty Hrs})) THEN
    0.0
        ELSE
            {Command.Hrs Charged} 
                ELSE
                      If Not isNull({Command.Subscription Hours}) Then
        If isNumeric({Command.Subscription Hours}) Then
            ToNumber({Command.Subscription Hours})
    Else 0.00

Then it gets rid of the duplicates and zeros them out, but also wont allow me to do a summary and also gets rid of the first entry (i presume because there is no previous to compare to)

[DellSC · Accepted solution]

Since you're showing this information in a footer, if you use your first formula (without the call to Previous()), which I'll call {@HrsCharged}, you could use a Running Total to get this sum:

Field to Summarize: {@HrsCharged}
Type of Summary: Sum
Evaluate: On Change of Field: {Command.U_ObjEntry}
Reset: On Change of Group: <technician ID group>

-Dell

[lsauser · Accepted solution]

Thanks for your reply dell.stinnett-christy

Worked like a charm!

Regards,

Nick