Click to edit Master text styles. FA Web Ad Hoc Query. Second level. Third level. Fourth level Fifth level. Training Material.

Click to edit Master text styles

Second level

Third level

Fourth level Fifth level

FA Web Ad Hoc

Query

Contents

Navigation

3-6

Security

7-9

Field Options

10-15

•

Calculated Fields

11-12

•

Advanced Functions

13-15

•

Advanced Functions

13-15

Charts and Gauges

16-17

Steps to Create

18-26

Navigation – Portal Screen\Reports List

Click on

Design a New

Report to create new queries

Click on a folder

Hover to show the Delete or

Pencil to design (only available if the user has edit rights to

the query) Click on

report name to view the report a folder

to

collapse the group

Navigation – In the Query

The Report Designer is broken into Tabs that relate to the different tasks needed to create a report or query

Each tab has a button at the bottom of the screen that will lead you to the next step in the process or you can click on the Tab name.

Navigation – Tool Bar

Return to the Reports List Create a new report Save the Print the report (in pdf)* Export in csv format)* Export in RTF format* Export in Excel or Word format* Set the number of rows to

return

Save

Save the Report as a different name or in

a different location Show the SQL query for the report* Export in Open Office (.sxw) format* Export the report XML* Email the Report+

+Opens your email program and inserts a link to the report

*

Launches a window with

options to Open or Save in the chosen format

the Report

Navigation – Icons and Meanings

Delete Insert

below

Access Advanced Functions Insert

Above

Design Click to drag to

reorder a field

Get Help on a function by hovering your mouse

pointer over the button Hide the

filters Add a field to the report

Delete a field from the report

Update the Results

Security - Tables

Ad Hoc Query table views have been created with easy to understand names that relate to FA screens.

If a user does not have screen access rights to a screen, the user will not have access to the view related to that screen.

Note: Department and Location Security are not currently available for Queries.

Security – Reports – Share With

Controls for who can see the Query Reports created and what they can do are on the Misc tab. By Default, these controls are set to give Everyone Full Access to the report

If you do not wish to share the If you do not wish to share the report, choose … from the Share With drop down. At this time the only option is Everyone or No one (…)

Hint: When first creating a report, set the Share With to … until you are ready to publish it for others to run.

Security – Reports – View and Modify Rights

Use the Rights to control what a user can do with the report

Full Access allows anyone to view and modify the report. Everyone will have the pencil and red x

Read Only sets the report so that modifications to the report can only be saved by the Report Owner. Others may modify can only be saved by the Report Owner. Others may modify the report but will have to save it with a different name.

View Only sets the report so that a user can run the report, access the filter, add or remove fields from the report (for that session), print or export the report. No changes can be

saved.

Locked sets the report so that a user can run the report, but not add or remove fields from the report or the filter

None sets the report so that it does not appear in the Reports List

Field Options – Functions and Formats

The Functions and Formats available depend on the type of field (i.e. date, or number)

Date Number Date Number

Field Options – Sort, VG and A

Sort - a check in this box will sort the returned data in the field in ascending (a, b, c) order

VG – a check in this box will break the data into Visual Groups based on the data in the field

Example of Visual Groups

Arithmetic –Using the A will create a calculated field in the report grid, meaning column z = column x * column y. It will perform an arithmetic

operation using the field above and the field where you checked the "A" box and output that result as a column. Clicking in the "A" checkbox cycles

through the different operators available: division (/), multiplication(*), addition (+) and subtraction (-). Use this function if you want to calculate a value or if you want to concatenate two fields together.

Field Options – A = Calculated Fields

First example – Concatenation - The Part Short Description has been added (concatenated using +) to the same field as the Part ID

Second example – Calculation - We have “Reorder Quantity" and “Current Unit Price" fields but we also want to know the total cost to reorder the part. To accomplish this, we add

another “ReorderQuantity" field and a “CurrentUnitPrice” field. We multiply the

“ReorderQuantity” with the “CurrentUnitPrice" column and the result is placed in the second “ReorderQuantity” field which we renamed to “Reorder Cost“. The result is a calculated field with the value. (Reorder Quantity * Current Unit Price = Reorder Cost. )

Field Options - Advanced Functions

Hide this field Includes the field for the purposes of calculation, but does not

include it on the report

Sort (z-a) Sorts by this field

Italic Italicizes the values in this field

Bold Makes the values in this field bold

Width Controls the width of the field. Default is px, but you can use in

Click the gear icon next to a field to get the Advanced Field

dialogue box

Default is px, but you can use in for inches.

Label Justification L R M – controls the position of the column header

Value Justification L R M – controls the position of the values in the report

Subreport Links to another query

Drill-down style Drill down method

URL Link the field to a screen or report

Advanced Functions - Link fields

It is possible to link fields to Enterprise Portal screens using the Advanced Functions and the URL field. Anyone viewing the report could click the hyperlinked data and launch the Enterprise Portal screen, if that user has screen rights to access the screen.

