# Averaging 2 values based on an ID in SSRS

I am hoping I might be able to get some help with a problem I am having. I am currently trying to build a report in SSRS to replace an excel spreadsheet we use for data analysis so the report can be dynamic. I have set the report to be a matrix and pull through all the reported grades for a student. I also have a column that shows their Most Likely Grade (MLG). My problem is that students have more than one teacher for each subject, so what I need to do is average out the two attainment grades and then compare it to the average MLG. The MLG has a transposed value stored in the database that I use in calculation, so for example an A actually = 1.

In essence, in the example pictured - History MLG = A,A = (1+1)/2 = MLG of 1 Attainment score for Spring is (1+2)/2 = 1.5, so the value returned to the column would be 1.5.

My logic would be to look for the subject ID, if the subject ID is the same as another in the column,then add the 2 attainment scores, average them and return the average value into a new column.

I am very new to SSRS so I am not sure if this is possible and if it is how to get this to work.

This is the SQL query on the dataset

``````WITH Pupils AS
(SELECT DISTINCT txtSchoolID
FROM TblReportsStorePupilArchive
WHERE (TblReportsStorePupilArchive.intReportCycle = @intReportCycle)
AND (TblReportsStorePupilArchive.intNCYear IN
(SELECT ID FROM dbo.SplitCSV(@intNCYear) AS SplitCSV_1) OR @intNCYear IS NULL)
AND (TblReportsStorePupilArchive.txtForm IN
(SELECT ID FROM dbo.SplitCSV(@txtForm) AS SplitCSV_1) OR @txtForm IS NULL)
AND (TblReportsStorePupilArchive.txtSchoolID IN
(SELECT ID FROM dbo.SplitCSV(@txtSchoolID) AS SplitCSV_1) OR @txtSchoolID IS NULL)
AND (TblReportsStorePupilArchive.txtBoardingHouse IN
(SELECT ID  FROM dbo.SplitCSV(@txtHouse) AS SplitCSV_1) OR @txtHouse IS NULL
OR
(SELECT ID FROM dbo.SplitCSV(@txtHouse) AS SplitCSV_1) OR @txtHouse IS NULL)
),

Cycles AS
(SELECT TblReportsManagementCycleID FROM  TblReportsManagementCycle AS AcademicYearCycles
(SELECT intReportYear FROM TblReportsManagementCycle AS CurrentCycle
WHERE CurrentCycle.TblReportsManagementCycleID = @intReportCycle)
)

INNER JOIN Pupils ON AcademicReports.txtSchoolID = Pupils.txtSchoolID
INNER JOIN Cycles ON AcademicReports.intReportCycleID = Cycles.TblReportsManagementCycleID
``````

• I am using SSRS 2015 Mar 28, 2018 at 8:05
• Given this is SSRS 2015 the easiest solution would probably be to solve it in the datasource query. Can you post your dataset definition and table structures so we can see if you can do it in the query? Mar 28, 2018 at 8:39
• Sure. Here is the dataset query: Mar 28, 2018 at 9:11
• That doesn't show up, but instead of adding it in a comment it's better to edit your question Mar 28, 2018 at 9:12
• Oh I misread your first comment as ssrs 2005, There is no 2015 so which version are you using? Mar 28, 2018 at 9:26

``````SELECT        txtSchoolID, intNCYear, intReportCycleID, intSubjectID, txtCurrentSubjectReportName, AVG(CAST(intGradeTransposeValue AS decimal(5, 2))) AS avg_attainment, txtReportCycleName, txtGradingName, txtGrade, txtReportTermName, intReportCycleTerm, txtReportCycleShortName, dtReportCyclePrintDate, intGradeTransposeValue