Is it possible to sum distinct records ie instead of counting them?
Posted on 2008-06-26
I have a report which has records like this:
ID Number Gross Earnings Salary Sacrifice Salary Sacrifice Type
001234 100,000 200.00 Car
001234 100,000 1000.00 Unit
001234 100,000 500.00 Holiday
I need to Group by ID Number and sum the Gross Earnings (ie $100,000 not $300,000) and the Salary Sacrifice ($1700.00) to get a total of $101,700. I have managed to do this by inserting a formula of Maximum (Gross Earnings) + Sum (Salary Sacrifice).
The problem I have is that I now need to add in another group (Supervisor), which means that I can't use the maximum function anymore as it will only produce one record for the group.
Any ideas on how to approach this one?