User Query

Compose queries to retrieve information from the database that is not accessible through standard reports.

In the Main Menu, click Reports, User Query.

Alternatively, in Standard Reports, click User Query.

When opened from Main Menu, Report dropdown this window can remain open while navigating other areas of Open Dental. When opened from Standard Reports, this window must be closed to navigate to other areas of Open Dental.

Users must have the User Query permission to run and change variables in released user queries.
Users must have the User Query Admin permission to write, edit, run, save, and delete queries.

Running a Query

Text Box: Displays the query text. Users with the User Query Admin permission, can enter text directly in this box. For users without the permission, this box is read-only.

Favorites: Open Query Favorites.

Add to Favorites: Add the query text currently in the text box to Query Favorites.

Copy: Copy the query text in the text box to the clipboard.

Paste: Paste any text currently on the clipboard into the text box.

Submit Query: Run the query text currently in the text box. When long queries are running, the Submit button changes to Stop Execution. Click to cancel a large query that may take a long time to run.

Format

Change settings to determine how query results are displayed.

Format: Select how to view the results. The default is determined by the Default to 'Raw' format in User Query window setting in Report Setup: Misc Settings

Column Alignment: Choose the alignment of the displayed query results.

Show Row Numbers: Choose to number query rows.

Enable word wrap: Choose to wrap long text that may otherwise be cut off.

Print Orientation: Choose whether to print the report in landscape or portrait.

Query Results

Title: The title of the query in the text box, if one exists. When running a query from Query Favorites, this box is prefilled with the title saved to the favorite. Users can type into this box to label the query before printing.

Query Results: By default, the results are displayed in human-readable format. Columns vary based on the query being run. If a column header is prefaced with a $, the cells are always formatted as a dollar amount.

Once the table displays the way you want it, there are several options:

Hints:

Note:
  • To also run command queries, user must also have the Command Query permission.
  • If using Random Primary Keys, queries with CREATE TABLE or DROP TABLE syntax can only run on the computer named as the User Query Server on the Replication Setup window (Replication).


Find a Query

Security: Access to queries is controlled by user Security Permissions.

Programming Resources, Database Documentation has a list of tables and columns in the Open Dental database.