Skip to main content
EMS Software, LLC

Working 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. A Query Builder function is available from the Reports menu. You can use this function to create queries for retrieving information from your EMS database. When you create a query, you can name and save the query so that you can run the query at any time that you choose. You can create a query from a newly created query, or you can create a query by copying an existing query and editing the copied query as needed. You can also edit a query, delete a query, and view and print a query's filters.

To learn more about creating Automated Reports from Queries with EMS check out Creating Automated Reports.

To create a query

1. On the EMS menu bar, click Reports > Queries > Query Builder. The Query Definitions dialog box opens. The dialog box displays all the queries that have been previously defined in your EMS database and that have a status of Active.   

emsum_Reports_small_reminder.png

Optionally, to view all queries in your EMS database, regardless of status, under Show, click Inactive.

4. Do one of the following:

• To create a query from scratch, click New.

• To create a query by editing an existing query, select the query that is to be edited, and then click Copy.

The Query Builder Filter dialog box opens. The Query Builder Filter tab is the active tab. You use the options on this dialog box to name and define the query.

emsum_Reports_image007.png

5. Enter or edit the needed information for the query. See:

Query Builder Filter tab”.

Display Fields tab”.

Filter tab”.

Sort tab”.

Chart tab”.

Users tab”.

6. Do one of the following:

• Click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

• Open the Results tab, and then click Preview to run the query immediately and view the results on the tab. You can then click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

Query Builder Filter tab

emsum_Reports_image007.png

1. On the Query Builder Filter tab, name and define the query.

Option 
Description
 
Filter Name

Name of the query filter.

Note: The name can be a maximum of 50 characters, including spaces.

 
Filter Type

The type of information that the filter is to capture. The Filter Type affects the items that are available on the Display Fields tab and the Filter tab. A Filter Type of Group allows you to capture address information that can be exported and subsequently used by other programs to create mailing labels.

Note: Only a single Filter Type, Reservation, is available in EMS Professional.

 
Display in Browser

Available only for a Filter Type of Billing or Reservations. Select this option if the query that you are creating is to be available in the Browser.

 
Open in Navigator

Available only if the Filter Type is Reservations and Display in Browser is selected. Indicate what the system should open in the Navigator when a user double-clicks on an item that is retrieved by the query.

 
Owner

Available only to administrative users.

 
Return First “xx” Number of Records
 

To limit the number of records that the query returns, select this option, and then enter the number of records in the field.

 
Inactive

By default, a query is added as an active query. Select this option to inactivate the query.

2. Continue with any other configuration for the query as needed, or do one of the following:

• Click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

• Open the Results tab, and then click Preview to run the query immediately and view the results on the tab. You can then click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

Display Fields tab

Query Builder Filter dialog box, Display Fields tab

emsum_Reports_image008.png

1. Open the Display Fields tab, and then do one of the following:

• Select the field, or CTRL-click to select the multiple fields that are to be displayed in the query results, and then click the Move button (>) to move the selected fields to the Selected list.

• In the Field Search field, enter the string by which to filter your search for available fields. Select the field, or CTRL-click to select multiple fields, and then click the Move button (>) to move the selected fields to the Selected list.

emsum_Reports_small_reminder.png

The search is limited to the exact order of characters in the string, but the string is not case-sensitive and it can be found anywhere in the search results. For example, a search string of Add returns both Added By and Date Added. As you enter the search string, the Available Fields list is dynamically updated with a list of fields that meet the search criteria.

emsum_Reports_small_reminder.png

If you are copying an existing query, then when the Display Fields tab opens, the Selected list is already populated with a list of fields. You can select one or more of these fields, and then click the Remove button (<) to move these fields back to the Available list.

2. The fields are displayed in the query results in the order in which they are listed in the Selected list. Optionally, to change the order of the fields, select a field and then click Move Up/Move Down as needed.

3. Continue with any other configuration for the query as needed, or do one of the following:

• Click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

