Set Up Sample Queries

The first query you will create will be an Employee Original Hire Date List that retrieves the following information: employee first name, employee last name, original hire date, benefit description, and employee premium. This example shows you how to group the Employee Original Hire Date List by the benefit plan description, sort the information in ascending order and add subtotals to each group and a grand total to the query. The query will be output as a Report (Landscape Layout).

1. Select Reports > Employees or Training > Secure Query. The Query List Manager dialog box opens and lists previously saved queries.
2. Click New. The Enter a Title for this New Query dialog box opens.
3. In the New Title field, enter Employee Original Hire Date. This field is required because the query name appears at the top of pages and prints on most reports.
4. Select an Access Type of Private (personal use only).
5. Click OK. The Select Data To Use in Query dialog box opens.
6. From the Choose Employee Information tab, select Demographics and HR Status.
7. From the Choose Additional Information tab, select All Benefits.
8. Click OK. The Select Fields to Show in Query dialog box opens. The Fields to pick from list depends upon the tables you have selected from the Select Data To Use in Query dialog box. The Fields to include in query list shows those fields you select.
9. From the Fields to pick from list box, double-click or press the Ctrl key to select :
   • First Name
   • Last Name
   • Original Hire Date
   • Benefit Description
   • Employee Premium

The selected fields appear in the right list box in the order in which you selected them. This is the order in which Sage HRMS displays the fields in your query. To remove a field from the Fields to include in query list, highlight it and click Remove or double-click it.

1. To change the order of the fields, move your cursor over the button to the left of the field until you see a two-headed arrow.
   1. Press your left mouse button and drag the field to the new position.
   2. Release the mouse button.
   3. The field moves to the new position and the other fields automatically reorder to accommodate the moved field.
2. Click OK. The Query Main page opens.
3. From the Output Results tab, select Report (Landscape Layout).
4. From the Group Records tab, select Benefit Description at the Group records first by this field drop-down list.
5. Clear the Count the number of records in the group check box.
6. Select the Show Detail records in the group check box.
7. From the Summarize the records for this numeric field list, select Employee Premium and click once to highlight it.
8. Select the Sum the records in this field radio button.
9. From the Sort Records tab, click the Select Order button. The Select Sorting Order dialog box opens.
10. From the Fields to pick from list, double-click Benefit Description and Last Name. Both fields automatically default to ascending order.
11. Click OK.
12. Click Run Query.
13. Because you selected Report (Landscape Layout) as the output, the Report Setup dialog box opens.
14. Select Preview.
15. Click Start Report. The Report Preview window opens and displays the report.

In this example you will copy the Employee Original Hire Date List, create a Benefit Summary report, and add a record count to the query. The query will be output as a Report (Portrait Layout).

1. From the Query List Manager dialog box, select Employee Original Hire Date and click once to highlight it.
2. Click Copy. The Enter a Title for this Copied Query dialog box opens.
3. In the To Title field, enter Benefit Summary.
4. Click OK. The Query List Manager dialog box opens.
5. Select Benefit Summary and click once to highlight it.
6. Click Open. The Query Main page opens.
7. On the Output Results tab, select Report (Portrait Layout).
8. On the Select Fields tab, click the Select Fields button.
9. From the Fields to include in query list, double-click or highlight the field and click Remove to select and remove the First Name, Last Name, and Original Hire Date fields from the query.
10. Click OK.
11. On the Group Records tab, select the Count the number of records in the group check box.
12. Clear the Show Detail records in the group check box.
13. Click Run Query.
14. Because you selected Report (Portrait Layout) as the output, the Report Setup dialog box opens.
15. Select Preview.
16. Click Start Report. The Report Preview window opens and displays the report. A column for the Record Count has been added after the Employee Premium column.

In this example you will filter the Employee Original Hire Date List to find employees with a status of either active or leave of absence and add a condition that prompts you for a date range for the original hire date when the query is run.  

1. From the Query List Manager dialog box, select Employee Original Hire Date and click once to highlight it.
2. Click Filter. The Set Record Filter dialog box opens.
3. On the Pick a Field tab, select Original Hire Date.
4. Click Next to move to the Pick an Operator tab.
5. Select Between two dates.
6. Click Next to move to the Enter a Value tab, if the system did not automatically move you to that tab.
7. Click Ask later. The Conditions List page opens and lists the previously saved condition.
8. Click Add. The Select a Connector dialog box opens.
9. Click And. The Set Record Filter dialog box opens.
10. On the Pick a Field tab, select Employee Status Code (A/T/L/D/N).
11. Click Next to move to the Pick an Operator tab.
12. Select Exactly matches.
13. Click Next to move to the Enter a Value tab, if the system did not automatically move you to that tab.
14. Select A from the drop-down list.
15. Click OK. The Conditions List page opens and lists previously saved conditions.
16. Click Add. The Select a Connector dialog box opens.
17. Click Or. The Set Record Filter dialog box opens.
18. Repeat steps 10 through 14 selecting L from the drop-down list on the Enter a Value tab.
19. Click OK. The Conditions List page opens and lists previously saved conditions.
20. From the list of conditions, select Employee Status Code (A/T/L/D/N) exactly matchesA or and click once to highlight it.
21. Click Bracket. The Insert or Remove Parentheses dialog box opens.
22. Click Insert to add parentheses. A check mark indicates the condition you selected.
23. Select Employee Status Code (A/T/L/D/N) exactly matches L and double click.
24. Click Done. If the Enter a Value tab opens, click Cancel. The Query List Manager dialog box opens.
25. Select the Employee Original Hire Date query and click once to highlight it.
26. Click Open.
27. Click Run Query.
28. The Enter a Value tab opens. Enter a From and To date for the query.
29. Click OK. The Report Setup dialog box opens.
30. Select Preview.
31. Click Start Report. The Report Preview window opens and displays the report.