Setting up sample queries

These samples are designed to walk you through the basic steps involved in creating custom queries using Secure Query .

This sample query is 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 list is grouped by the benefit plan description. The information is sorted in ascending order. There are subtotals for each group and a grand total. The query output is a Report with Landscape layout.

  1. Select either Reports > Employees > Secure Query or Reports > Training > Secure Query.
  2. In the Query List Manager window, click New.
  3. In the Enter a Title for this New Query window for New Title, enter Employee Original Hire Date. This query title appears at the top of pages and prints on most reports.
  4. For Access Type, select Private (for personal use only).
  5. Click OK.
  6. In the Select Data To Use in Query window on the Choose Employee Information tab, select Demographics and HR Status.
  7. Click the Choose Additional Information tab, select All Benefits, and click OK.

  8. In the Select Fields to Show in Query window in the Fields to pick from list, double-click or press the Ctrl key to select the following fields:

    • First Name
    • Last Name
    • Original Hire Date
    • Benefit Description
    • Employee Premium

    The selected fields appear in the Fields to include in query list (on the right) in the order in which you selected them. This will be the order they display in your query.

    Note: To remove a field from the Fields to include in query list, highlight it and click Remove or double-click it.

  9. If you want 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.
  10. Click OK.
  11. On the Query main page on the Output Results tab, select Report (Landscape Layout).

  12. On the Group Records tab:

    1. For Group records first by this field, select Benefit Description.
    2. Clear the Count the number of records in the group check box.
    3. Select the Show Detail records in the group check box.
    4. For Summarize the records for this numeric field list, select Employee Premium and click once to highlight it.
    5. Select Sum the records in this field.

  13. On the Sort Records tab:

    1. Click the Select Order button.
    2. In the Select Sorting Order window for Fields to pick from list, double-click Benefit Description and Last Name. Both fields automatically default to ascending order.
    3. Click OK.
  14. Click Run Query.
  15. Because you selected Report (Landscape Layout) as the output, the Report Setup window opens. Select Preview.
  16. Click Start Report. The Report Preview window opens and displays the report.

This sample query copies the Employee Original Hire Date List to create a Benefit Summary report, and then adds a record count. The query output is a Report with a Portrait layout.

  1. In the Query List Manager window, select the Employee Original Hire Date query; (click once to highlight it).
  2. Click Copy.
  3. In the Enter a Title for this Copied Query window for To New Title, enter Benefit Summary.
  4. Click OK.
  5. In the Query List Manager window, select Benefit Summary and click Open.
  6. On the Query main page on the Output Results tab, select Report (Portrait Layout).

  7. On the Select Fields tab:

    1. Click the Select Fields button.
    2. Remove the First Name, Last Name, and Original Hire Date fields from the query by either double-clicking or highlighting the field and clicking Remove.
    3. Click OK.

  8. On the Group Records tab:

    1. Select the Count the number of records in the group check box.
    2. Clear the Show Detail records in the group check box.
  9. Click Run Query.
  10. Because you selected Report (Portrait Layout) as the output, the Report Setup window opens. Select Preview.

  11. Click Start Report. The Report Preview window opens and displays the report.

    Notice that a column for the Record Count was added after the Employee Premium column.

This sample query filters the Employee Original Hire Date List so only employees with a status of either active or leave of absence are included. It also has a condition that prompts you for a date range for the original hire date when the query is run.  

  1. In the Query List Manager window, select the Employee Original Hire Date query; (click once to highlight it).
  2. Click Filter.
  3. On the Set Record Filter window 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. If needed, click Next to move to the Enter a Value tab.
  7. Select Ask later.

  8. On the Conditions List page, add a new condition for employee status:

    1. Click Add.
    2. In the Select a Connector window, select And.
    3. In the Set Record Filter window on the Pick a Field tab, select Employee Status Code (A/T/L/D/N).
    4. Click Next to move to the Pick an Operator tab.
    5. Select Exactly matches.
    6. If needed, click Next to move to the Enter a Value tab.
    7. Select A from the drop-down list and click OK.
    8. On the Conditions List page, click Add.
    9. In the Select a Connector window, select Or.
    10. In the Set Record Filter window 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 L from the drop-down list and click OK.

  9. Add brackets to the condition:

    1. On the Conditions List page, select Employee Status Code (A/T/L/D/N) exactly matches A; (click once to highlight it).
    2. Click Bracket.
    3. In the Insert or Remove Parentheses window, click Insert. A check mark indicates the condition you selected.
    4. Select Employee Status Code (A/T/L/D/N) exactly matches L and double click (to place the matching ending parentheses).
  10. Click Done. (If the Enter a Value tab opens, click Cancel.)
  11. In the Query List Manager window, select the Employee Original Hire Date query; (click once to highlight it).
  12. Click Open and then click Run Query.
  13. When the Enter a Value tab opens, enter a From and To date for the query, and then click OK.
  14. In the Report Setup window, select Preview.
  15. Click Start Report. The Report Preview window opens and displays the report.