Web Interface > Analytics > Drag and Drop Queries

Drag and Drop Queries

The Query Builder tab in the left-hand pane of Analytics can be used in conjunction with the Query Filter tab in the right-hand pane to quickly create queries without having to write a single line of SQL.

To create a new drag-and-drop query:

Under Adhoc Tools, select the WorkSite database you would like to query from the Select Database drop-down menu.
In the left-hand pane, click the Query Builder tab to display the WorkSite table tree.
Click the + button next to one of tables to display its fields.
Add fields for inclusion in the query results.
1. Drag a field from the Query Builder tab to the Display Fields list on the Query Filter tab.
2. Repeat step one until all desired fields have been added.
Add at least one filter for the query.
1. Drag a field from the Query Builder tab to the Filter Fields list.
2. Configure values for the each of the columns in the Filter Fields list. See Filter Fields.
Click Run to execute the query.

Once a query has successfully completed, you can perform operations on all documents in the results set. See Applying Operations to Query Results for more information on performing bulk operations through Analytics.

Query Builder Tab

After selecting a database from the Adhoc Tools drop-down menu, the Query Builder tab will list the tables and columns that are available for use with the Query Filter.

WorkSite Tables

Friendly Table Name	Actual Table Name
Document Master	MHDOCS.DOCMASTER
Document Access	MHDOCS.DOC_ACCESS
Document Denial	MHDOCS.DOC_DENIAL
Projects	MHDOCS.PROJECTS
Project Access	MHDOCS.PROJ_ACCESS
Project Denial	MHDOCS.PROJ_DENIAL
Project NVPS	MHDOCS.PROJECT_NVPS
Project Items	PROJECT_ITEMS

Milan Tables

Friendly Table Name	Actual Table Name
Audit History	MILAN.AUDIT
Event Log	MILAN.EVENTLOG
WorkSite Recycle Bin	MILAN.WORKSITERECYCLEBIN

Display Fields

The Display Fields section of the Query Filter tab lists the columns that should be displayed in the query results—the equivalent of the SELECT clause in an SQL query.

Column	Description
Display Field Name	The friendly name of a field.
Sort	A drop-down menu from which you can select the sort order, Ascending or Descending, of a query based on the values of the field.
Format	A drop-down menu from which you can select the format, MM/DD/YYYY or DD/MM/YYYY, for date fields.
Delete	Clicking this icon will delete the field from the list.
Move Up/Move Down	Moves the selected field up or down the list. The order of the Display Fields list determines the order in which the columns will be displayed in the query results.

Filter Fields

The Filter Fields section lists the columns on which the query results will be filtered—the equivalent of the WHERE clause of a query.

Column	Description
Begin	Selecting this check box adds a starting parenthesis, "(", to the WHERE clause.
Filter Field Name	The friendly name of the field.
Operation	A drop-down menu from which you can select the operation to be performed on the selected field. The options are =, <, <=, >, >=, !=, LIKE, NULL, NOT NULL.
Value	The value to which the selected field will be compared. In some cases this option is presented as a drop-down menu, from which you can choose a valid value. For example, when filtering by the OPERATOR field, the option will display a list of all users.
End	Selecting this check box adds an ending parentheses, ")", to the WHERE clause.
Operator	A drop-down menu from which you can select to perform an AND or OR operation between the filter fields.
Delete	Clicking this icon will delete the field from the list.