Set-up Screen Reports

Screen Reports are created by writing and storing a SQL query in the table. The table has location fields that control which screen the report will be available on.

You can also use xlsx, docx and pdf outputs for your screen report.
Go to the Set-up Screen Report Templates page for the steps required to create these outputs, after you have created the screen report you want.

Creating Queries

Below are links to pages that provide details to help write queries you can use in screen reports

User Defined Queries

Encrypted Fields

Office(xls) Format

Office(xlsx) Format

Derived Fields - Below are the libraries that are available for use in screen reports:

payroll_advice

quote

Default Reports

The records below are stored in as standard entries.

module	page	sub_page	name	output_filename_alias	status_flag	Location	Key
fm	contracts	fbt	transactions		active	Contract / FBT
fm	contracts	fbt	driver_portal		active	Contract / FBT
			driver_portal		active	Driver Portal
fm	contracts	payout	payout_report		active	Contract / Payout
gl	becs	reimbursements	preview	reimbursements_preview	active	BECS / Reimbursements (ABA)	#becs_id#
gl	becs	reimbursements	transactions	reimbursements_{BECS ID}	active	BECS / Reimbursements (ABA)	#becs_id#
gl	becs	disbursements	client_preview	disbursements_preview	active	BECS / Disbursements (ABA))	#becs_id#
gl	becs	disbursements	company_preview	disbursements_preview	active	BECS / Disbursements (ABA))	#becs_id#
gl	becs	disbursements	transactions	disbursements_{BECS ID}	active	BECS / Disbursements (ABA))	#becs_id#
gl	xero	general_ledger				Export / Xero PL Exports	#export_id#

Sample table population

You can set up screen reports on each of the screens listed below. This also shows the required configuration you need;

report_query_id

module

page

sub_page

name

description

query

user_id_last_edit

last_edit

template_id

output_filename_alias

print_format

status_flag

100000

contracts

order

file name

Select from the Contracts / Order tab

WHERE contractid = '#t1.contractid#'

native

active

100001

contracts

finance

file name

Select from the Contracts / Finance tab

WHERE contractid = '#t1.contractid#'

native

active

100002

contracts

lease

file name

Select from the Contracts / Lease tab

WHERE contractid = '#t1.contractid#'

native

active

100003

contracts

lease

file name

Select from the Contracts / Payout tab.

100004

contracts

fbt

file name

Select from the Contracts / FBT tab

WHERE contractid = '#t1.contractid#'

native

active

100005

quotes

contract

file name

Select from the Quotes / Contract tab

WHERE quoteid = '#t1.quoteid#'

native

active

100006

quotes

vehicle

file name

Select from the Quotes / Vehicle tab

WHERE quoteid = '#t1.quoteid#'

native

active

100007

employees

current_packages

file name

Select from the Employees / Current Packages tab

WHERE t1.driverid = '#t1.driverid#'

native

active

100008

becs

disbursements

file name

Select from the BECS / Disbursements (ABA)) tab. Use this key id to create a preview report for a Company. Only use one key id per query or the query will not run.

WHERE companyid = '#companyid#'

native

active

100009

becs

disbursements

file name

Select from the BECS / Disbursements (ABA)) tab. Use this key id to create a preview report for a Client. Only use one key id per query or the query will not run.

WHERE clientid = '#clientid#'

native

active

100010

becs

disbursements

file name

Select from the BECS / Disbursements (ABA) tab). Use this key id to create a report that will run when a becs batch has been selected. Only use one key id per query or the query will not run.

WHERE becsid = '#t1.becsid#'

native

active

Field	Mandatory / Optional	Description
reportqueryid	Auto	System allocated unique identifier. Auto
module	Optional	Enter the module that the screen report will generate from. 'qt','fm','gl' or 'sp' are currently supported. Records that are not used in system screens are added with a 'null' entry. Optional
page	Optional	Enter the screen that the screen report will generate from. Refer to the Sample table population section for examples. Records that are not used in system screens are added with a 'null' entry. Optional
sub_page	Optional	Enter the tab that the screen report will generate from. Refer to the sample data section. Records that are not used in system screens are added with a 'null' entry. Optional
name	Mandatory	Enter the report name as one word in lower case. E.g. orderfile This name is shown in the screen selection drop list field. The name is displayed in proper case. E.g. Order File Each name used on a page must be unique (you cannot have two reports with the same name on the same screen). The name is also used as the default file name with the key id appended. E.g. If you navigate to Contracts / Order and select the 'Order File' for contract 108978, the file will be called 'orderfile108978.xls'. You can use a different name for the file output by adding an entry into the outputfilename_alias field. Mandatory
description	Optional	Enter a description of the report for reference. This is not displayed on user screens. Optional
query	Mandatory	Store the sql query required to generate the report here. The query must be valid. Visit User Defined Queries for query building information and the derived field libaries for calculated fields you can use here. The standard query will deliver the systems regular logo and presentation. Visit Office(xls) formatFormat) if you want to modify the look of the standard report. To return the details for the contract that is displayed on the screen, the query must contain the following mark-up: WHERE contractid = '#t1.contractid#' (Table Alias must be t1) You can set-up a query that delivers multiple records, but it won't be usable with a form, as the placeholders will only return data for the first record of the query, and this may not be the displayed contract. If any components are invalid and cause the report to fail, an alert 'Nothing to report!' will appear on screen. Please contact Catch-e to have the issue resolved. This message will also appear if there are no transactions for the chosen contract.
templateid	Optional	This field is currently non-functional
fileid	Optional	If this field is blank, the default screen report outputs will be available. These are 'Office(xls) as PDF','Office(xls)' and 'Text(CSV)'. If you want to create an Office(docx) or Office(xlsx) template to use with your screen report, go to the Set-up Screen Report Templates page for the steps that are required to do this. The last step of the set-up process is to add the fileid record that has been created here.
outputfilenamealias	Optional	Add an entry here if you want the output file name to be different to the stored name. You can use placeholders in the alias for any of the fields that exist in the stored query. E.g. 'orderfile#contractid#' or 'orderfile{contractid}' will display 'Order File 100000' in the selection list box and generate a file called 'orderfile100000.xls'. Noting some placeholders like dates using dd/mm/yyyy. A “/” would be considered a prohibited character. This feature will work if you add a placeholder that contains illegal characters such as / \ : * ? " < > \|, the prohibited characters will be replaced with underscores "_".
printformat	Mandatory	Enter which print formats you want to make available to users when the report is printed from the screen. See below for list of print formats.
statusflag	Mandatory	If 'active', the report is visible on the specified screen and if 'inactive', it is hidden.

printformat

print_format	available formats in drop list
native	Office(xls)
native-and-pdf	Office(xls) as PDF Office(xls)
pdf	Office(xls) as PDF

Useful Queries

# Show current Screen Reports select rq.report_query_id,rq.module,rq.page,rq.sub_page,rq.name,rq.print_format AS print_format_xls,rq.status_flagfrom gb_report_queries AS rq ORDER BY module DESC, page DESC, sub_page DESC, name DESC