National Reporting Requirements

This page captures the National Reporting Requirements for reports to be built from the Oriel Recruitment Dataset.

Working Group

James Harris Ashley Ransoo Alana Martinez (Unlicensed) Clare Wright, Jonathan Howes, Alice Gagg

Oriel Dataset

No.	Table Name	Column Name	Comments
1	vw_VacancyList	ClosingDateTime
2	vw_VacancyList	Grade
3	vw_VacancyList	GradeAbbreviation
4	vw_VacancyList	NationalVacancyId
5	vw_VacancyList	OpeningDateTime
6	vw_VacancyList	OrganisationRecruitingForList
7	vw_VacancyList	PostType
8	vw_VacancyList	PreferencingRegion
9	vw_VacancyList	PreferencingSubRegion
10	vw_VacancyList	ProgrammeStatus
11	vw_VacancyList	SpecialtyProgramme
12	vw_VacancyList	StaffGroup
13	vw_VacancyList	VacancyId
14	vw_VacancyList	VacancyTitle
15	vw_VacancyDetails	AdvertDescription
16	vw_VacancyDetails	ClosingDateTime
17	vw_VacancyDetails	CommencingFrom
18	vw_VacancyDetails	EnquiryEmailAddress
19	vw_VacancyDetails	EnquiryWebsite
20	vw_VacancyDetails	Grade
21	vw_VacancyDetails	GradeAbbreviation
22	vw_VacancyDetails	NationalVacancyId
23	vw_VacancyDetails	OpeningDateTime
24	vw_VacancyDetails	OrganisationRecruitingForList
25	vw_VacancyDetails	PostType
26	vw_VacancyDetails	PreferencingRegion
27	vw_VacancyDetails	PreferencingSubRegion
28	vw_VacancyDetails	RecruitmentOffice
29	vw_VacancyDetails	SpecialtyProgramme
30	vw_VacancyDetails	StaffGroup
31	vw_VacancyDetails	VacancyId
32	vw_VacancyDetails	VacancyTitle
33	vw_VacancyDetails	VacancyURL
34	vw_VacancyDetails	HierarchyDeadlineDate	To know the cutoff date for all offer accepted.
35	vw_VacancyDetails	UpgradeDeadlineDate	To know the cutoff date for potential upgrades.
36	vw_VacancyDetails	RoundID	To be able to filter vacancies to a particular round./To not have to deconcatenate this value from the National Vacancy ID
37	vw_VacancyDetails	RoundRO	To be able to filter vacancies to a particular round./To not have to deconcatenate this value from the National Vacancy ID
38	vw_VacancyDetails	Round	To not have to deconcatenate this value from the National Vacancy ID
39	vw_VacancyDetails	RecruitmentYear	To not have to deconcatenate this value from the National Vacancy ID
40	vw_VacancyDetails	RecruitmentType	To not have to deconcatenate this value from the National Vacancy ID
41	vw_VacancyDetails	AdvertNumber	To not have to deconcatenate this value from the National Vacancy ID
42	vw_PreferenceDetails	ClosingDateTime
43	vw_PreferenceDetails	CompetitionRatio
44	vw_PreferenceDetails	Employer
45	vw_PreferenceDetails	NationalVacancyId
46	vw_PreferenceDetails	OfferCode
47	vw_PreferenceDetails	OpeningDateTime
48	vw_PreferenceDetails	PlacementSite
49	vw_PreferenceDetails	PlacesAvailable
50	vw_PreferenceDetails	PreferenceLevel
51	vw_PreferenceDetails	PreferenceUniqueCode
52	vw_PreferenceDetails	ProgrammeDuration
53	vw_PreferenceDetails	ProgrammeName
54	vw_PreferenceDetails	ProgrammeNumber
55	vw_PreferenceDetails	SpecialtyProgramme
56	vw_PreferenceDetails	VacancyId

Reporting Requirements

No.	Report Name/Summary	Detailed Description	Report Type - Dynamic/ Static
1.	Application Status/Window	Track progress of applications from Applied> Longlisted > Declined > Shortlisted > Offered > Offer Accepted	Dynamic - Dynamic Data PID - Not in NDW?
2.	Core recruitment data by applicants - by Specialty/ Grade/ Round/ Rounds/ Demographics		Dynamic
3.	Table showing count of crossover applications within and across rounds for each vacancy, this can be filtered by round, Specialty, specialty grouping . The report options are:	Crossover count at application Crossover at application as a percentage Crossover count of those who accept Each of the above reports also have the count of applicants, applications (generally the same as applicants other than where devolved nations run their own recruitment eg. NI CST) and unique applicants by specialty
3.	Core vacancies/posts advertised - by Grades/ Specialty/ Competition ratios	Posts filled, comparisons over the years Posts re-advertised (avoid double counting)
4.	Equality and Diversity Monitoring	Flag for investigation by ethnicity Specialty/Local Office/Nation/CoQ Review GDPR
5.	Where are people applying from?	By address fields? By Preferences (region, sub-regions, sectors etc.)
6.	Applications behaviours during the application window?	How many applications? How many unique applications by specialty? How many cross-overs?

Questions / Assumptions for Discussion

No.	Question/Assumption	Comments	Owner
1.	In order to build all the reports required for national reporting, HEE requires its data from Hicom to be available more frequently (more than daily, perhaps hourly).
2.	Oriel Custom Reporting and Scheduled Custom Reporting are currently available. Both allows us to get a CSV file of a subset of Oriel recruitment data. It is currently resource-heavy on the Data Service team to have to download the data, manipulate and upload back. This is almost manual ETL process. Having the data available to HEE refreshed more frequently from Hicom would allow us to focus on reports creation rather than the process of getting the data.
3.	Workforce Extract is a piece of work that was commissioned to automate the process of bringing Offer Accepted applicants from Oriel into TIS without the need for administrative intervention. HEE still requires the Workforce Extract work that was previous commissioned from Hicom. (Addition of Programme Name, number etc. ). Is it independent from this work from a Hicom perspective?
4.	New Managed instance of NDW - separating Applicants (PID) from pseudonymized data. What are the IG implications/constraints of bringing the data in-house for National reporting?
5.	HEE recruitment timeline dictates when a round start and finishes. The Hierarchy deadline date on Oriel is not strictly following those dates. How does that impact an automated data solution?
6.	Who are the end users of the National Reporting? (National, Local Office and Specialty) What are the general needs of those users? What are the specific reporting needs of those users?	Internal - Discovery work will be required once we have the data.	Internal