Excluding repeated values in a sum

The scenario is as follows:

I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.

SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z

The problem is that each contract can have multiple actions associated with it...
There ideal output would be:

Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z
Total: $80

Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..

Any help would be greatly appreciated.

Thanks,
Neil

Could you sum the totals and divide by the rowcount? This would give an accurate total for the repeated values within the group, but I haven't tested it to know if you can accurately retrieve a grand total.|||Otherwise known as the AVG function. Guess I'm not thinking to well this morning. With that being the case, there would be an issue with the Grand Total.|||Hi Simone,

Yeah the grand total would not work for that, otherwise it would work I guess.. any other ideas?

Thanks,
Neil
|||

Hello Neil,

One thing you could try is to create a public variable and a function to increment your variable (via your code window), then in each detail row, increment if the previous record's contract number is different from the current number.

In each detail row, call your function to increment the value. =Code.IncrementValue(IIf(Previous(Fields!ContractNum.Value) <> Fields!ContractNum.Value, Fields!ContractNum.Value, 0))

In your total textbox, display your variable. =Code.ContractTotal

I haven't tried this, but hopefully it will get you started.

Hope this helps.

Jarret