Sql-server – Averaging 2 values based on an ID in SSRS

sql serverssrst-sql

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
TblReportsStorePupilArchive.txtAcademicHouse IN
(SELECT ID FROM dbo.SplitCSV(@txtHouse) AS SplitCSV_1) OR @txtHouse IS NULL)
),

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

SELECT AcademicReports.*

FROM VwReportsManagementAcademicReports AS AcademicReports

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

enter image description here

Best Answer

I managed to do this in the end by altering the SQL query on the Dataset

SELECT        txtSchoolID, intNCYear, intReportCycleID, intSubjectID, txtCurrentSubjectReportName, AVG(CAST(intGradeTransposeValue AS decimal(5, 2))) AS avg_attainment, txtReportCycleName, txtGradingName, txtGrade, txtReportTermName, intReportCycleTerm, txtReportCycleShortName, dtReportCyclePrintDate, intGradeTransposeValue
FROM            VwReportsManagementAcademicReports
WHERE        (intReportCycleAcademicYear = 2017) 
AND (intNCYear > 6) 
AND (intGradeID = 1)
GROUP BY intSubjectID, txtCurrentSubjectReportName, txtSchoolID, intNCYear, intReportCycleID, txtReportCycleName, txtGradingName, txtGrade, txtReportTermName, intReportCycleTerm, txtReportCycleShortName, dtReportCyclePrintDate, intGradeTransposeValue
ORDER BY intReportCycleID, intSubjectID
Related Question