Report Queue Setup

The Report Queue screen is used to display the reports that have been selected using the Report Queue flag. From this screen, users can retrieve the reports they have selected from the following menu options:

• Fleet / Administration / Reports

• Reporting / Standard Reports

This screen has been created to make it easier for users, both internal and external, to run reports without compromising system performance. Users will be able to select whether to run the report and download it immediately or to use the 'Report Queue' flag to push the report to the Report Queue screen.

Reports that take longer than the time specified in the Global Control reportqueuetimeouttobackground (default time is 10 minutes) will be sent to the Report Queue. The user will be alerted on screen that the report is taking a long time, and can be retrieved from the Report Queue screen. If set up, an email will be sent to the user when the report is available.

Please refer to Report Queue Roles Set Up to see which Roles have access to this feature.

User Set Up

By default, each User is set up to receive email notifications when a report they have requested is available to be retrieved from the Report Queue screen.

If you would like to switch this feature off, then please visit System / Users > Details Tab and check OFF the Report Queue Notifications flag.

Catch-e Set Up

Email Notification Template

Where the Report Queue Notifications flag is switched ON, an email template will need to be created. The content of these messages will be defined by you and supplied to Catch-e to upload into the system accordingly.

Under gb_templates, a template will need to be created with the name reportqueuemailmessage. The template_id will need to be recorded and entered into the gb_control reportqueuemailtemplate_id field in order for the email template to be created and sent as notification.

Warning: Currently, only ONE template with this name can be created and therefore one template_id stored in the gb_control table.

Long Running Reports

When system reports are run from Reports and Standard Reports, a cut off time can be set to push long running reports to the Report Queue screen. The default value is 10 minutes.

The table below describes how long running reports are managed under specific set ups:

Please contact Catch-e support to allocate a cut-off run time for reports before they are sent to the Report Queue. We will update the global control reportqueuetimeouttobackground accordingly.

Set-up Screen Report Templates

Screen Reports Templates Overview

You can develop a Screen Report Template in the same way that a System Form is created. Templates can only be created for 'Office(xlsx)' or 'Office(docx)' files.

Create a Screen Report Template

1. Select the Report Format 'Field List' for the screen report you want to create a template for

2. The 'Field List' is only available to 'admin' users

3. Only fields listed in the 'Field List' can be used as {placeholders} in your template

4. The {placeholders} will only return for the first record in the screen report that is run

5. The screen report should be limited to the one record you want to use in your template

6. Print and save this document

7. Create the xlsx or docx template you want. It should have the same name as the Screen Report it will be created from

8. Add the required {placeholders} from the saved 'Field List' file into your template file

9. Work out the following details you need to provide to Catch-e Support:

   • The name of the existing Screen Report

   • The kind of file you are creating (xlsx or docx)

   • The report formats you want to have available, e.g. PDF only, xlsx only or PDF and xlsx

10. From the details provide above, Catch-e Support can create the required:

    • gb_report_queries record

    • gb_report_query_files record

    • file_id (with filename)

11. When the records have been created, use the Upload a form process to upload and test your new template

Screen Report Templates Field Descriptions

gb_report_query_files Table

Print Format Values

Sample Record

Troubleshooting

Prior to uploading DOCX files, all {placeholder_names} must be spell checked and any text you see the wavy red underline must be Added to your dictionary.

In this sample document below, the text "{placeholder_names}" has a wavy red underline. This {placeholder} will not produce the desired outcome on your document.

However, the text "misspelled" also has a wavy red underline. Because this word is not a {placeholder} your document will show the text (with the spelling issue).

Useful Queries

Show Current Screen Report Templates

SELECT rq.report_query_id, rq.module, rq.page, rq.sub_page, rq.name, rq.print_format AS print_format_xls, rq.status_flag, fe.extension, rqf.print_format AS print_format_file, f.status_flag FROM gb_report_queries AS rq LEFT JOIN gb_report_query_files AS rqf ON rqf.report_query_id = rq.report_query_id LEFT JOIN gb_files AS f ON f.file_id = rqf.file_id LEFT JOIN gb_file_extensions AS fe ON fe.file_extension_id = rqf.file_extension_id; 

