Here is how to create a query - selecting data for the query, sorting, filtering and grouping the data, to get the best results.
Select data
To select fields and add them to the Query data panel:
- Go to Enquiries > Query Manager.
- At the Data tab, do either of the following to locate the field you want:
- In the Search box, enter the criteria you want to search on, and then click Search. At the Field Search Results screen, double-click the field you want. EmpowerHR locates and selects the field in the Catalogue panel, or
- Expand the folders in the Catalogue panel until you locate the field you want, and then click it to select it
- Add the field to the Query data panel by doing either of the following:
- click Move Right, or
- double-click the field
- Repeat this procedure until you've added all the fields you want to use in the query.
- If you select any field just for filtering purposes, you can stop it appearing in the data the query returns (and displays in the query results window) by clearing the display field checkbox that immediately precedes the field name in the Query data panel.
- To change the order of fields in the Query data panel, select a field name and then click and drag it to where you want it to appear in the field list.
- See the following procedure for how to sort the data you selected for the query.
Sort data
Use the following procedure to define the sort order of data in the output results.
To set the sort order:
- In the Query data panel, click the sort field icon that appears between the display field checkbox and the field name.
- Each time you click the sort field icon, it cycles through sort ascending (up arrow), sort descending (down arrow), and unsorted (no arrow) states.
- When you run the query, EmpowerHR displays the output results sorted in the order you've chosen.
Filter data
You can apply filtering to restrict the data returned by a query.
Query Manager:
- offers standard (pre-defined) data filters. See standard filters for a list of many of EmpowerHR's pre-defined data filters.
- provides system parameters. These are filters that automatically prompt you to select data when you run the query. See standard parameters for a list of pre-defined system parameters.
- allows you to add your own user-defined data filters and system parameters. See advanced query manager for instructions.
To filter data:
- As you select the fields from the Catalogue panel on the Data tab, Query Manager automatically constructs a set of recommended filters.
- Click Filter.
- EmpowerHR displays the Filter tab, listing:
- available standard filters in the Predefined filters panel
- any recommended filters in the Recommended/Selected filters panel
- A recommended filter initially displays with a red funnel requiring selection, or a frequently used optional filter displays with a grey funnel. See the following table for the meaning of the filter icons.
| Icon | Meaning |
|---|---|
Green filter (funnel) | Selected filter |
Red filter (funnel) | Unselected filter |
Grey filter (funnel) | Frequently used filter |
Match filter (a = b) | System parameter automatically prompting you to enter selection criteria when you run the query. For example: Employee Code |
Element filter | Filter based on the Leave or Payment Code defined by the selected element. For example: Leave Accrual Pay Codes |
- To select a filter, click the filter icon. When you select a filter that satisfies a particular set (for example, restriction on employees), EmpowerHR greys out any other filters within the set to indicate the recommendation is satisfied, and sets the selected filter to green.
|
Unless all recommended filter sets are satisfied, when you run the query, EmpowerHR displays a warning message and prompt. |
- If you want to further restrict data, you can:
- select additional filters from those recommended, or
- add filters from the filter catalogue by selecting the filter and clicking Move Right
Group/total data
Grouping data allows you to aggregate or summarise data according to common characteristics. Query Manager offers a range of aggregate functions to help you gain valuable insights from large datasets, making data analysis more manageable and informative.
Query Manager supports the following aggregation functions, which are available when you click the define aggregate function icon displayed beside each field in the Query data panel on the Data tab.
- Sum
- Count
- Average
- Minimum
- Maximum
![]() |
With the exception of calculated fields, Query Manager automatically applies the group clause to any non-aggregate (and non-literal) fields. See use calculated fields for the grouping actions available for calculated fields. |
© 2023 Fusion5. All rights reserved.