Calculate Value Based On Other Values For Use In SRS Report

I am trying to create a report in SRS that will calculate a Composite Score based on the Sum of other scores and then that Sum is divided by 12.


Here is a copy of the Query I have created to calculate the numbers I want to use, but when I create the expression in SRS the calculations don't come out correctly. The expression that I use in SRS is at the bottom of the Script.

Thanks in Advance


--6 Trait_03052008.sql

--Pull test scores for 6 Trait out of Campus for SRS reports.

SELECT s.lastname+','+' '+s.firstname AS Student, e.grade, sm.lastname+','+' '+sm.firstname AS Teacher, sc.name AS School,

t.name AS Test, t.code, ts.rawscore,

CASE WHEN t.name = '1 Ideas' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 3 AS Numeric)END AS IComp,

CASE WHEN t.name = '2 Organization' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 3 AS Numeric) END AS OComp,

CASE WHEN t.name = '3 Voice' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 2 AS Numeric)END AS VComp,

CASE WHEN t.name = '4 Word Choice' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 2 AS Numeric)END AS WComp,

CASE WHEN t.name = '5 Sentence Fluency' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore AS Numeric) END AS SComp,

CASE WHEN t.name = '6 Conventions' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore AS Numeric)END AS CComp

FROM testscore ts

JOIN student s ON s.personid = ts.personid

JOIN roster r ON r.personid = s.personid

JOIN test t ON t.testid = ts.testid

JOIN calendar ca ON ca.calendarid = s.calendarid

JOIN enrollment e ON e.enrollmentid = s.enrollmentid and e.calendarid = ca.calendarid

JOIN section se ON se.sectionid = r.sectionid

JOIN course c ON c.courseid = se.courseid AND c.calendarid = ca.calendarid

JOIN school sc ON sc.schoolid = s.schoolid

JOIN staffmember sm ON sm.personid = se.teacherpersonid AND sm.schoolid = ca.schoolid

WHERE t.code LIKE '6%' AND c.calendarid =50 AND c.homeroom = 1

GROUP BY s.lastName,s.firstName,e.grade,sm.lastName,sm.firstName,sc.name,t.name,t.code,ts.rawScore

ORDER BY [Student], t.code, t.name DESC


Expression being used in SRS to calculate the Composite Score.

=SUM(Fields!IComp.Value)+(Fields!OComp.Value)+(Fields!VComp.Value)+(Fields!WComp.Value)+(Fields!SComp.Value)+(Fields!CComp.Value)/12