SSRS 2012: Sum of Distinct Values

Question

All the similar questions I could find about this issue are either for SSRS 2005 (which had limits and couldn't be solved before), are unanswered, or the answers link to an external site rather than having an actual answer. Either way, here is my issue:

The report has a row group where it generates a different number for each row. I am trying to add up all these numbers and show them in the grand total row in the bottom. Therefore, I am trying to sum up all the fields using =Sum(Fields!contract_amt.Value) outside the group. However, when I use that expression for the grand total row, it sums out all the rows in the dataset and generates a very large number due to many rows in the dataset being duplicated.

How do I make it so the expression only adds up the distinct values from the field? (ex. only the distinct rows)

Answer 1

First you need to flag the rows that you want to sum. You can do this either with a calculated field on the dataset or a field in your query. I like to use the ROW_NUMBER function in SQL for this.

Then you can do a conditional aggregate like this:

=Sum(IIf(Fields!DISTINCT_FLAG.Value = 1, Fields!contract_amt.Value, Nothing))