1. Select File > Add Data Tables...
Select source: Information Link
Source location: /OPOLYAKOV/Data Sources/Gradebook Exports - Grade Level Personalized/CYGradebook_PS_ES - Grade Level Personalized - No Count False (2a2c4bb2-b582-446c-a6e9-455b338f0fb1)
Add Relations
Grades_Current_Year.Other ID = Gradebook View Export.Student ID
Grades_Current_Year.Grade Course = Gradebook View Export.CY Course
Grades_Current_Year.Grade Term = Gradebook View Export.MP
Gradebook View Export.Student ID = Grades_Prior_Year.Other ID
2. Select Insert > Rows...
Select source: Information Link
Source location: /OPOLYAKOV/Data Sources/Gradebook Exports - Grade Level Personalized/CYGradebook_MS_HS - Grade Level Personalized - No Count False (d5ac8495-5d98-4ba7-88e1-e4c789bb555c)
Match columns:
Student ID => Student ID
Student Name => Student Name
Grade => Grade
MP => MP
Department => Department
CY Course => CY Course
Section => Section
Period => Period
CY Description => CY Description
Teacher Name => Teacher Name
Sch Yr => Sch YR
Date Due => Date Due
Assignment => Assignment
Detailed Description => Detailed Description
Assignment Type => Assignment Type
Assignment Grade => Asgn Grade
Assignment Weight => Asgn Weight
Benchmark => Benchmark
Benchmark Grade => Bnch Grade
Special Code => Special Code
Special Code Description => Special Code Description
Comment => Comment
Entity => Entity
Entity Name => Entity Name
Show Score As => Show Score As
No Count => No Count
Missing => Missing
User Name => User Name
3. Insert > Binned Column...
Name: Binned Benchmark Grade
Expression: BinBySpecificLimits([Benchmark Grade],0,1.99,2.49,2.99,3.49)
4. Insert > Calculated Column...
Name: Benchmark Status
Expression: case when [Binned Benchmark Grade]="x ≤ 0.00" then "Missing Assignment (Grade = 0)"
when [Binned Benchmark Grade]="0.00 < x ≤ 1.99" then "Below Basic"
when [Binned Benchmark Grade]="1.99 < x ≤ 2.49" then "Low Basic"
when [Binned Benchmark Grade]="2.49 < x ≤ 2.99" then "High Basic"
when [Binned Benchmark Grade]="2.99 < x ≤ 3.49" then "Proficient"
when [Binned Benchmark Grade]="3.49 < x" then "Advanced"
when [Benchmark Grade] Is Null then "Grade = *"
end
5. Insert > Binned Column...
Name: Binned Assignment Grade
Expression: BinBySpecificLimits([Assignment Grade],0,1.99,2.49,2.99,3.49)
6. Insert > Calculated Column...
Name: Assignment Status
Expression: case when [Binned Assignment Grade]="x ≤ 0.00" then "Missing Assignment (Grade = 0)"
when [Binned Assignment Grade]="0.00 < x ≤ 1.99" then "Below Basic"
when [Binned Assignment Grade]="1.99 < x ≤ 2.49" then "Low Basic"
when [Binned Assignment Grade]="2.49 < x ≤ 2.99" then "High Basic"
when [Binned Assignment Grade]="2.99 < x ≤ 3.49" then "Proficient"
when [Binned Assignment Grade]="3.49 < x" then "Advanced"
when [Assignment Grade] Is Null then "Grade = *"
end
7. Insert > Calculated Column...
Name: Avg Grade of Classmates on Assignment
Expression: Avg([Assignment Grade]) Over ([Teacher Name],[CY Description],[Section],[Assignment],[Assignment Type])
8. Insert > Calculated Column...
Name: Avg Grade of Classmates on Benchmark
Expression: Avg([Benchmark Grade]) Over ([Teacher Name],[CY Description],[Section],[Benchmark],[Assignment Type])