...
This report lives in ReportServer and separate reports per campus. Will be used by the Provost/Chancellor offices to determine TPRC committees.
Requirements
Parameters are Campus and Year. Report should have the following fields.
| Application ID | Applicant Name | Applicant's UH Username | Year | Campus | Office | Current Classification and Rank | Excluded Faculty's Name | Excluded Faculty's UH Username |
Questions/Observations
What fields are needed in this report? See above
Manoa, West Oahu, and Hilo shouldn’t have any results for 2022 and prior because they’ve never used this feature, but results are showing up for those campuses. I believe that’s because when those campuses came on board, the Exclusions tab was available to the applicant but unbeknownst to them, who they excluded on the tab wasn't relevant.
Current Query 6/27/2023
| Code Block | ||
|---|---|---|
| ||
SELECT DISTINCT
applicant.application_id as 'Application ID',
applicant.name as 'Applicant Name',
applicant.username as 'Applicant''s UH Username',
application.year as 'Year',
campus.description as 'Campus',
office.description as 'Office',
CONCAT(rank.classification_code, rank.code) as 'Current Classification and Rank',
excluded.name as 'Excluded Faculty''s Name',
excluded.username as 'Excluded Faculty''s UH Username'
FROM application, office, campus,
(SELECT application_role.application_id, person.uhuuid, person.name, person.username FROM person INNER JOIN application_role ON person.id=application_role.person_id WHERE application_role.role_id=1) applicant,
(SELECT application_role.application_id, person.name, person.username FROM person INNER JOIN application_role ON person.id=application_role.person_id WHERE application_role.role_id=12) excluded,
(SELECT eligibility.application_id, appointment_rank.classification_code, appointment_rank.code FROM eligibility LEFT JOIN appointment_rank ON eligibility.current_rank_id=appointment_rank.id) `rank`
WHERE
applicant.application_id=excluded.application_id AND
application.id=applicant.application_id AND
application.office_id=office.id AND
campus.id=office.campus_id AND
applicant.application_id=rank.application_id AND
campus.code='MA' AND
application.year=2020
ORDER BY applicant.application_id; |