CODEWITHSUNDEEP
sqlsql-serverreporting-servicesreportreportbuilder3.0
grantmcconnaughey
grantmcconnaughey

Reputation: 10719

Add an overall average row to Report Builder report

I need help creating an overall average in Report Builder 3.0. I have a query that returns data in the following format:

Major       Num Of Students     Max GPA     Min GPA     Avg GPA
----------  ------------------  ----------  ----------  -----------
Accounting  89                  4.0         2.3         3.68
Business    107                 4.0         2.13        3.23
CIS         85                  3.98        2.53        3.75

I added a total row in Report Builder that shows the sum number of students, overall Max GPA, and overall Min GPA. But I can't simply run the Avg function on the Avg GPA column, as it needs to take into account the number of students for an overall average. I believe that I need to do something like the following (in pseudocode):

foreach ( row in rows ) {
    totalGpa      += row.numOfStudents * row.avgGpa
    totalStudents += row.numOfStudents
}

overallAvgGpa = totalGpa / totalStudents

Does anyone know how I could do this in my report?

Upvotes: 1

Views: 2101

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

In your case you need weighted average here, something like this in the Total row:

=Sum(Fields!numOfStudents.Value * Fields!avgGpa.Value)
  / Sum(Fields!numOfStudents.Value)

You can see I'm creating the expression Fields!numOfStudents.Value * Fields!avgGpa.Value for each row, summing that, then dividing by the total students.

In your case this would give (89 * 3.68 + 107 * 3.23 + 85 * 3.75) / (89 + 107 + 85), i.e. 3.53, which seems about correct.

Upvotes: 1

Related Questions