1. Using Oracle Business Intelligence for Recruiting and Onboarding
  2. Recruiting - CSW Funnel

Recruiting - CSW Funnel

The chart reveals health indicators based on candidate conversion rates across the key standard reference recruiting statuses.

Stage Conversion

Image showing a funnel chart

Report Columns

The table shows the report columns and syntax if any
Name Syntax
Stage 
CASE  
	WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
		= 'New - No' 
		THEN '1- All Submissions' 
	WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
		= 'New - Yes' 
		THEN '2- Completed Submissions' 
	WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
		IN ('Reviewed - No', 'Reviewed - Yes') 
		THEN '3- Reviewed' 
	WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
		IN ('1st Interview - No', '1st Interview - Yes') 
		THEN '4- Interviewed' 
	WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
		IN ('Hired - No', 'Hired - Yes') 
		THEN '5- Hired' 
		ELSE "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
END
# Submissions 
CASE 
	when  CASE  
		WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
		= 'New - No' 
		THEN 'All Submissions' 
		WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
		= 'New - Yes' 
		THEN 'Completed Submissions' 
		WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
		IN ('Reviewed - No', 'Reviewed - Yes') 
		THEN 'Reviewed' 
		WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
		IN ('1st Interview - No', '1st Interview - Yes') 
		THEN 'Interviewed' 
		WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
		IN ('Hired - No', 'Hired - Yes') 
		THEN 'Hired' 
		ELSE "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
	END 
		<> 'All Submissions' 
		THEN "Candidate Counts"."# Submissions" 
	ELSE EVALUATE('LEAD(%1,1) OVER (ORDER BY 1)',"Candidate Counts"."# Submissions") +  "Candidate Counts"."# Submissions" 
END
Target 
IFNULL(
	(
		(MSUM 
			(CASE when  
				CASE  WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
					= 'New - No' 
					THEN 'All Submissions' 
				WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
					= 'New - Yes' 
					THEN 'Completed Submissions' 
				WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
					IN ('Reviewed - No', 'Reviewed - Yes') 
					THEN 'Reviewed' 
				WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
					IN ('1st Interview - No', '1st Interview - Yes') 
					THEN 'Interviewed' 
				WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
					IN ('Hired - No', 'Hired - Yes') 
					THEN 'Hired' 
				ELSE "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" END
			<> 'All Submissions' 
				THEN "Candidate Counts"."# Submissions" 
			ELSE EVALUATE('LEAD(%1,1) OVER (ORDER BY 1)',"Candidate Counts"."# Submissions") +  "Candidate Counts"."# Submissions" END, 2)
		- CASE when  
			CASE  
				WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
					= 'New - No' 
					THEN 'All Submissions' 
				WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
					= 'New - Yes' 
					THEN 'Completed Submissions' 
				WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
					IN ('Reviewed - No', 'Reviewed - Yes') 
					THEN 'Reviewed' 
				WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
					IN ('1st Interview - No', '1st Interview - Yes') 
					THEN 'Interviewed' 
				WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
					IN ('Hired - No', 'Hired - Yes') 
					THEN 'Hired' 
				ELSE "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" END
			<> 'All Submissions' THEN "Candidate Counts"."# Submissions" END) 
	* CASE  
		CASE  
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				= 'New - No' 
				THEN '1- All Submissions' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				= 'New - Yes' 
				THEN '2- Completed Submissions' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				IN ('Reviewed - No', 'Reviewed - Yes') 
				THEN '3- Reviewed' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				IN ('1st Interview - No', '1st Interview - Yes') 
				THEN '4- Interviewed' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				IN ('Hired - No', 'Hired - Yes') 
				THEN '5- Hired' 
			ELSE "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" END
		WHEN '2- Completed Submissions' 
			THEN 0.9  
		WHEN '3- Reviewed' 
			THEN 0.1 
		WHEN '4- Interviewed' 
			THEN 0.8 
		WHEN '5- Hired' 
			THEN 0.5 
		ELSE 1 END)
,EVALUATE('LEAD(%1,1) OVER (ORDER BY 1)',"Candidate Counts"."# Submissions") +  "Candidate Counts"."# Submissions")
% Converted 
cast(
	CASE when  
		CASE  
		WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
			= 'New - No' 
			THEN 'All Submissions' 
		WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
			= 'New - Yes' 
			THEN 'Completed Submissions' 
		WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
			IN ('Reviewed - No', 'Reviewed - Yes') 
			THEN 'Reviewed' 
		WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
			IN ('1st Interview - No', '1st Interview - Yes') 
			THEN 'Interviewed' 
		WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
			IN ('Hired - No', 'Hired - Yes') 
			THEN 'Hired' 
		ELSE "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" END
	<> 'All Submissions' 
		THEN "Candidate Counts"."# Submissions" 
	ELSE EVALUATE('LEAD(%1,1) OVER (ORDER BY 1)',"Candidate Counts"."# Submissions") +  "Candidate Counts"."# Submissions" END
	/(msum(
		CASE when  
			CASE  
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				= 'New - No' 
				THEN 'All Submissions' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				= 'New - Yes' 
				THEN 'Completed Submissions' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				IN ('Reviewed - No', 'Reviewed - Yes') 
				THEN 'Reviewed' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				IN ('1st Interview - No', '1st Interview - Yes') 
				THEN 'Interviewed' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				IN ('Hired - No', 'Hired - Yes') 
				THEN 'Hired' 
			ELSE "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" END 
		<> 'All Submissions' 
			THEN "Candidate Counts"."# Submissions" 
		ELSE EVALUATE('LEAD(%1,1) OVER (ORDER BY 1)',"Candidate Counts"."# Submissions") +  "Candidate Counts"."# Submissions" END,2)
	- CASE when  
		CASE  
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				= 'New - No' 
				THEN 'All Submissions' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				= 'New - Yes' 
				THEN 'Completed Submissions' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				IN ('Reviewed - No', 'Reviewed - Yes') 
				THEN 'Reviewed' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				IN ('1st Interview - No', '1st Interview - Yes') 
				THEN 'Interviewed' 
			WHEN "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" 
				IN ('Hired - No', 'Hired - Yes') 
				THEN 'Hired' 
			ELSE "Submission CSW Status - Historical"."Historical Reference Status Name" || ' - ' || "Submission General Info"."Submission Is Completed" END
		<> 'All Submissions' 
			THEN "Candidate Counts"."# Submissions" 
		ELSE EVALUATE('LEAD(%1,1) OVER (ORDER BY 1)',"Candidate Counts"."# Submissions") +  "Candidate Counts"."# Submissions" END) 
as double) * 100

Filters

The table shows a filter
Name
Historical Reference Status Name is equal to / is in New; Hired; 1st Interview; Reviewed
Recruiting - Summary Dashboard Prompt Listener

View Configurations

The table shows the report settings and configurations
Name Additional Information
Funnel

Actual: # Submissions

Target: Target

Stage: Stage
Column

Bars: # Submissions, Target

Group By: Stage
Table

Columns and Measures: Stage, # Submissions, Target, % Converted

Funnel Settings

For this funnel, high values are desirable.

The table shows the graph settings
Threshold Status
115

Exceeded Target
95 95% of Target
75 75% of Target
50 50 % Of Target