Create File Record

SELECT * FROM gb_file_types; SELECT * FROM gb_files; 

Find Screen Report Template Format Data

SELECT * FROM gb_report_queries; SELECT * FROM gb_file_extensions WHERE extension = 'xlsx'; /* or 'docx' */ SELECT * FROM gb_report_query_files; 

Generate Required Upload File

SELECT '' AS report_query_file_id, rq.report_query_id, f.file_id AS file_id, fe.file_extension_id, 'native-and-pdf' AS print_format /* or 'native' or 'pdf' */ FROM gb_report_queries AS rq INNER JOIN gb_file_extensions AS fe ON fe.extension = 'xlsx' /* or 'docx' */ INNER JOIN gb_files AS f ON f.name = 'transaction.docx' WHERE rq.name = 'transactions' AND rq.sub_page = 'fbt'; 

Derived Fields budgets_settlement

Derived fields from the /fleet/administration/contracts/templates/budgets_settlement library

This library is used to calculate budget values from the first period of a contract and other sundry fields.

Input Fields

Output Fields

• {postingclasscode}net — Net amount of the {postingclasscode} budget for period 1 of a contract, e.g. Fnet, INSnet, CEOnet. The FIN and CSD budgets are additionally combined into FINCSD_net

• {postingclasscode}gst — GST amount of the {postingclasscode} budget for period 1 of a contract, where {postingclasscode} represents a posting class code. The FIN and CSD budgets are additionally combined into FINCSD_gst

• credit_category — Value from fm_credit_categories > credit_category

• aftermarketequipment# — The description of an item of aftermarket equipment, stored in the qt_quote_optional_equipment table. The # represents a number, so if there were three aftermarket equipment items fitted, three fields will be returned numbering 1, 2, and 3

• aftermarketequipment#_code — The aftermarket code of an item of aftermarket equipment

• aftermarketequipment#_costprice_net — The cost price net of an item of aftermarket equipment

• aftermarketequipment#_listprice_net — The list price net of an item of aftermarket equipment

• aftermarketequipment#_discount_net — The net discount of an item of aftermarket equipment

Derived Fields emissions

Derived fields from the /lib/PDF/emissions library

This library is used to calculate emission values based on existing data. These readings are shown on screens and used in various reports, such as the Carbon Emissions Report.

This library can also be used with the following scheduler job methods: executeQuery, mailQuery, and eventQuery.

This library can also be used with the following APIs: printReportQuery and triggerTableImportProcedure.

Input Fields

Output Fields

Derived Fields fbt_operating

Derived fields from the /lib/PDF/fbt_operating library

This library is used to calculate FBT Operating information based on existing validated readings from the system. This library can be used with the Print a report query API.

Input Fields

Output Fields

Derived Fields financials

Derived fields from the /lib/PDF/financial library

This library is used to calculate financial values such as the NPV.

Input Fields

Output Fields

• pmt_periods_to_date — Monthly payments to date. If you don't provide this in the Input Fields it will be calculated as follows: Number of months between finance_start and today, minus periods_deferred

• npv — NPV (Net Present Value)

Derived Fields fuel_performance

Derived fields from the /lib/PDF/fuel_performance library

This library is used to calculate fuel usage values e.g. Litres / 100km. To do this it calculates a km span between the current row and the previous one.

Input Fields

Output Fields

Derived Fields funds_check

Derived fields from the /lib/PDF/funds_check library

This library is used to show the Funds Check pop up screen fields and values.

Library Comment Syntax

## library_type standard ## library_path /lib/PDF ## library_name funds_check 

Input Fields

Output Fields

Derived Fields lease_budgets

Derived fields from the /lib/PDF/lease_budgets library

This library is used to derive the GST Overridden flag on the Contract / Lease screen.

Input Fields

Output Fields

• is_gst_overridden_flag — Calculates GST from the net values of all budgets, including an adjusting value in the largest budget, and then compares with the actual GST values. If there are any discrepancies 'yes' is returned otherwise 'no'