Back to Manual Table of Contents
Warning! This feature is only for very advanced users. But those power users will find this to be one of the best features of DentOffice.
You can write any report, view it in any format, and export any data you want. This unprecedented level of power and control is simply not available with any other practice management program. The following sections attempt to explain why it is so powerful and gives you a glimpse of the implications. At first glance, it just looks like different way of doing reports, but what it really does is open up your data to enable you to connect with the rest of the world. Your data is no longer locked inside a proprietary black box, but can be fully utilized as it should be. If you can imagine it, DentOffice can do it. The next paragraph gives a very quick introduction to SQL. For more information, see the section on using SQL.
Introduction to SQL: DentOffice uses an SQL (Structured Query Language) database, which is simply a set of about 50 tables. The tables are not quite like a spreadsheet in that the rows are not arranged in any particular order, so you can optionally specify what order you would like to view them in. To retrieve a table, you send a command, or query, in plain text to the database server. The database sends back a table which can be viewed or manipulated. If you use a JOIN command in the query, the table that is returned is actually a combination of data from more than one table in the database. As part of the query, you can specify the columns to display, and the order and names of columns. You can filter out rows that do not fit your criteria, and you can order the rows by whichever column you wish. You can even add columns that are formed using mathematical formulas that operate on other columns.
The simplest query looks somthing like this: SELECT * FROM fee. In English, that means to select all columns from the 'fee' table. As a side note, most of the tables are named in singular rather than plural because they describe the data a single row holds. So, the 'fee' table is not named fees because 'fee' is a more accurate description of the information contained in one row. In the example below, a much more complex query was used to return a table showing three fee schedules.
In the Main Menu, select Reports | User Query. The following window will open (blank at first):
The large text box in the upper left corner shows the query (actually only about half of it in this example) that was used to select this table. The table is long, and the scrollbar at the right can be used to view the other rows. Frequently, the table is very wide as well, in which case there would be a horizontal scroll bar at the bottom. Because queries can take time to write, and because one little typo can cause an error, it is best to save your query in the favorites list.
Once you have retrieved your table you can do a number of things with it. You can reorder the rows by clicking on the column headers. The first click orders the rows in ascending order (a small up arrow will show in the header), and a second click will sort the rows in descending order.This does not change the data in the table, but is just another way of viewing the same data.
One very useful feature offered in this window is the ability to view the table in human-readable format instead of raw format. For instance, in the table above, the raw format would not show the extra zeros on the dollar amounts. Other similar changes that DentOffice can make to the table are explained in the formatting section.
Once you have the table the way you want it, you can either print it or export it to a tabbed text file to be used in a spreadsheet program or a letter merge. Before printing, you can use the print preview to see what it will look like.
We now have a section of Query Examples which you can look through. Any of these queries may be copied and pasted directly from our website.