This article is part of a series. At the bottom is a link to the next article. To start with an overview of the Query Tool, Click Here to go to the beginning of the series.
Landing Page
Select the Reports menu > Query Tool.
The landing page includes a button in the top right corner to Create a New Query.
The page also contains a list of saved queries, offering several options: running a saved query, exporting the results, editing the query, or deleting it. It also shows the date that the query was created, the last editor of the query, and the date of the change.
Creating a Query
- Select the Reports menu > Query Tool.
- Select Create A New Query in the top right corner.
-
Choose a Data View by selecting the drop-down list.
Below that is a "Fields" panel on the left and a "Columns" panel on the right.The "Fields" panel will show a list of available fields for the selected data view.
The "Columns" panel will show a list of the fields added to the query.
- In the Fields panel, select Add next to the field to add it to the query specifications, and it will appear on the right. To remove an added query specification, in the "Columns" panel, select Remove next to the field, and it will disappear.
The order of the fields can also be changed in the Columns panel by clicking and holding the button next to a field and dragging it up or down.
- Once all the fields have been added, select Set Filters in the bottom right corner.
- Add one or more filters. If nothing is added, it will return all profiles or records. To add a filter, select Add A Filter and choose a Filter Field.
The filter does not have to be an added field.
- Choose an operator and value. Possible operators include:
- Equals: This operator pulls data with an equivalent value. For example, Location Equals Austin, Texas. Only profiles with addresses in Austin, Texas will be pulled.
- Not Equal: This operator pulls data with values that are NOT equivalent. For example, Location Does Not Equal Austin, Texas. Only profiles with addresses not in Austin, Texas will be pulled.
- Greater Than: This operator pulls data with a value greater than the value set. For example, Company Size is Greater Than 1000 Employees. Only organisation profiles with more than 1000 employees will be pulled.
-
Less Than: This operator pulls data with a value less than the value set. For example, Company Size is Less Than 1000 Employees. Only organisation profiles with less than 1000 employees will be pulled.
Greater Than and Less Than can also be used in conjunction with Equal To, where the operator searches for data that is Greater Than or Equal To. - Contains: This operator pulls data that contains certain information, such as profile contacts that contain a certain area code in their phone number.
- Does Not Contain: This operator pulls data that does not contain certain information, such as profiles with addresses containing the province of Ontario.
-
In the Period: This operator pulls data from profiles with certain activity within a certain time period. For example, profiles with unpaid invoices from the last 30 days.
Click Here for more information about the Period options.
Examples:
- In the "Basic Profile Info" view, there is a filter field for Member Checkbox. By adding this, setting the operator to Equals, and the value to True, it will filter the query to member profiles.
- In the "Basic Profile Info" view, is a filter field for Member Since Date. By adding this, setting the operator to Greater Than Or Equal To, and the value to 1/1/2023, it will filter the query to profiles who were members since the beginning of 2023.
Forgetting to set operators and values is a common error when using the query tool. If operators and parameters are not set correctly, the data will be incorrect, or the query will not run.
For using multiple filters in the Query Tool, review this section on Advanced Filters.
- Once the filters are added, select Set Sort Order in the bottom right corner to specify one or more fields by which to sort the results. Select Add a Sorting Rule and choose a field by which to sort.
- After adding the sort order, select Run This Query in the bottom right corner, or on the left menu. This screen will show a grid with the results of the query and the number of records at the bottom right (Record Count).
Everything may not appear on one screen if there are many results. Check the bottom of the screen for more result pages.
- Before continuing, consider saving the query for future use. Select Save Query Definition in the top right corner, and enter a Name, Description, and Assignee.
If an Assignee is not set, it will be shared with other MC Trade users that have access to this tool. If the Assignee is set to yourself, it will be private.
- To export the results, select Export These Results in the bottom right corner.
- To access the profiles related to the results, select Open These Results In Profile Selector in the bottom right corner. This requires Profile ID to be a field/column selected in the query.
- If there is a need to make changes to the code directly or create a custom SQL query, select View the SQL Code on the far left.
Running a Saved Query
- Select the Reports menu > Query Tool.
- If there are any saved queries, it will display a list. Select the play button on the far left to run the query. Running the query will display the results.
- To export the data, select the Actions button on the far right and choose Export data.
- To open the results in the profile selector, select the Actions button on the far right and choose Open results in profile selector.
To change the filtering or sorting, edit the query first, with one exception: if there are any date fields to the Filters section, the system will prompt to enter any new date values prior to exporting or opening in the profile selector.
Editing a Saved Query
- Select the Reports menu > Query Tool.
- Next to the saved query, select Edit.
Deleting a Saved Query
- Select the Reports menu > Query Tool.
- Next to the saved query, select the Actions button on the far right, and choose Delete.
Next in series: Advanced Filtering for Queries