OP Reports Tutorials: Other Useful Tables

Indirectly-Accessible Data

In OP’s Staff Directory (Utilities -> Manage Practice -> Staff/Provider Directory), you can see all ID numbers and initials of active and inactive staff members:

When you need reports that include staff member data (like “Which of my nurses gave the most vaccines last month?” or “How many transactions per day do each of my billing staff process?”), you will need to know both the staff initials and ID, since some OP tables use one and some use another.

If the data field that designates a staff member is “1127,” but you want the report to read “KLH” or “Kristel Hassler, CPNP,” you’ll need to join to the STAFF1 table:

Code Tables

Many different lists of codes used in OP — adjustment reasons, status of labs, VIS choices, etc. — are found in the CODE_TABLE. CODE_TABLE.CODE_ID has a unique index number, and CODE_TABLE.DESCRIPT has the text explanation you see when working in OP:

Many different parts of OP reference CODE_ID to indicate a list option. Here are a few of the codesets you might want in a report :

Let’s say you want a report of all active patients and their races. You’d need to join REGISTER to CODE_TABLE, like this:

SELECT REGISTER,FNAME, REGISTER,LNAME, CODE_TABLE.DESCRIPT FROM REGISTER INNER JOIN CODE_TABLE ON (CODE_TABLE.REF_ID = REGISTER.RACE) WHERE (REGISTER.STATUSPAT = “ACTIVE”) AND (CODE_TABLE.GROUP_ID = “25”)

You can read more about “Joins” in Module 3.

Computed Fields

Ages and Aging

Any age — either a patient’s age, or for financials (e.g. how old is this balance?) — is the difference between two dates. To compute any age correctly, it’s essential to make sure you are selecting two correct dates.

Database conventions say that more recent dates are “bigger.” To find children born after 1/1/2001, search for DOB > 1/1/2001. To find the time elapsed between two dates, you can subtract:

[More Recent Date] – [Older Date]
…to get the difference in days.

For example, consider the question “How many two-year-olds did we see during 2013?” (By two-year-olds, we mean “have achieved 2nd birthday but not yet 3rd birthday.”

• We saw John (DOB 2/5/11) on 12/31/13. He was two years old on the date of service, but now he’s three years old.
• We saw Mary (DOB 2/5/12) on 12/31/13. She was one year old on the date of service, but now she’s two years old.
• If we want to know how many kids were 2 years old at the time we saw them, our search will subtract the dates (DOS – DOB).
• If we want to know how many kids we saw in 2013 who are now 2 years old, our search will subtract the dates (today’s date – DOB).

Similarly, consider the question, “How many days old is this family balance?” You might be interested in separating claims into <30 / 31-60 / 61-90 buckets, for example.

Let’s say you saw patient Jonah on 2/1/13. You billed his primary insurance later that day. You received an EOB processed by his primary insurance on 3/1/13. A few days later, 3/4/13, you posted a partial payment from his primary insurance, created a secondary claim, and sent the secondary claim. On 5/1/13, the secondary insurance paid a tiny amount. On 5/5/13, you posted this payment and turned the remaining amount over to patient responsibility. Your practice sends paper statements once a month, which are prepared on the last business day of the month (5/30/13). Jonah’s mom receives a statement on 6/4/13 showing the amount she owes.

If today is 8/1/13, how old is mom’s balance?

• (Today – original DOS): about 6 months old ?
• (Today – patient responsibility assigned date): about 3 months old ?
• (Today – statement date on which the responsibility first appeared): about 2 months old?
• Some other method?

From a patient collections perspective, you might want to use the two-months-old formula, since most practices won’t assess a late fee on old claims which insurance is still processing. But from an accounting or cash flow perspective, you are still owed money for services rendered 6 months ago. You must decide what method you want to use for the report you are writing.

Other Useful Tables

Hey, what’s in THIS table?

If you’re curious to know what’s in many OP tables and fields quickly, you can create a mini-Data Dictionary report for that table. In OP Reports, in the Data/Table tab (see the TABLE section in the next tutorial), select the table of interest; in the Fields tab, tick the “All Fields” check box in the lower right side of the window, and then click the Preview tab then the Report Data button. This will dump all the contents of that OP table to the screen. By looking at those patient records in OP, working back and forth, you can figure out what data piece comes from where.

You may find it helpful to create a Data Dictionary subfolder within OP Reports to keep these “exploratory” reports together.

Another option to see quickly what’s in an OP table and its fields is the Data Viewer. In OP (not OP Reports), select Tools, then Database Viewer.

You can scroll through the Data Dictionary by looking at the tables and descriptions on the left, and their component fields on the right. Here the table AAP_ACTIONPLANS (“Patient asthma action plans”) is shown, where you can see that the fields ID, PATNO, P_ADDR_ID, PATIENTWEIGHT, etc. are component fields of that table.

The Data Grid tab shows a random part of the database table (to show you quickly how data looks where it’s stored); the Data Details tab shows a random row with the field name next to the contents of the field.

On the SQL tab, in the upper window, type a test SQL and click Run. You can quickly check SQL scripts in this window, and save the results in a .CSV file.