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 (Unlicensed) Jonathan Howes (Unlicensed) Alice.Gagg (Unlicensed)

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 -

Include Protective Characteristics in all reports relevant

No.	Report Name/Summary	Detailed Description	Report Type - Dynamic - Static	Priority (L/M/H)	Target Users/ Audience/ Nations National Specialty Local Office	Aggregate/ Row-level data	Filters/ Sortable by	Comments
1.	Core vacancies/posts advertised - by Specialty/ Grade/Nation/Region/Round/ Rounds/ Demographics/ Competition ratios	Posts filled, comparisons over the years Posts re-advertised (avoid double counting) Dual training programme sub-specialty etc.	Static - after recruitment round ended Dynamic - before recruitment round closes	Highest (for fill rates April 2024 potentially)
2.	Application Status/Window at any point during the Recruitment Round By specialty	Track progress of applications from Applied> Longlisted > Declined > Shortlisted > Offered > Offer Accepted Pseudonymised applicant data	Static - after recruitment round ended Dynamic - before recruitment round closes	High
3.	Applicants behaviours during the application window Just around application numbers rather than the stage of application (1)	Pseudonymised applicant data How many applications? How many unique applications by specialty? How many cross-overs? Include applications in progress, i.e. not submitted Include Protective Characteristics	Static - after recruitment round ended Dynamic - before recruitment round closes	High (for medical)	MDRS - Useful throughout the application window to try to estimate numbers without going to Oriel		Date submitted field (ability to look at applications submitted per day)
4.	Equality and Diversity Monitoring - applicable for all applicants and their reports	Pseudonymised applicant data Flag for investigation by any of the EDI pertinent information Specialty/Local Office/Nation/Region/CoQ	Static - after recruitment round ended Dynamic - before recruitment round closes	High	MDRS - Used most often for FOIs		Specialty Vacancy (to discount local vacancies) Unique applicants Recruitment round	Doesn’t exist in UK reporting so FOIs only past 2 years of data.
5.	Table showing count of crossover applications within and across rounds for each vacancy, this can be filtered: by Specialty/ Grade/Nation/Region/Round/ Rounds/ Demographics	Pseudonymised applicant data 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 (potentially using the Oriel Applicant ID as the pseudonymised unique identifier)	Static - after recruitment round ended Dynamic - before recruitment round closes	Medium - High (will be re recruitment reform next years)	Used to inform changing recruitment processes.			Included in 9 as well. Either keep it separate or include in 9.
6.	Where are people applying from? where people are geographically based and where they are applying to where people are qualified from and where they are applying to	Pseudonymised applicant data By address fields By Country of Qualification By previous work History By Preferences (region, sub-regions, sectors etc.)	Static - after recruitment round ended Dynamic - before recruitment round closes	Medium	National team mostly Some FOIs
7.	Core recruitment data by applicant demographics/protected characteristics - by Specialty/ Grade/Nation/Region/Round/ Rounds/ Demographics	Pseudonymised applicant data	Static - after recruitment round ended Dynamic - before recruitment round closes	Medium - Only currently have nationality and CoQ
8.	Historical Application Status trends after the Recruitment Round that is currently available via Scotland reporting system.	Lift and shift Scotland system. Pseudonymised applicant data	Static - after recruitment round ended	Dependent on having UK reporting
9.	Evidence of Recruitment Activity	Cumulative count of applicants per status Non-cumulative count of applicants per status Pseudonymised applicant data	Static - after recruitment round ended Dynamic - before recruitment round closes	Low - No current functionality	Overseas Sponsorship Team National Local Office	Training Programme Date advertised Date opened Date closed Number of applicants applied Number of applicants longlisted Number of applicants shortlisted Number of applicants offered		Used for UKVI audit. Number required for each cohort (August 2024, etc).

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).	In principle this will be fine. Minute-by-minute queries would cause issues but a more frequent than daily shouldn’t be an issue. Will need to work with the Data Service team what their refresh schedule could be.
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.	DR3 currently seems the best option. May need to create an independent DR3 for Oriel depending on the volume of data requested.
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 previously commissioned from Hicom. (Addition of Programme Name, number etc. ). Is it independent from this work from a Hicom perspective?	Idea to keep the Workforce Extract independent despite there being potential duplication of fields. This will This will help with IG concerns as well as ensuring only relevant records are brought to TIS as the Workforce Extract is PID but reporting requirements are pseudonymised. Workforce Extract includes documents which are not required for reporting.
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?	As per above reporting requirements pseudonymised data only. Will need to have a conversation with IG team around transactional Oriel data and Oriel data kept for research purposes. Suggestion of using the Oriel Applicant ID as the pseudonymised unique identifier which can also potentially link records to TIS.
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?	To be dealt with at the reporting side not the data side. Hicom will continue to send dynamic data.
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