Sum of distinct in SSRS

Question

I have to get distinct rows sum in SSRS expression.

**StudentId  TestScoreMath   CurriculumEnrolled      EnrollStatus**

  100              200            Nursing             Enrolled

  100              200            Physical              Enrolled

  200              100             Tech                Enrolled

Total Student TestScoreMath should be 300 not 400. I tried using the expression

=Sum(Fields!TestScoreMath .Value, "table1_Group3")

How can I SUM the value of TestScore of distinct students? I need this only in SSRS.

You should include your version in the question as well as the tags. The answers for this are different from version to version. Also, what have you tried? How far are you? What does your report look like right now? — Jamie F
For studentID 100, why is the total TestScoreMath not 400? Can you show an example dataset and what grouping you are trying to sum by? Agreed, query level is definitely the right place to solve this sort of issue. — Davos

Jamie F Jamie F · Accepted Answer · 2012-11-05T23:47:14

In SSRS 2005, this is not too easy. I would add embedded code to keep a running total. You can have the custom code to check to see if the current student ID matches the last. If so, then don't add another test score to the total. Or you could group by Student ID and call your custom code only in the header or footer of that group.

This sample shows totaling all rows: you'll need to add to this to track the studentID.

Sum of distinct in SSRS

1 Answers