• Open the Results tab, and then click Preview to run the query immediately and view the results on the tab. You can then click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

Filter tab

emsum_Reports_image009.png

1. Open the Filter Fields tab, and then do one of the following:

• For each field that is to define the query, select the field, and then click the Move button (>) to move the fields to the Filter Summary list.

• In the Field Search field, enter the string by which to filter your search for available fields. For each field that is to define the query, select the field, and then click the Move button (>) to move the fields to the Filter Summary list.

emsum_Reports_small_reminder.png

The search is limited to the exact order of characters in the strings but the string is not case-sensitive and it can be found anywhere in the search results. For example, a search string of Contact returns both Contact Name and1st Contact. As you enter the search string, the Available Fields list is dynamically updated with a list of fields that meet the search criteria.

For each field that you select, a dialog box opens in which you must specify the allowed values for the field. After you specify the values and click OK, the dialog box closes, and the selected field is moved to the Filter Summary list.

emsum_Reports_small_reminder.png

If you are copying an existing query, then when the Filter Summary tab opens, the Filter Summary list is already populated. You can select one or more of these fields and then click the Remove button (<) to move these fields back to the Available list. If you want to use the same Filter Summary fields in the “new” query, but with different values, you cannot change the values directly. You must move the appropriate fields back to the Available list, then select the fields again to change their values.

2. Continue with any other configuration for the query as needed, or do one of the following:

• Click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

• Open the Results tab, and then click Preview to run the query immediately and view the results on the tab. You can then click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

Sort tab

emsum_Reports_image010.png

1. Open the Sort tab, select the field or CTRL-click to select the multiple fields by which the query results are to be sorted, and then click the Move button (>) to move the fields to the Selected list.

2. Continue with any other configuration for the query as needed, or do one of the following:

• Click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

• Open the Results tab, and then click Preview to run the query immediately and view the results on the tab. You can then click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

Chart tab

emsum_Reports_image011.png

1. Open the Chart tab, and specify the type of chart that is to be used to graph the query results.

2. Manually enter any information for the chart (for example, Chart Title) as needed.

3. Optionally, do one or both of the following:

• To show a legend with the chart, select Show Legend, and then specify the legend information (alignment, header, footer, and marker style and size).

• To spell check any charting information that you manually entered, click Spelling.

4. Continue with any other configuration for the query as needed, or do one of the following:

• Click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

• Open the Results tab, and then click Preview to run the query immediately and view the results on the tab. You can then click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

Users tab

emsum_Reports_small_reminder.png

The ability to assign specific queries to specific users is available only in EMS Enterprise. It is not available in EMS Professional.

emsum_Reports_image012.png

1. Open the Users tab, and in the Available list, select the user, or CTRL-click to select the multiple users who can run this query, and then click the Move (>) button to move the selected users to the Selected list.

emsum_Reports_small_reminder.png

By default, all administrative users can run any query that any user creates. These users are listed in the Administrative list. You cannot remove any administrative users from this list. Additionally, if a user is assigned to a User Template, this template controls which queries they have access to here.

 

2. Continue with any other configuration for the query as needed, or do one of the following:

• Click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

• Open the Results tab, and then click Preview to run the query immediately and view the results on the tab. You can then click OK to close the Query Builder Filter dialog box and save the named query. The named query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

To edit a query

1. On the EMS menu bar, click Reports. The Reports menu opens.

2. On the Reports menu, click Queries > Query Builder. The Query Definitions dialog box opens. The dialog box displays, by name, any queries that have been previously defined in your EMS application. 

3. Select the query that you are editing, and then click Edit. The Query Definitions dialog box opens. All of the tabs are populated with the information for the selected query.

4. Edit the query as necessary including one or more of the following:

• Renaming the query.

• Changing the fields that are to be displayed in the query results.

• Changing the fields that define the query.

• Changing the order of the fields by which the query results are to be sorted.

• Changing the charting options for the query results.

• Changing the users who can run the query.

5. Do one of the following:

