You will need permission to the Ad-hoc tool in order to use it. If you were given permission, you can access it from the dashboard menu.

A data model can be thought of as the source of the data you will be reporting on. It can encompass many things, and your organization may have one or many to choose from. Select the data model that contains the data you would like to report on and click the Load button.

1. Result Columns - This is where you will select the columns that you want to appear on the report

2. Sort Columns - Here you can dictate how the resulting rows will be sorted.

3. Query Conditions - Here you can add rules about what should and should not appear on the report.

4. Update Result - Display the results of the query in a grid in the space below the query builder.

5. Export Result - Export the results of the query into a CSV file which can be opened with Excel.

Above are some additional action buttons that appear on the bottom-right-hand portion of the ad-hoc page.

1. Actions - This button can be used to save a query after you have built or load a previously built query into the Ad-hoc tool. This will be covered more in depth later.

2. Sql - Clicking this button will display a pop-up that contains the SQL query that was build based on what was created using the query builder.

3. Query Text - Displays a pop-up with a summary of the conditionals added in the query builder above. For example, if you added a conditional to report demographic information for only contacts with the gender "Male" it would read something like "Demographic Gender = Male"

To add a column to the result columns list, click the green text "[Click here to add new column]." You will be presented with a list of fields that are available based on the data model you selected. Click on the field you would like to appear as a column in the report, and it will be added to the list.

By default, the column will be added as a Simple Column, given a default name based on the pattern {DataModel} {FieldName}, and not be considered a sorted column. An example of a newly added column appears in the form of "PatientApplications PatientLastName" as shown above.

You can change the name of the column by clicking on it. You will be presented with a text box, enter the desired name and click enter and the name of the column will be changed. In the example above, the column from the field "PatientApplications PatientFirstName" has had the column name changed to be simply "First Name"

You can also add a result column as a sort column by clicking the text "Not Sorted" and choosing if this column is sorted in Ascending or Descending order. In the example above, you can see that the column "First Name" has been added as a sort column in ascending order. Note that this will add the field to the "Sort Columns" section of the query builder (not shown).

The above menu is reached by clicking the downward arrow located next to a column.

1. Sorting - An alternative way to make this column a sort column. You can choose between Ascending and Descending.

2. Move Up - Move the selected column up on the list of columns displayed in the Result Columns window.

3. Move Down - Move the selected column down on the list of columns displayed in the Result Columns window.

4. Delete Column - Remove the selected column from the list of result columns.

5. Aggregate Function - Change the selected column from a simple column to an aggregate column. Aggregate functions are covered in more detail below.

6. Simple Column - Change the selected column from an aggregate column to a simple column.

Aggregate functions allow you to extrapolate specific information about a field. For example, you can use Maximum to get the highest value entered, or Minimum for the opposite. In the example above, the column will display the latest date an application was entered.

When a column is changed to an aggregate column, the aggregate function will be displayed before the column source. You can click on it to change the function being applied to the column to another applicable function. Here is a list of some of the functions that may be available depending on the kind of data

1. Count - Total count of the field entered. This is not a distinct count. You may want to use this for something like "total applications entered by a contact"

2. Maximum - Available for number and date based columns. It will display the highest number or latest date available

3. Minimum - Available for number and date based columns. It will display the lowest number or earliest date available

4. Sum - Available for number based columns, it will add up all data in the field and display the total

5. Average - Available for number based columns, it will calculate the numerical average for the field selected

To add a column to the Sort Columns list, click the green text "[Click here to add new column]." Sort columns do not have to appear in the result list in order to appear in the sort list. You can sort columns in both Ascending and Descending order. This can be changed by clicking the blue text that reads either "Ascending" or "Descending."

Note that the resulting rows are sorted with priority in the order that they appear on the list, so in the above example, because first name appears first, last name sorting will only be applied if there are multiple results with the same first name but different last names.

Similar to the Result Columns menu, you can click the down arrow to perform the following actions on a column:

1. Ascending - Set the column to sort in ascending order

2. Descending - Set the column to sort in descending order

3. Move up - Move the column up on the sort list

4. Move down - Move the column down on the sort list

5. Delete sorting - Remove the selected column from the sort list.

To add a condition to the list, click the green text "[Click here to add new condition]." Conditions are broken into three parts. The field that the condition is applied to, the check that is being made, and the value that is being applied to the check. Clicking on the check or the value will allow you to change them. In addition, check boxes next to each condition can be unchecked to disable the condition when filtering the results without removing it completely.

In the example above, the conditions are set to includes rows where the applicant is Male and the status of the application is neither Approved or Denied.

The check that is being made consists of a conditional phrase that is always self-explanatory. Some examples are:

1. Is (not) equal to - includes rows where the field and the value are (or are not) the same

2. contains - usually reserved for text based fields, includes rows where the field includes the vaue, but may contain other text as well.

3. does not contain - usually reserved for text based fields, includes rows where the field DOES NOT contain the value provided.

4. is (not) in list - includes rows where the field is (or is not) in the list of values

5. Starts with - usually reserved for text based fields, includes rows where the fields begins with the included value. Note that this is different from contains because for this, the value must be the first thing in the field in order to pass the check

6. does not start with - usually reserved for text based fields, includes rows where the field starts with something that is NOT the value supplied.

7. is greater than (or equal to) - reserved for numeric and date based fields, includes rows where the field is greater than (or equal to if selected) the supplied value

8. is less than (or equal to) - reserved for numeric and date based fields, includes rows where the field is less than (or equal to if selected) the supplied value

9. is (not) between - reserved for numeric and date based fields, includes rows where the field is (or is not) between the two values supplied

10. (Month | Year | Day) is - reserved for date fields, includes rows where the (Month | Year | Day) of the field date is the same as the (Month | Year | Day) in the date supplied as a value.

In the query builder, all conditions are contained within a Condition Bracket. These brackets can set a broader condition for all the conditions within it, as each bracket contains a conditional statement that can be changed.

The Query Conditions has one over-arching bracket that will contain all conditions and any additional brackets required. This bracket cannot be deleted, but the conditional it uses can be changed if needed. The default conditional it uses is "all" meaning that all conditions within the bracket must be met for the row to be included in the results.

To add an additional condition bracket, click on the down arrow next to a condition to open the menu and select "Add new bracket after current." The new bracket will be added and can contain additional conditions and can even nest additional brackets if needed.

When dealing with nested brackets, indentation will be used automatically to show which bracket the condition is in. When a bracket is opened, a default condition is automatically added. To add additional conditions to a nested bracket, open the menu for a condition item within the bracket and select "Add new condition after current."

The conditionals that can be applied to brackets are as follows:

1. all - all conditions in the bracket must be met in order for the row to be included

2. any - at least one condition in the bracket must be met in order for the row to be included

3. not all - at least one, but not all, conditions in the bracket need to be met in order for the row to be included

4. none - none of the conditions in the bracket should be met in order for the row to be included

Using the "Actions" button in the bottom right-hand corner of the screen, you will see a pop up that will allow you to save and upload any queries that are build with the ad-hoc tool.

Saving a built tool is as simple as clicking the save button. Depending on the browser you are using, the behavior may vary. Using firefox, the file will automatically save to the "Downloads" folder with the name "query.xml" This file can be renamed and moved to a more suitable location after the save. If using Internet Explorer you will be given the option to "Save As" which will allow you to select the destination and name of the file without having to go into the downloads folder.

To load a query that was previously saved, click the browse button, navigate to and select the desired query, and click Load. Remember to Load the data model before trying to load the query, or the query will not load correctly.