Enter Url for Fleet Primary screen

Advanced Functions – Link Fields (cont.)

Example 1: Single filter syntax (Such as filtering on the Equipment ID in the Fleet Equipment Screen). Paste in the Open EP link, this syntax:

' onClick="javascript:var myWin = top.cash.openEPWindow('2024', '2024', '152:2={0}'); return false;" extra='

Example 2: Multi filter syntax (Such as filtering on the three part WO ID in the Work Order Center screen). The following is an example for opening WO Center sending in filter for WO ID where the columns are WO Loc, Year, and Number. Put in the Open EP link, this syntax:

' onClick="javascript:var myWin = top.cash.openEPWindow('2037', '2037', '188:2={0}^188:3={2}^188:4={3}'); return false;" extra='

Elements of the Syntax String:

Here is the link broken down into its parts: ' onClick="javascript:var myWin =

top.cash.openEPWindow('{WindowName}', '{ScreenID}', '{FilterPairs}={{FieldValueToSend}}'); return false;" extra='

{WindowName} - This is the IE window name, we use the screen id as the name to avoid confusion {ScreenID} - The FA Screen ID you are trying to open

{FilterPairs} - The filter fields in the form of WindowID:CtrlID=value, separated by ^ if more than one {FieldValueToSend} - This is the index of the field from the report that you wish to send the data for. 0 represents the current field, and 1 thru X represents column index from left to right.

Note: Your link has to include all single quotes and double quotes exactly as shown the examples above. Recommend that you copy/paste that syntax.

Charts

Available Chart Types are Trend, Pie, Plot, Bar, and Funnel.

Choose the type of chart, the label(s) and value(s).

Note: Charts and Gauges are placed above the

Summary at the top of the report.

Gauges

Available Gauge Styles are Radial, Radial 2 and Linear

Linear

Radial2 Radial

STEPS TO CREATE A QUERY

OR REPORT

Steps to Create – Select Tables

1. Click on Design a New Report

2. Select the Tables

3. Verify the link is correct

Steps to Create – Select Fields

5. Select the Fields

6. Change the Description

(optional)

7. Choose which field to Sort

by (optional, but recommended)

8. Choose which field to use as a Visual Group

(optional)

9. Apply any Functions, Formats or Advanced Functions (optional)

Steps to Create – Select Summary Fields

10. Enter a Title for the summary (optional)

11. Select the Fields for the Summary

12. Select one of the fields to Group by

13. Select one of the fields to Count

14. Click the check box next to Subtotals if you want a subtotal (optional)

Steps to Create – Misc

15. Enter a Title for the report (recommended)

16. Enter a Description

(optional)

17. Enter a Header (optional)

18. Enter a Footer(optional

19. Choose to Share or not

Steps to Create – Style

21. Change the report colors

22. Check the box next to

Minimize Grid Width to make the report more make the report more viewable on the screen

Steps to Create – Filter

22. Choose the Field(s) to filter on

23. Choose the Operator for the filter. “Equals (Select)” will create a drop down list of the values in that field.

24. Select the value to filter on. If you chose “Equals (Select)” pick from the list, otherwise, key in the value.

Steps to Create – Preview

Steps to Create – Save the Report

26. Give the report a Name

27. Choose a category for the report

28. Click OK

Note: Do not begin the name of your report with “FA-” All reports that begin with “FA-” may be overwritten 28. Click OK

29. Click the Report List and you will see the report in the list.

may be overwritten when your system is upgraded.

Out of the Box Ad Hoc Queries

FA-Equipment

FA-Component Replacement List Date FA-Components - Current Assigned FA-Equipment - Current Assigned FA-Equipment Count by Dept

FA-Equipment Count by Maint Class FA-Equipment Count by PM Class FA-Equipment Count by Rental Class FA-Fleet Replacement List Date FA-Fleet Replacement List Meter

FA-Fuel

FA-Comp Fuel drilldown FA-Component Fuel FA-Fleet Fuel

FA-Fleet Fuel drilldown FA-Fuel trans Alan

FA-Fuel txns by date range

FA-Shop Operations

FA-Direct Task Analysis

FA-Employee by Assigned Location FA-Fleet Replacement List Meter

FA-Operators FA-Overdue PM by Miles and Date

FA-Parts Operations

FA-DirectIssueDetailsDEPT FA-DirectIssueDetailsEQ FA-Parts Adjustments

FA-Parts Direct Issue History Single Part FA-Parts Direct Issue Summary by Dept

FA-Parts Direct Issue Summary by Equipment FA-Parts Location Inv Value by Stock Status FA-Vendor List

FA-Employee by Assigned Location FA-In ProgressWork Order Costs FA-Indirect Task Analysis

FA-My Time Sheet FA-Work Delays

FA-Work Delays Last 30 Days FA-Work Delays Last 7 Days FA-Work Delays This Month FA-Work Delays This Week FA-Work Order Listing