Reporting Information

All the recruitment data within the CAT is held in a relational database. Using the database tables and the entity-relationship diagram you can generate your own reports to show the information you require.

Standard Reports

The following reports are provided as standard with the CAT. The first two reports are special in that their definitions cannot be altered by the CAT administrator. All other reports are implemented so they can be altered or deleted by the CAT administrator.

report name	description
find a CV	List all CVs having a given property.
duplicate CVs	List potentially duplicate CVs.
count applications	Count the number of applications received by job type within the given time range.
count interviews	Count all interviews within the given time range.
count offers	Count the number of offers made within the given time range.
count starters	Count the number of new starters within the given time range.
interview effort	Calculate the total ime spent performing interviews for each interviewer.
list agency applications	List the applications received from a given agency in the given time frame.
list applications	List all applications received within the given time range.
list interviews	List all interviews within the given time range.
list interviews for job	List all interviews of a given type for a given job within the given time range.
list offers	List offers made within a given time range and show the current application status.
list starters	List all new starters within the given time range.

Running Reports

Running a report configured within the CAT is very simple. Clicking on the REPORT bubble displays a list of configured reports. Clicking on the name of one of these will run the report. If the report requires parameters, the user is prompted for the parameter values. For example, a report may list all received applications between two dates -- the values for these dates are the report's parameters. The results are displayed within the browser as an HTML table.

Defining Reports

The CAT provides an administrator with the ability to configure reports within the database. These are then accessible to users of the system by clicking on the REPORT bubble.

There are 3 stages to configure a report:

enter the SQL query, a name by which it is known and a brief description of what the query returns
set up names for the columns returned in the query
(optional) define parameters for the query e.g. a time range of interest etc.

Once these are done, the report is available to use.

Query Parameters

Definition: A parameter to a query is a value which is supplied when the query is run.

For example, the query may list the names of applications received between two dates -- these two date are the query parameters since they are entered by the user when the query is run.

The following is a query without any parameters:


	select title, givenName, surname
	from catApplicant
	order by surname asc

The following query requires the surname to be entered when the query is run:


	select title, givenName, surname
	from catApplicant
	where surname like ?
	order by surname asc

A query parameter has the following attributes:

name -- e.g. start date, end time, surname etc.
type -- what type of value is entered e.g. date, string, integer. The CAT also allows a type of enumeration where the value is chosen from a given set.
default value -- a value supplied by the system which the user may accept or change. The CAT defines some functions to help with default date and time values.

If the parameter is an enumeration there is an additional attribute which defines the set of allowed values. This can be an SQL expression such as


    select distinct surname from catApplication

or a Carapace script expression which returns a List such as


    (list "Mon" "Tue" "Wed" "Thu" "Fri")

Date & Time Functions

The following Carapace functions are defined to simplify the definition of date and time values within query parameters. Each of them takes an optional offset integer argument.

function	description	offset units
catDateTime	return the date & time in `DD-MMM-YYYY HH:MM:SS` format	seconds
catDate	return the date in `DD-MMM-YYYY` format	days
catTime	return the time in `HH:MM:SS` format	seconds
catMinutes	return the time in `HH:MM` format	minutes
catMonth	return the date of the first day in the month in `DD-MMM-YYYY` format	months

For example:


    (catMonth)          # first day of current month
    (catMonth -1)       # first day of previous month
    (catMonth  1)       # first day of next month

Contents

Index

Current topic: Carapace Application Tracker