Set-up Screen Reports

Set-up Screen Reports

Screen Reports created by writing and storing SQL query in gb_report_queries table. Location fields control which screen report available on.

Can also use xlsx, docx, and pdf outputs for screen reports. Refer to Set-up Screen Report Templates page for steps required after creating screen report.

Creating Queries

Links to pages providing query details:

User Defined Queries
Encrypted Fields
Office(xls) Format
Office(xlsx) Format

Derived Fields

Libraries available for use in screen reports:

payroll_advice
quote

Default Reports

Standard entries stored in gb_report_queries:

Module	Page	Sub Page	Name Output	Filename Alias	Status	Location	Key
fm	contracts	fbt	transactions		active	Contract / FBT
fm	contracts	fbt	driver_portal		active	Contract / FBT
fm			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

Screen reports can be set up on screens listed below with required configuration:

Report Query ID

Module

Page

Sub Page

Name

Description

Query

User ID

Last Edit

Last Edit Template ID Output

Filename Alias

Print Format

Status

100000

fm

contracts

order

file name

Select from Contracts / Order tab

WHERE contract_id = '#t1.contract_id#'

0

0

0

0

native

active

100001

fm

contracts

finance

file name

Select from Contracts / Finance tab

WHERE contract_id = '#t1.contract_id#'

0

0

0

0

native

active

100002

fm

contracts

lease

file name

Select from Contracts / Lease tab

WHERE contract_id = '#t1.contract_id#'

0

0

0

0

native

active

100003

fm

contracts

lease

file name

Select from Contracts / Payout tab

0

0

0

0

native

active

100004

fm

contracts

fbt

file name

Select from Contracts / FBT tab

WHERE contract_id = '#t1.contract_id#'

0

0

0

0

native

active

100005

qt

quotes

contract

file name

Select from Quotes / Contract tab

WHERE quote_id = '#t1.quote_id#'

0

0

0

0

native

active

100006

qt

quotes

vehicle

file name

Select from Quotes / Vehicle tab

WHERE quote_id = '#t1.quote_id#'

0

0

0

0

native

active

100007

sp

employees

current_packages

file name

Select from Employees / Current Packages tab

WHERE t1.driver_id = '#t1.driver_id#'

0

0

0

0

native

active

100008

gl

becs

disbursements

file name

Select from BECS / Disbursements (ABA) tab. Preview for Company. Use one key id per query

WHERE company_id = '#company_id#'

0

0

0

0

native

active

100009

gl

becs

disbursements

file name

Select from BECS / Disbursements (ABA) tab. Preview for Client. Use one key id per query

WHERE client_id = '#client_id#'

0

0

0

0

native

active

100010

gl

becs

disbursements

file name

Select from BECS / Disbursements (ABA) tab. Report when BECS batch selected. Use one key id per query

WHERE becs_id = '#t1.becs_id#'

0

0

0

0

native

active

Field Descriptions

report_query_id

Mandatory / Optional: Auto

Description: System allocated unique identifier.

module

Mandatory / Optional: Optional

Description: Enter module that screen report will generate from. Supported: 'qt', 'fm', 'gl', 'sp'. Records not used in system screens added with 'null' entry.

page

Mandatory / Optional: Optional

Description: Enter screen that screen report will generate from. Refer to Sample table population section for examples. Records not used in system screens added with 'null' entry.

sub_page

Mandatory / Optional: Optional

Description: Enter tab that screen report will generate from. Refer to sample data section. Records not used in system screens added with 'null' entry.

name

Mandatory / Optional: Mandatory

Description: Enter report name as one word in lower case. Example: orderfile. Name shown in screen selection drop list in proper case (Order File). Each name on page must be unique (cannot have two reports with same name on same screen). Name also used as default file name with key id appended. Example: navigating to Contracts / Order and selecting Order File for contract 108978 creates file 'orderfile108978.xls'. Use output_filename_alias field for different output file name.

description

Mandatory / Optional: Optional

Description: Enter report description for reference. Not displayed on user screens.

query

Mandatory / Optional: Mandatory

Description: Store SQL query required to generate report. Query must be valid. Visit User Defined Queries for query building information and derived field libraries for calculated fields. Standard query delivers system regular logo and presentation. Visit Office(xls) format if modifying standard report look. To return details for contract displayed on screen, query must contain markup: WHERE contract_id = '#t1.contract_id#' (Table Alias must be t1). Can set up query delivering multiple records, but not usable with form as placeholders only return data for first record, may not be displayed contract. If invalid components cause report failure, alert 'Nothing to report!' appears on screen. Contact Catch-e to resolve. Message also appears if no transactions for chosen contract.

template_id

Mandatory / Optional: Optional

Description: Currently non-functional.

file_id

Mandatory / Optional: Optional

Description: If blank, default screen report outputs available: Office(xls) as PDF, Office(xls), Text(CSV). To create Office(docx) or Office(xlsx) template for screen report, visit Set-up Screen Report Templates page for required steps. Last step adds file_id record created here.

output_filename_alias

Mandatory / Optional: Optional

Description: Add entry if output file name different to stored name. Can use placeholders for any fields in stored query. Example: 'orderfile#contract_id#' or 'orderfile{contract_id}' displays Order File 100000 in selection list and generates file 'orderfile100000.xls'. Some placeholders with prohibited characters (/ \ : ? " < > |) replaced with underscores. Works with placeholders containing illegal characters such as / \ : ? " < > |, prohibited characters replaced with underscores "_".

print_format

Mandatory / Optional: Mandatory

Description: Enter which print formats available to users when report printed from screen. Refer below for print format list.

status_flag

Mandatory / Optional: Mandatory

Description: If 'active', report visible on specified screen. If 'inactive', hidden.

Print Format Options

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_flag from gb_report_queries AS rq ORDER BY module DESC, page DESC, sub_page DESC, name DESC

Notes

Module identifiers: qt (Quotes), fm (Fleet Management), gl (General Ledger), sp (Salary Packaging)
Table alias in WHERE clause must be t1 for standard screen reports
Placeholders format: #field_name# or {field_name}
Dates using dd/mm/yyyy format: forward slash "/" considered prohibited character
Query must be valid SQL to execute without errors
Multiple record queries work but not with forms (placeholders only return first record data)