Beaverlog Tips: Volume 23 - March 10, 2005

User-Modifiable Reports

This topic is covered in great depth (and length) in the User Guide for The THERAPIST Pro. However, there have been some questions and a little confusion about a few things. To avoid confusion, the words "table" and "file" will be used interchangeably to refer to a database table.

Why are all files not available in the user-modifiable reports?

Good question; I'm glad you asked. The user-modifiable reports are a compromise between flexibility and ease of use. On one hand, we wanted to give you the ability to create some complex and robust reports. On the other hand, you should be able to do it without a degree in computer science with an emphasis on database theory. The THERAPIST is based on a collection of over 150 inter-related database tables. Sometimes the relationships are simple (many patient phone numbers to one patient for example) and sometimes they are complex.

An example of a complex relationship (actually a series of relationships) is payments to patients. When you receive a payment, say from an insurance company, it may cover services for multiple patients. So each payment has a header record in the Payments table for the check as you receive it. That record is related in a parent-child relationship to one or more records in the PatientPayments table (one per patient to whose account the payment is applied). Each PatientPayments record is related to the patient account record in the Patients table. This is a child to parent relationship (PatientPayments to Patients). Thus PatientPayments is a child of two parents. The payment can be further divided by applying it to one or more services for each patient. Those child records (grandchildren of both Payments and Patients) are in the PaymentApplications table. This table is also the child of the ServiceHeaders table for the services to which the payment is applied. The ServiceHeaders table is also a child of Patients. It is even more complicated than that when you start considering the relationships to providers, adjustments, authorizations, and interest just to name the most obvious.

That brings us back to the compromise we made between flexibility and simplicity. The trade-offs ended up with reports that were very flexible within certain limits and not too difficult once you understand it. The limits are that the available tables and their relationships are hard-coded into the report headings (those groups at the top level of the user-modifiable reports tree). We had to do this so the program could be written with the background processes to retrieve the appropriate records in all the related tables.

An added caveat is that sometimes a table you might expect to be available in a report is missing (responsible parties in a report based on the Patients table, for example). This is another part of the compromise. Since responsible parties are the child in the relationship to Patients and there can be multiple parties for each patient. If responsible parties were included and you were expecting to report on patients, you would get duplicate Patients records in the report for each responsible party and could, in theory, get no patients where there is no responsible party.

To accommodate some of the shortcomings resulting from the compromise, we have provided many report headings with different groups of related tables.

Why don't my reports sort in the order I specified?

The complete answer would be too complex for this article. The simple answer is that you can only use fields somewhere in the Processed Files hierarchy as sorting fields. The other files are outside of the actual reports record retrieval process and are used as lookups that occur after a processed file record has been retrieved. Additionally, records are retrieved most logically in the order in which they appear in the processed files tree, though you have some flexibility there.

Why are all fields not available in the user-modifiable reports?

This is an easy one. It was a choice we made when designing the report. We took a look at the possible fields and made choices about which ones would be useful and which would only be confusing. Many fields are used internally by the program and their contents would be meaningless in a report. Nonetheless, in version 2.5.008 of The THERAPIST Pro, we have made available all of the fields in the included tables, with only a couple of exceptions where the information would be truly without value.

What happens if the report I want cannot be done in user-modifiable reports?

From time to time, if a good case can be made, a new user-modifiable report heading can be added with just the structure needed for a particular kind of report. We evaluate the request to see how broadly useful it would be and the resources required to do it. This is the same process we go through to add any new feature to the program. Not every request results in the new feature. In the case of reports, either built-in or user-modifable, if we decide that it is a feature that should be added, we will additionally determine whether it is best done as a user-modifiable report or a built-in report. We can do much more complex things with build-in reports. For example, despite several requests for statements as a user-modifiable report, it is just not possible to make user-modifiable statements with the same features as our build-in statements. (Note that the Patient Transactions with Payment Details heading was added in version 2.5.004 with two simple statement sample reports.)

Your other option is to use the ODBC driver. With this add-on, you can export all of your data to another tool such as Access, Excel , or Crystal Reports and create whatever report these tools will allow you. Of course, it is then up to you to define the relationships between the data tables but the accompanying manual gives them to you.

Tip: Menu Speed Keys in Windows XP

By default, Windows XP hides the underlined letters of menu items. These are the keys you can press to select the menu item from your keyboard. Previous versions of Windows showed these underlined keys all of the time. You can see the underlines by holding down the Alt key. Alternately, you can change the way Windows displays these keys so that they are always shown. The best part is that it's easy to do. Close or minimize any applications you are running so that you can see your desktop. Right-click anywhere on the desktop (not on one of the icons used to run programs) and select Properties from the popup menu. Go to the Appearance tab and click on the Effects button. Uncheck the box labeled"Hide underlined letters for keyboard navigation until I press the Alt key". Click Ok on the Effects window, then Ok again on the Properties window.