Work with a Query

A query is the primary mechanism for retrieving information from a database. A query consists of questions that are presented to the database in a predefined format.

The Query Builder feature is available from the Reports menu. You can use this feature to create queries that retrieve information from your EMS database.

When you create a query, you can save it and run it whenever needed. You can create a query from scratch, create a query by copying an existing query, edit a query, delete a query, and view or print query filters.

Create a query

1. On the EMS menu bar, select Reports > Queries > Query Builder.

   The Query Definitions dialog opens and displays all active queries defined in your EMS database.

   To view all queries regardless of status, select Show > Inactive.

2. Do one of the following:

   • Select New to create a query from scratch.
   • Select an existing query, and then select Copy to create a query based on an existing definition.

   The Query Builder Filter dialog opens.

3. Configure the query using one or more of the following tabs:

   • Query Builder Filter
   • Display Fields
   • Filter
   • Sort
   • Chart
   • Users

4. Do one of the following:

   • Select OK to save the query.
   • Open the Results tab, and then select Preview to run the query immediately and view the results. After reviewing the results, select OK to save the query.

   The saved query appears under Reports > Queries.

Query builder filter tab

Use the Query Builder Filter tab to define the query and configure general settings.

Option	Description
Filter Name	Name of the query filter. The name can contain up to 50 characters, including spaces.
Filter Type	Determines the type of information that the query returns. The selected filter type affects the options available on the Display Fields and Filter tabs. A filter type of Group enables you to capture address information that can be exported for use in mailing labels. In EMS Professional, only the Reservation filter type is available.
Display in Browser	Available only for Billing and Reservations filter types. Select this option if the query should be available in the browser.
Open in Navigator	Available only when the filter type is Reservations and Display in Browser is selected. Determines what opens in Navigator when a user opens a query result.
Owner	Available only to administrative users.
Return First "xx" Number of Records	Limits the number of records returned by the query. Select this option, and then specify the maximum number of records to return.
Inactive	By default, new queries are active. Select this option to make the query inactive.

After configuring the Query Builder Filter tab, continue configuring the query as needed.

1. Do one of the following:

   • Select OK to save the query.
   • Open the Results tab, and then select Preview to run the query and review the results before saving.

Display fields tab

Use the Display Fields tab to select the fields that appear in query results.

1. On the Display Fields tab, do one of the following:

   • Select a field, or hold Ctrl while selecting multiple fields, and then select > to move the fields to the Selected list.
   • In Field Search, enter text to filter the list of available fields. Select one or more fields, and then select > to move them to the Selected list.

   The search is not case-sensitive and matches the character sequence anywhere within the field name. For example, searching for Add returns both Added By and Date Added.

   The Available Fields list updates automatically as you enter search criteria.

   If you copied an existing query, fields may already appear in the Selected list. To remove a field, select it, and then select <.

2. The fields are displayed in query results in the order shown in the Selected list.

   To rearrange the order, select a field and use Move Up or Move Down.

3. Do one of the following:

   • Select OK to save the query.
   • Open the Results tab, and then select Preview to review results before saving.

Filter tab

Use the Filter tab to define the criteria used to return query results.

1. Open the Filter Fields tab.

2. Do one of the following:

   • Select a field, and then select > to move it to the Filter Summary list.
   • Use Field Search to locate fields, select the required fields, and then select >.

   The search is not case-sensitive and matches text anywhere within the field name.

   For example, searching for Contact returns both Contact Name and 1st Contact.

3. For each selected field, specify the allowed values, and then select OK.

   The field is added to the Filter Summary list.

4. If you copied an existing query, fields may already appear in the Filter Summary list.

   To change filter values, remove the field from the summary list, add it again, and then specify new values.

5. Do one of the following:

   • Select OK to save the query.
   • Open the Results tab, and then select Preview to review results before saving.

Sort tab

Use the Sort tab to define the order in which query results are displayed.

1. Open the Sort tab.

2. Select one or more fields, and then select > to move them to the Selected list.

   To select multiple fields, hold Ctrl while selecting the fields.

3. Do one of the following:

   • Select OK to save the query.
   • Open the Results tab, and then select Preview to review results before saving.

Chart tab

Use the Chart tab to create a chart from query results.

