Custom Queries

Custom Queries can be used to get a more personalized report of room usage, and what events are happening in an exportable spreadsheet. The can also be configured to run regularly on a schedule and deliver the results to you email inbox.

Create a Custom Query

To create a query, navigate to the Query Builder:

Reports > Queries > Query Builder

Click ’New' to open the builder proper, and create your query using the fields contained in the tabs along the top of the window.

Query Builder Filter

• Filter Name
  Creates a name for your query. Note that queries should be named in a manner identifying the department they belong to

• Filter Type
  Designates the type of information being queried. This determines the fields you can retrieve information for and the aspects you filter based on.

• Owner
  Allows you to pass ownership of a query to another user.

• Return First ‘xx’ Number of Records
  This checkbox activates the number field next to it. Changes the default number of fields to retrieve to the number indicated.

Display Fields

These are the fields that will be retrieved and represented in the final spreadsheet. You may search for particular fields via the text box at the top of the list. To add a field to your query, select it in the left pane and click the > button. To remove it, select in the right pane and click <.

Filter

These are the fields you are using to distinguish data you want from data you do not. For instance, only spaces in a particular building. All filters are run sequentially. To add a a filter to your query, select it in the left pane, and click the > button. A smaller window will open asking which values you want to find in that field, as well as how they should compare. To change this value, remove the filter and re-add it.

Sort

This dictates the order in which fields will be listed in the final spreadsheet. By default, each field has a prescribed order in which it will appear. This will override the orders of listed fields while keeping those that were unchanged in their original order. Select a field from the left and click > to move it over to the right pane. Select a field from the right pane and click ‘Move Up’ or ‘Move Down’ to change their order.

Users

This tab would assign the query to a particular user, but as query access is controlled by access template in our installation, this tab has no uses for our purposes.

Chart

Chart provides multiple configurations for the appearance and labelling of the charts produced by certain query types.

Results

This tab displays the preview data obtained by clicking the ‘Preview’ button at the bottom of the window. 'Preview' must be clicked between changes to update this tab.

Schedule an Automated Query

Local Administrators can schedule a Query for automated run and delivery to either other Desktop Users' Dashboards or through email to selected addresses. To configure automated delivery:

Reports > Automated Report Delivery

From this window, select ‘new’ and complete the listed fields to set up your report to run and contact necessary parties.

• Description
  The name of your report in EMS. Ensure that you include your Department in the description to make identifying reports easier

• Automated Report Type
  These selections change the type of report being run. To run your custom Queries, select ‘Query’ and select your query from the Query drop-down menu.

• Frequency
  This dictates what days your report runs: daily, specific days of the week, or once a month.

• Send Report At:
  Dictates the time at which the report will be run and sent

• Time Zone
  Specify Eastern here. This is overrides the default setting.

• Reporting Period
  This specifies the range of dates on which the report is run with respect to the moment it is being called.

• Email From
  This should be left blank or the email may not get sent.

• Email Subject
  Whatever you want the mail to come into you inbox as.

• Email Address
  Enter the full email address of your recipient(s) here, one at a time. Then click ‘Add’ to add them to the chart. You may remove people from the delivery by selecting them from the list and clicking ‘remove’.

Click ‘OK’ to save your changes. This applies to later edits and modifications as well.