How to Use Query Builder

Allows users to create simple queries against defined data models for easy reporting.

 

PERMISSIONS

MODEL AND PAGE PERMISSONS

Before your staff can use Query Builder, you will first need to set permissions. While you can add
required permissions to an existing Permission Group from Admin >Task Pad > Security
Administration > Permissions, it is best practice is to create a new Permission Group. After you
create the Permission Group, you will need to add Permissions from Admin > Task Pad > Security
Administration.

1. Add DICreateSystemQuery which grants permissions to the module

 

2. Add DICreateSystemQuery which grants permissions to the page

 

3. You will then need to grant access to the desired Data Modules by Permission Group from Admin >
Categories > Permissions.

 

  •  Select a Query Models.
  •  Select Permission Group(s).
  •  Click the Save button.

 

DASHLET PERMISSIONS

You will also want to set permission for both the My Queries and Company Queries Dashlets.

 

CREATING QUERIES

The Query Builder provides a simple and intuitive solution for querying IRIS Practice Engine data.


1. Select Reporting > Query Builder.
2. From the Data Model panel, select the Data Model that you want to work with from the list. Any
existing reports are displayed in the My Queries panel. Refer to Query Models for a complete list
of available fields. 

3. Click the New button.
4. Enter a Query Name and description.
5. Click the Save button.

 

6. Select the Columns to display. The columns available for selection will be limited to those in the
data model chosen for this query.

 

7. For each column added, select the double-arrow icon to sort results if desired. Optional

• Ascending or Descending
• Move to the first – Sort by this field first.
• Move to the previous – Sort by this field prior to another.
• Move to the next – Sort by this field next.
• Move to the last – Sort by this field last.

 

8. For each column added, select the f icon to apply count, row number, or min/max results.
Optional

 

• Sum – Displays the sum of column at the bottom of each group, together with a grand total
at the end of the result table.
• Count – Display the count of column at the bottom of each group, together with a grand
total at the end of the result table.
• Average – Displays the average of the column group (where applicable), together with the
average of the whole column at the end of the result table.
• Minimum – Displays the minimum of the column group (where applicable), together with
the minimum of the whole column at the end of the result table.
• Maximum – Displays the maximum of the column group (where applicable), together with
the maximum of the whole column at the end of the result table.

9. Repeat for all the columns to be included in the query.

 

After adding the columns to display, apply conditions to filter the data. Conditions are used to limit
the results of the query. Each condition consists of a column (such as Client Name), an operator
(such as is in the list), together with one or more values.


Whichever option is selected will affect how conditions are connected.

• All – Means all conditions are true.
• Any – Mean Records can include one or more of the selected conditions.
• None – Means that all conditions are false.
• Not All – Means not all conditions are true, at least one of them is false.

 

1. Click the Add new condition icon, to add a condition.

2. Set the condition value. Condition values will vary.

3. Repeat the following processes for each desired condition. Each subsequent condition is added
as a separate line in the Conditions panel.

 

COMPLEX CONDITIONS

If you want to add a more complex set of conditions, click the Add group condition icon.

 

ENABLE/DISABLE CONDITIONS

There may be occasions when you want to enable/disable conditions to alter the query results. This
function is useful if you don't want to permanently delete the condition from the query and/or want
to see the impact of removing the condition. Click the Toggle enable icon the enable/disable the
condition.

 

AGGREGATE COLUMN DATA

Data can be aggregated in each applicable column of the results. Various aggregate options are
available. Hover over the required column until the icons appear, then click the Aggregate Column
icon. Select the required option from the drop-down list.

 

Sum
Displays the sum of this column at the bottom of each group, together with a grand total at the end
of the result table.

 

Count
Displays a count of this column at the bottom of each group, together with a grand total at the end
of the result table.

 

Distinct Count
Displays the sum of this column at the bottom of each group, together with a grand total at the end
of the result table.

 

Average
Displays the average of the column group (where applicable), together with the average of the
whole column at the end of the result table.

 

Minimum

Displays the minimum of the column group (where applicable), together with the minimum of the
whole column at the end of the result table.

 

Maximum

Displays the maximum of the column group (where applicable), together with the maximum of the
whole column at the end of the result table.

 

Remove the Aggregate
An aggregate can be removed from a column by hovering over the column and clicking the
Aggregate Column icon.

 

RUN THE QUERY

 

EXPORTING

Once you have achieved the required results for your query, you may want to export the date for
further manipulation, such as using a spreadsheet package. Data can be exported as CSV, HTML, PDF
or specifically for Excel.

1. Click the Export icon.
2. From the Export result window, select the Format of the file from the list. Click OK.
3. The file is then generated, then automatically downloaded to your browser.

 

MAXIMIZE

If you have multiple columns in your query, you may want to maximise the display space by clicking
the Maximise icon.