1. Open the Chart tab.

2. Select the chart type to use for displaying query results.

3. Enter any chart information that is required, such as a chart title.

4. Optionally complete one or both of the following tasks:

   • Select Show Legend, and then configure the legend alignment, header, footer, marker style, and marker size.
   • Select Spelling to check manually entered chart text.

5. Do one of the following:

   • Select OK to save the query.
   • Open the Results tab, and then select Preview to review results before saving.

Users tab

The Users tab is available only in EMS Enterprise.

This functionality is not available in EMS Professional.

1. Open the Users tab.

2. In the Available list, select one or more users, and then select > to move them to the Selected list.

   To select multiple users, hold Ctrl while selecting users.

3. Administrative users automatically have access to all queries.

   These users appear in the Administrative list and cannot be removed.

   If a user is assigned to a User Template, that template may determine which queries the user can access.

4. Do one of the following:

   • Select OK to save the query.
   • Open the Results tab, and then select Preview to review results before saving.

Edit a query

1. On the EMS menu bar, select Reports.

   The Reports menu opens.

2. Select Queries > Query Builder.

   The Query Definitions dialog opens and displays the queries defined in your EMS database.

3. Select the query that you want to edit, and then select Edit.

   The Query Builder dialog opens with the current query settings loaded.

4. Modify the query as needed.

   You can perform one or more of the following actions:

   • Rename the query.
   • Change the fields displayed in query results.
   • Change the fields used to define the query.
   • Change the sort order of query results.
   • Change chart settings.
   • Change the users who can run the query.

5. Do one of the following:

   • Select OK to save the query.
   • Open the Results tab, and then select Preview to review the results before saving.

Delete a query

1. On the EMS menu bar, select Reports.

   The Reports menu opens.

2. Select Queries > Query Builder.

   The Query Definitions dialog opens and displays the queries defined in your EMS database.

3. Select the query that you want to delete, and then select Delete.

   A confirmation message appears.

4. Select Yes.

   The query is deleted.

View and print query filters

1. On the EMS menu bar, select Reports.

   The Reports menu opens.

2. Select Queries > Query Builder.

   The Query Definitions dialog opens and displays the queries defined in your EMS database.

3. Select the query that you want to review.

4. Select Print.

   A preview of the selected query opens.

   From the preview, you can print the query or email the query information.

5. Select the option that best meets your requirements.

Run a query

You can run a saved query from the Reports menu or from the Query Definitions dialog.

1. On the EMS menu bar, select Reports > Queries.

2. Do one of the following:

   • Select the name of the query that you want to run.
   • Select Query Builder, select the query, and then select Run.

3. The query runs and the results are displayed in the Query Results dialog.

4. Optionally modify the query criteria, and then select Get Data to rerun the query.

Date range options

Option	Description
Custom	The starting date defaults to the first day of the current month, and the ending date defaults to the last day of the current month. You can modify either date or both dates. For some queries, selecting Custom enables Use Specific Times. If the starting and ending dates are the same, you can specify a time range for the selected day.
Last Month	The starting date is set to the first day of the previous month, and the ending date is set to the last day of the previous month.
Last Quarter	Uses the previous calendar quarter.
This Quarter	Uses the current calendar quarter.
This Quarter To Date	Uses the current calendar quarter through the current date.
Last Year	The starting date is January 1 of the previous year, and the ending date is December 31 of the previous year.
This Month	The starting date is the first day of the current month, and the ending date is the last day of the current month.
This Year	The starting date is January 1 of the current year, and the ending date is December 31 of the current year.
This Year To Date	The starting date is January 1 of the current year, and the ending date is the current date.

Calendar quarters are defined as follows:

• First quarter: January 1 through March 31.
• Second quarter: April 1 through June 30.
• Third quarter: July 1 through September 30.
• Fourth quarter: October 1 through December 31.

Modify query results

1. Optionally perform one or more of the following actions:

   • Modify the starting date, ending date, or both.

   • Enter text in Report Comment.

     The comment can contain up to 255 characters, including spaces.

   • Select Options and modify room filter settings or the number of records returned.

2. After reviewing the results, optionally do one or both of the following:

   • Select Print to open a print preview and access available print options.
   • Select Export, and then select Excel or XML to export the query results.