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