Tool Search: Filter Designer
The following queries are related to census and/or staff information and require that the Census/Staff radio button be selected from the main Filter Designer page.
Staff Members with Individual Tool Rights
This query returns staff member staff members who have individual tool rights assigned instead of group tool rights, as recommended.
Box A
INNER JOIN UserAccount ua ON ua.personID = individual.personID INNER JOIN UserToolRights utr ON utr.userID = ua.userID
Box B
No text necessary
Students Marked as No Shows
This query returns students who have been flagged as "no-show" students (those who have enrollment records but have never actually attended school). The user will need the calendarID of the desired school calendar ( this example uses a value of 80).
This query cannot be performed in Student mode. No Show students are excluded from the student view, so this query must search within all people.
Box A
INNER JOIN Enrollment e ON e.personID = individual.personID
Box B
AND e.noshow = 1 AND e.calendarID = 80
People with a Staff Number but no District Assignment Record
This query returns staff members who are not linked to a district assignment.
Box A
LEFT OUTER JOIN EmploymentAssignment e ON e.personID = individual.personID
Box B
AND individual.staffNumber IS NOT NULL AND e.personID IS NULL
Staff Members with Disabled User Accounts
This query returns staff members who have disabled user accounts.
Box A
INNER JOIN UserAccount ua ON ua.personID = individual.personID
Box B
AND individual.staffNumber IS NOT NULL AND ua.homepage IS NULL AND ua.disable = 1
Staff Members who have not Logged In
This query returns staff members who have not logged into their accounts between the specified date range.
Box A
INNER JOIN UserAccount ua ON ua.personID = individual.personID LEFT OUTER JOIN SecurityAccess sa ON sa.userID = ua.userID AND sa.[timestamp] BETWEEN 'MM/DD/YYYY' AND 'MM/DD/YYYY'
Box B
AND individual.staffNumber IS NOT NULL AND ua.homepage IS NULL AND sa.accessID IS NULL
Staff Members with User Accounts Set to All Tools
This query returns staff members who have the All Tools checkbox set on their user accounts.
Box A
INNER JOIN UserAccount ua ON ua.personID = individual.personID AND allModules = 1
Box B
No text necessary
Parents/Guardians of Current Year Students Who have no Parent Portal Account
This query returns parents/guardians of current year students district-wide. This includes staff members with a Campus Application/Campus Instruction user account who do not have a parent portal account. (Note: Parent/guardian relationship is indicated by marking the Guardian and Portal checkboxes on the Relationships screen.)
Box A
JOIN relatedpair rp on rp.personID1 = individual.personID JOIN student s on s.personID = rp.personID2 LEFT JOIN useraccount ua on ua.personID = rp.personID1 and ua.homepage is not null and ua.homepage <> 'nav-wrapper/TeacherApp/control-center/home'
Box B
AND rp.guardian = 1 AND rp.portal = 1 AND s.activeYear = 1 AND (s.endDate >= GETDATE() or s.endDate is null) GROUP BY individual.personID, individual.lastName, individual.firstName HAVING COUNT(userID) = 0
Teachers with no Lesson Plans for the Current School Year
This query returns information on teachers who have not set up the Lesson Planner in the current school year.
Box A
INNER JOIN Section se ON se.teacherPersonID = individual.personID INNER JOIN Course c ON c.courseID = se.courseID INNER JOIN Calendar cal ON cal.calendarID = c.calendarID INNER JOIN SchoolYear sy on sy.endyear = cal.endyear and sy.active = 1 LEFT OUTER JOIN LessonPlanGroup lpg ON lpg.sectionID = se.sectionID LEFT OUTER JOIN LessonPlanGroupActivity lpga ON lpga.groupID = lpg.groupID LEFT OUTER JOIN LessonPlanActivity act ON act.activityID = lpga.activityID INNER JOIN activeTrial at ON at.trialID = se.trialID
Box B
GROUP BY individual.personID, individual.lastName, individual.firstName HAVING SUM(COALESCE(act.activityID, 0)) = 0
Teachers with Lesson Plans for at least one Section in the Current School Year
This query returns information on teachers who have set up at least one course section in the Lesson Planner for the current school year.
Box A
INNER JOIN Section se ON se.teacherPersonID = individual.personID INNER JOIN Course c ON c.courseID = se.courseID INNER JOIN Calendar cal ON cal.calendarID = c.calendarID INNER JOIN SchoolYear sy on sy.endyear = cal.endyear and sy.active = 1 LEFT OUTER JOIN LessonPlanGroup lpg ON lpg.sectionID = se.sectionID LEFT OUTER JOIN LessonPlanGroupActivity lpga ON lpga.groupID = lpg.groupID LEFT OUTER JOIN LessonPlanActivity act ON act.activityID = lpga.activityID INNER JOIN activeTrial at ON at.trialID = se.trialID
Box B
GROUP BY individual.personID, individual.lastName, individual.firstName HAVING SUM(COALESCE(act.activityID, 0)) > 0
Teachers with at Least One Section in the Current School Year but No Lesson Plans
This query returns teachers assigned to at least one section in the current schol year, but do not have any lesson plans created.
Box A
INNER JOIN Section se ON se.teacherPersonID = individual.personID INNER JOIN Course c ON c.courseID = se.courseID INNER JOIN Calendar cal ON cal.calendarID = c.calendarID INNER JOIN SchoolYear sy on sy.endyear = cal.endyear and sy.active = 1 LEFT OUTER JOIN LessonPlanGroup lpg ON lpg.sectionID = se.sectionID LEFT OUTER JOIN LessonPlanGroupActivity lpga ON lpga.groupID = lpg.groupID LEFT OUTER JOIN LessonPlanActivity act ON act.activityID = lpga.activityID INNER JOIN activeTrial at ON at.trialID = se.trialID
Box B
No text necessary
Teachers who have used the Save Draft on Behavior Referrals
This query returns a list of teachers who have saved drafts of student behavior referrals.
Box A
INNER JOIN BehaviorIncident i ON i.referralPersonID = individual.personID
Box B
AND i.status = 'DF'
Parent or Guardian without User Accounts
This query returns a list of parents/guardians who do not have a Campus Portal Account or a Campus Application account.
Box A
JOIN v_CensusContactSummary ccs ON ccs.contactPersonID = individual.personID JOIN student s ON s.personID = ccs.personID LEFT JOIN UserAccount ua ON ua.personID = ccs.contactPersonID
Box B
AND ccs.guardian = 1 AND ua.userID IS NULL AND s.activeyear = 1
Parent or Guardian with User Accounts but no Logins
This query returns a list of parents/guardians who have a Campus Portal Account but have not logged into it.
Box A
JOIN v_CensusContactSummary ccs ON ccs.contactPersonID = individual.personID JOIN student s ON s.personID = ccs.personID JOIN UserAccount ua ON ua.personID = ccs.contactPersonID JOIN v_UserAccountUsage uav ON uav.userid = ua.userid
Box B
AND ccs.guardian = 1 AND ccs.portal = 1 AND uav.totallogincount = 0 AND s.activeyear = 1
