Flexible Reports Tool

Flexible Reports Tool

About this guide

This guide explains how to use the Flexible Reports Tool to view reporting data, to create and modify reports. Reports built locally can be saved to be run again or saved and scheduled to run automatically.

The features described in this guide are only available to users that have permission to access the Flexible Reporting Tool.

Overview

The reporting data for your organisation is available to users with the specific permission assigned by your system administrators. The data can be manipulated to provide reports or for analysis of the information contained. The Flexible Reporting Tool provides access to the data, tools to manipulate it and the ability to export the information as well as viewing on screen. Reports that have been created can be saved to be run again. Saved reports can be scheduled to run automatically with the output sent out by mail, and can be made available to others in the organisation as needed.

Where to start

Click on the ‘Reports’ button on the left hand menu. The list of standard reports is displayed. If you have been assigned the permission to access the flexible reporting tool you will see the link at the top of the page.

Clicking on the “Access the reporting tool” link results in the flexible reporting reports being displayed. Access to the data in the system for reporting on has been divided into logical report areas. These enable related data to be easily accessed.

Click on the “View this report” link for the report area that you wish to view. A pre-selection screen is presented to provide options of the data item groups that you wish to include within a report. This provides a means by which you can select the relevant data items for your report or analysis, as an alternative to all columns in the report area.

Annex A lists the different reports available and identifies the data items and the associated data groups that are included in each report. Where a report includes vacancy data, this is consistent across reports.

Report View

The default view of the report is a table view, with each record as a row and each data item as a column.

The report table can be hidden, when wanting to display only a chart for example, by clicking on the minus symbol (-) beside the ‘Table’ heading.

At the top of the page, below the report title, the report controls are displayed. Click on a control button for it to expand to provide the relevant choices or data entry options. Click on the control button again for the choices to be removed from display.

Click on ‘Add’ button to apply a report control. Once a report control is in place, ‘Replace’ and ‘Remove’ buttons are displayed. Selecting different criteria within a control and clicking on ‘Replace’ will replace the existing control criteria with the new criteria, while clicking on ‘Add’ will apply the new criteria in addition to the existing. ‘Remove’ will remove the control criteria.

The alternative output format choices are available as buttons below the ‘Table’ heading.

Report Control Options

Layout

Layout options enable you to hide data items from the report table and to change the sequence in which the data columns are presented.

To hide a data item un-tick the checkbox to the right of the item.

To move a data item click on the left of right arrow (triangle) to move the column to the left or right.

Important: Ensure you click the ‘OK’ button at the end of the list of columns to implement your change.

Sort

The Sort option enables you to change the order the records (rows) in the report table.

You can also change the sort order by clicking on the relevant column heading in the table.

Filter

The Filter option enables you to reduce the data to only those records that match your filter criteria.

Select a data item (column) to filter on, select the appropriate comparison and enter the value.

Group

The Group option enables you to group the data by the data item (column) selected.

Aggregate

The Aggregate option enables you to calculate information at group levels, to summarise or simplify the data.

Select a data item (column) to aggregate on and select the function to be applied

Chart

The Chart option enables you to produce a chart or charts from the data, aggregated data or formulae in the report table. There are seven options for chart format.

Select the required chart option, then select the relevant chart controls.

Crosstab

The Crosstab option enables you to produce summarised information with data items in both the rows and the columns.

Select the data items for the columns and rows, then select the data to be aggregated and the function to be applied.

Formula

The Formula option enables you to add columns to the report table with data calculated from existing data items.

Enter the name of the new column, enter the formula for the calculation, then change the data type and display format if required. References to data items using the column heading should include the column heading in square brackets (eg. [Published date]).

Annex B provides information on the formula options available and their construct.

Paging

Paging enables you to change the number of rows that appear per page of the report on screen.

Saving reports

The saving and scheduling controls are displayed to the left of the report table.

To save a report enter a title or description in the Description and click on the Save button.

Saved reports are listed in the lower panel, under “Your saved reports definitions”.

Saved reports can be loaded for modifying or viewing the information. Saved reports can be scheduled to run automatically.

To schedule a report click on the Schedule button. Select how frequently the report should be run and specify the start date and time for the automatic running of the report.

To have the scheduled report sent to people in addition to yourself, enter the email address(es) in the box. Finish by clicking on Set schedule button.

Saved flexible reports can also be made available to users in the organisation via the permission “Access saved reports”. When such users click on the “Reports” tab, they will an option “Saved reports”:

The user will then be able to choose the report to run:

And will be able to choose some filter options to apply to the report ahead of running it:

Examples

Grouping vacancy data by Job Type, then Pay Band/Scale

Click on Group control button.

Select Job Type. Click on Add button.

Report table changes to reflect the selection

Select Pay Band/Scale. Click on Add button.

Report table changes to reflect the selection

The report table can be reduced down to the grouped rows by ticking the ‘Exclude Detail Rows’ checkbox.

Report table changes to reflect the selection

Include aggregated vacancy numbers

Click on Group control button. Select Vacancy ID. Select Count function. Click on Add button.

Report table changes to reflect the selection

Include bar chart for number of vacancies per job type

