...
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/1627/2023
| Code Block | ||
|---|---|---|
| ||
SELECT DISTINCT applicant.application_id as 'Application ID', applicant.name as 'Applicant Name', applicant.username as 'Applicant''s UH Username', officeapplication.descriptionyear as 'OfficeYear', campus.description as 'Campus', hrdw_employee.current_classificationoffice.description as 'Current ClassificationOffice', hrdw_employee.current_rankCONCAT(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, hrdw_employee, (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.uhuuid=hrdw_employee.uhuuidapplication_id=rank.application_id AND campus.code='HA'MA' AND application.year=2020 ORDER BY applicant.application_id; |
...