• Click OK to close the Query Builder Filter dialog box and save the edited query. The edited query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

• Open the Results tab, and then click Preview to run the query immediately and view the results on the tab. You can then click OK to close the Query Builder Filter dialog box and save the edited query. The edited query is displayed on the Reports menu under the Queries option. You can run this query at any later date when needed.

To delete a query

1. On the EMS menu bar, click Reports. The Reports menu opens.

2. On the Reports menu, click Queries > Query Builder. The Query Definitions dialog box opens. The dialog box displays any queries that have been previously defined in your EMS application. 

3. Select the query that is to be deleted, and then click Delete. A message opens, asking you if it is OK to delete the selected query.

4. Click Yes. The message closes. The query is deleted.

To view and print a query's filters

1. On the EMS menu bar, click Reports. The Reports menu opens.

2. On the Reports menu, click Queries > Query Builder. The Query Definitions dialog box opens. The dialog box displays any queries that have been previously defined in your EMS application.

3. Select the query that is to be printed, and then click Print. An onscreen preview of the selected query opens. A variety of options are available from this preview, including the options to print a hard copy of the query, to email the query, and so on.

Print preview for a query

emsum_Reports_image013.png

4. Select the option or options that best fit your working needs.

To run a query

When you run a saved query, you can run the query from the Reports menu, or you can run the query from the Query Definitions dialog box.

1. On the EMS menu bar, click Reports and on the Reports menu, click Queries, and then do one of the following:

• Click the name of the query that you are running.

• Click Queries > Query Builder and on the Query Definitions dialog box, select the query that you are running, and then click Run.

The query is run and the results are displayed onscreen in the Query Results dialog box.

emsum_Reports_small_reminder.png

The Results (lower) pane on the Query Results dialog box is an EMS browser window. See An EMS Browser Window for all the features that are available for this pane.

Query Results dialog box example

emsum_Reports_image014.png

2. Optionally, on the Query Results dialog box, do one or more of the following, and then click Get Data to rerun the query according to the edited definition.

Select the date for which the data in your EMS database is to be queried.

 Option

 
Description
 
Custom

The starting date is set to the first day of the month and the ending date is set to the last day of the month but you can edit one or both of these dates.

Note: For some queries, if you select Custom, a Use Specific Times option becomes available. If you enter the same date for the starting date and ending date, you can select Use Specific Times, and then enter a starting time and ending time to query the data in specific time range on the same 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
This Quarter
This Quarter To Date

Quarters are based on a calendar year:

• First quarter is 1/1 through 3/31.

• Second quarter is 4/1 through 6/30.

• Third quarter is 7/1 through 9/30.

• Fourth quarter is 10/1 through 12/31.

For example, if the current day’s date is 5/22/2012, and you select Last Quarter, then the starting date is set to 1/1/2012 and the ending date is set to 3/31/2012.

 
Last Year

The starting date is set to 1/1 of the previous year, and the ending date is set to 12/31 of the previous year.

 
This month

The starting date is set to the first day of the current month and the ending date is set to the last day of the current month.

 
This Year

The starting date is set to 1/1 of the current year, and the ending date is set to 12/31 of the current year.

 
This Year to Date

The starting date is set to 1/1 of the current year, and the ending date is set to the current day’s date.

Change the Starting Date, Ending Date, or both.

• In the Report Comment field, enter a comment that is to be printed on the query.

emsum_Reports_small_reminder.png

The comment can be a maximum of 255 characters, including spaces.

Click Options and change the settings for specific room filter and/or the number of records to return.

3. After you have run the query to your satisfaction, you can then do one or both of the following:

• To print the query results, click Print. An onscreen preview of the query results opens. A variety of options are available from this preview, including the options to print a hard copy of the query results, to export the query results to a .pdf, and to email the query results. Select the option or options that best fit your working needs.

• To export the query results to an Excel spreadsheet, or to an XML file, click Export, and then click Excel or XML as appropriate.