Click on Chart control button and select chart type button (Bar in this example).

Select Job Type as label. Select Vacancy ID as data. Select Count function. Click on Add button.

The chart is added below the report table.

Include pie chart for number of vacancies per job type

Click on Chart control button and select chart type button (Pie in this example).

Select Job Type as label. Select Vacancy ID as data. Select Count function. Click on Replace button.

The chart is added below the report table.

Include formula for month of publication

Click on Formula control button. Enter name of new column to be added to report table.

Enter formula – in this case: month([Published date])

Click on Add button.

The new column can then be used by other controls, such as Chart function.

See Annex B for information on formula options.Include crosstab table for number of vacancies per month per staff group

Click on Crosstab control button.

Select Month for header values (columns). Select Staff Group for label values (rows).

Select Vacancy ID for aggregate values (data item content). Select the aggregation function to be applied.

Click on Add button.

The crosstab table is added below the report table.

Annex A: The Report Areas

Vacancies

Choose the options from above to include data items into the report for you to view or work with. The data items in each group are listed below:

Group / Column / Description /
Job/role / Vacancy ID / System identifier for the vacancy used only within NHS Jobs
Organisation ID / System identifier for the organisation used only within NHS Jobs
Vacancy Reference number / Display reference for the vacancy which starts with the organisation's VPD code
Position number / Position number as taken from the approval section of the vacancy form
Organisation name / Name of the advertising organisation
Job Title / Job title on the vacancy
Location / Displayed Town/Location as it appears on the vacancy form
Post code / Postcode on the vacancy
Salary / Salary for the vacancy
Pay Scheme / Pay scheme on the vacancy
Pay Band/Scale / Pay band/scale on the vacancy
WTE / Whole time equivalent (WTE) indicated on the vacancy
Job Type / Job type on the vacancy
DBS Check required indicated / Indication on the vacancy that a DBS check is required
Approval process on NHS Jobs? / Indication of whether or not the vacancy is using the approval process on NHS Jobs
Welsh advert exists / Indication of whether or not a Welsh version of the advert exists
Number of views of advert / Number of views of vacancy advert
Number of applications submitted / Number of applications submitted to the vacancy
Create date / Date vacancy was created in NHS Jobs
Published date / Date the advert was published
Advert closed date / Date the vacancy advert closed
Date of first interview / Date of the earliest interview for the vacancy
Recruitment closed date / Date vacancy was last set to status "Recruitment closed"
Vacancy stats / Staff Group / Staff group on the vacancy
Area of Work / Area of work recorded on the vacancy
Occupation Code / Occupational code on the vacancy
Div/Dept for Reporting / Division/department for reporting on the vacancy
Cost centre / Cost centre as taken from the approval section of the vacancy form
Qualified Position / Indication of whether or not the vacancy is for a qualified position
Suitable for newly qualified applicant / Indication of whether of whether or not vacancy is suitable for newly qualified applicants as identified on the vacancy form
Vacancy team / Recruiting Manager email address / Email address of Recruiting Manager on the vacancy team
Recruitment Officer email address / Email address of Recruitment Officer on the vacancy team
Approver 1 email address / Email address for Approver 1 on the vacancy team
Approver 2 email address / Email address for Approver 2 on the vacancy team
Approver 3 email address / Email address for Approver 3 on the vacancy team
Approver 4 email address / Email address for Approver 4 on the vacancy team
Misc / Current vacancy status / Current status of the vacancy
Current advertising selections / The ‘publishing option’ last in use on the vacancy
Application Form selection / Application form selected for use on the vacancy
CV attachment allowed / Indication as to whether or not CVs can be attached to applications for the vacancy
Welsh applications accepted / Welsh language applications accepted for vacancy
Date advertised as Local restricted / Date vacancy was advertised to the organisation's restricted applicants
Job filled at Local restriction? / Was the vacancy filled when it was advertised to the organisation's restricted candidates?
Date advertised as Group restricted / Date vacancy was advertised to one or more restricted groups
Job filled at Group restriction? / Was the vacancy filled when it was advertised to a restricted group?
Date advertised as National restricted / Date vacancy was advertised to any restricted applicant nationally
Job filled at National restriction? / Was the vacancy filled when it was advertised to the national group of restricted candidates?
Date advertised to all NHS Jobs visitors / Date vacancy was advertised to all NHS Jobs visitors
Date applicant appointed / Date the successful applicant was recruited into the vacancy
Job filled by At Risk applicant? / Was the vacancy filled by an applicant who had a restricted account at the time?

Vacancies and applications

Choose the options from above to include data items into the report for you to view or work with. The data items in each group are listed below:

Group / Column / Description /
Job/role / Vacancy ID / System identifier for the vacancy used only within NHS Jobs
Organisation ID / System identifier for the organisation used only within NHS Jobs
Vacancy Reference number / Display reference for the vacancy which starts with the organisation's VPD code
Position number / Position number as taken from the approval section of the vacancy form.
Organisation name / Name of the advertising organisation
Job Title / Job title on the vacancy
Location / Displayed Town/Location as it appears on the vacancy form
Post code / Postcode on the vacancy