Using Data Tools to Improve Your Practice Management Reporting

This might sound familiar to you.

"Doctor, you are my boss and revenue creator.  My job is to help you create revenue by being more productive and more efficient and then to capture that revenue.  But the primary tool I have at my disposal – our practice management system – limits me.  It keeps me from doing my job of helping you do your job." 

Sound like a copout?  It is…and it isn't.  Most practice management (PM) and EHR systems make it very hard to extract usable information.  You can run lots of canned reports, but in our experience, these reports are difficult to customize and rarely provide what you need.  Fortunately, there are ways for you and your management team to get around these limitations.  This article provides an overview of how to break free of canned reports. 

Why should it matter to you?  Because analyzing your data will improve your profitability, as well as improve the care and service your patients receive.  We can do a better job of filling unfilled appointments, reducing no-shows and late cancellations, seeing who is referring to us (and who has stopped or slowed down), analyzing accounts receivable, and evaluating productivity…the list is endless.  The ability to slice and dice all of the information your PM/EHR tracks is already available.  You need two tools that are already in your practice. 

The first tool is Microsoft Excel.  Exporting data from your practice management system into Excel permits you to manage the data rather than just look at it.  Excel has a tool, PivotTable, which should be every practice manager's best friend.  It takes Excel and its analytical capabilities to a new level.

The second tool is Microsoft SQL Server, the backbone of virtually all practice management (PM) and electronic health record (EHR) systems.  It is a relational database management system.  Disparate data fields (e.g., patient names, appointment types, referring physician addresses) come together using Microsoft SQL Server.  It is the means by which we can go break free of the limited canned reports our PMs and EHRs offer.

Here's a comparison to show the difference in capabilities between these tools and what the EHR/PM systems offer out of the box:

• In the EHR/PM system, I can run a canned report that will show me the number of new patients referred by a doctor to our practice for a given period.  What I see is what I get, and if it isn't what I want, I need to tweak the report parameters and try again…ugh.  If I want to run the report for another time period, I have to run it again.

• I can export the canned report into Excel and sort the data, be it from most referrals to least or something else.  I can sort referrals by referring doctor and then by location or some other permutation.  Again, though, I am limited to what is in the canned report. 

• I can create a PivotTable using the data I exported into Excel.  Now, I can easily slice and dice the information (e.g., I only want to look at referrals from Dr. Smith to Drs. Jones, Friedman, and Ramsey) and take a deep dive at a more granular level.  Again, though, I am limited to the data contained in my original canned report.  The data remains fixed in time, so changing the date parameters requires running new canned reports.

• Finally, I can use SQL Server and pull data to create PivotTables that contain only the data fields I want.  I can trend referrals from Dr. Smith on a month-to-month or year-to-year basis without running and combining multiple reports, I can let her know if patients she referred are not showing up for their recommended care, or I might call her if I notice her referrals have dropped to ask if there's something we can do to improve our service or care.  Because our SQL Server is updated every night when our PM does its back-up, the data in this PivotTable is updated every time I open it.  

I thought I was hot stuff downloading reports into Excel.  It was only upon learning PivotTables and harnessing the power of our SQL Server that I realized just how primitive and limited I was.  If you want to get as much out of your PM and EHR as you put into it - and we put tons of clinical and non-clinical data into ours every day - explore going to the next level with PivotTables and SQL Server. 

First, learn how to use PivotTables.  Nate Moore, CPA, FACMPE, a practice administrator turned data junkie, is the pre-eminent authority.  He has a set of free set of PivotTable tutorials here.  The tutorials build upon one another and are an excellent resource for both the novice and advanced PivotTable user.

Second, consider purchasing Better Data, Better Decisions: Using Business Intelligence in a Medical Practice .  This book is the bible for those wanting to use PivotTables and SQL Server tools in their medical practices.