SSRS Summing Distinct Values of Specific Group

Question

In SSRS, there are 2 groups and 1 detail row that I am trying to total the distinct value of each group.

group 1 = 1 school

group 2 = 5 students

detail = 4 classes taken by each of the 5 students

field 1 = student name

field 2 = number of previously taken courses (use the numbers 2,4,6,8,10)

field 3 = course names of current schedule (ELA, math, science, social studies)

My dataset has 20 rows representing each of the 5 students taking 4 classes each. On the report, there are no totals in the details row. In the student group row, when I pull just the value (not the sum) of the previously taken courses, it correctly displays in the column as 2,4,6,8,10 depending on the student, of course, because it is part of the grouped by expression.

Here's where we get to the problem. When group 1 is totaled in the previously taken courses column, the total comes up 4 times the value I expect. Presumably, the report is totaling all 20 entries instead of grouping them by distinct student value.

The report display should look like 1 row for school, 5 sub rows below school showing the students and the number of previously taken courses, and then finally 4 sub rows below each students with the course names listed.

I know the distinct count function will not help because the values need to be totaled not counted. I tried Sum(Fields!prevCount.Value,"GroupBystudent" but that didn't work either. I have tried referencing the ReportItems!Textbox.Value and I got an error that said it needed to be done in the header or footer.

Can this even be done? If so, could you please help me with the Expression? Many thanks.

Hannover Fist Hannover Fist · Accepted Answer · 2015-09-21T22:01:38

I had a similar issue but unfortunately, it can't be done with an expression.

I did it with some VB code to sum unique checks for me. It think it will work for you as it is now.

Here's the VB that goes in the SSRS code section (Report Properties->Code tab):

Private Checks As String

Function AddChecks(ByVal items As Object()) AS DECIMAL

Dim CheckCheck as String 
Dim Amount as Decimal

If items Is Nothing Then
Return Nothing
End If

For Each item As Object In items

    CheckCheck = Left(item, InStr(item, "|"))
    Amount = Val(Mid(item, InStr(item, "|") + 1, LEN(item)))

    If InStr(Checks, CheckCheck) = 0 THEN
        Checks = Checks & CheckCheck
        AddChecks = AddChecks + Amount 
    End If
Next

End Function

Paste this code in your reports code section.

For your expression, you would use

=CODE.AddChecks(LookUpSet(1, 1, Fields!StudentName.Value & "|" & Fields!NumberOfCourses.Value, "Finance"))

SSRS Summing Distinct Values of Specific Group

1 Answers