The Problem with Panels, part 1

“Hey, can you send me Dr. Darvin’s panel for a mailing list I’m putting together? Name, DOB, Address? Thanks.”

Seems like a simple request. And since you’re confident you know exactly what they’re asking for, you proceed on your merry way. Oh, woe is you…

So you look up Dr. Darvin’s DoctorFacility.DoctorFacilityId value (let’s say it’s 22) and you whip up a quick query…

(See this article for info on the Exclude Patients view. Also, note that your organization may use the “Responsible Provider” field in registration rather than the Primary, so you would use DoctorId in that case, not PrimaryCareDoctorId.)

You copy/paste that into Excel and ship it off, and the practice tells you they’ve never heard of half of these people! They only want his current panel. Well, this is everyone in the system who has Dr. Darvin as their PCP in Registration, what exactly are they looking for? How do they define “current”?

I find this question coming up so often that I don’t even bother running panel reports anymore until I have a rock-solid definition of “panel,” from their perspective. Because everyone seems to define it differently, and most don’t even understand that fact until you start asking for specifics. Some want everyone, per the query above. Some will receive that list and realize they only wanted patients seen in the last three years. So let’s throw that into the mix:

We’re putting Appointments in a subquery with a SELECT DISTINCT so we don’t get duplicate patient records. We could have joined to it directly and put the DISTINCT in our top-level SELECT, but I’m willing to bet this is more efficient (without actually checking the execution plan, sorry, too lazy).

But now Dr. Darvin is certain this list is missing patients. He can name a few who aren’t on this list, and he knows he’s seen them recently. Well, you ask, is Dr. Darvin set as their PCP in Registration? Oh, turns out he’s not. So what to do about that?

One option would be to have Dr. Darvin come up with a list of who he thinks is missing, tell the PSRs to fix them, and we’ll run the query again. But relying on his memory is obviously not the best solution. Maybe we can give him a list of those patients he’s seen for office visits in the past three years who are not in the original list. This isn’t directly related to our panel query above, but it will help to make it more accurate:

Now Dr. Darvin can review this list of patients he’s seen, and let the front desk know which of them should have him set as their PCP in Registration. So at least that part of it is off you now.

But guess what? Now another practice calls and they want a panel for Dr. Baris, but they only want patients age 18-65 who’ve been seen in the past year. Oh, and they only want Medicare patients. Ugh! You know what? It’s time to give them a report they can run on demand, for any provider, entering whatever parameters they want. Go get your own data from now on! 🙂

I’m going to give you two versions of the report: a printable version and one designed for export to Excel. Both run off the same stored procedure, and I’ll provide the procedure at the end of this post. Bear in mind this is an “everything they could possibly ask for” version of the report. You may decide to scale it down based on your needs. But this version allows the user to filter on provider, patients seen since a given date, age range, and financial class. They can also choose whether to show patient detail or just a count. If they need more than that, they’re clearly working too hard and need a vacation.

We’re going to need a few things before we get started. One, we’re going to need our trusty Providers View. This will allow the user to select the provider whose panel they want to report on. This view will be the data source for our main report. We’ll put the actual panel results in a subreport that calls our stored procedure. But let’s not get ahead of ourselves just yet. Go check out that link, add the view to your database and read up on how to use it as a report parameter, and then we’ll proceed.

All set? Good. Now follow the instructions in that post to create a parameter based on that view, and call it Providers.

The next thing you need is a list of your financial classes. I can’t tell you what your list is, but I can tell you how to get it:

Once you have that list, you’ll need to enter it into a string parameter in your report. Call it FinancialClasses and start entering those MedListIds and Descriptions. Enter a descriptive prompt text, and set the parameter to prompt with Description only, don’t allow custom values, and allow multiple values. If you’re using one of my reports, be sure to go in there and replace my list with yours. If you don’t want to bother with a financial class filter, be sure to go through the stored procedure and make the necessary adjustments.

The hard part is done. Now add a few more simple parameters for:

ApptsSince: A date parameter for “patients seen since this date”. Default it to some far past date in the event they don’t want to filter.
ShowDetail: A boolean to indicate whether they want to see patient detail. Probably best to default this to True.
StartAge and EndAge: Number parameters for an age range. Default these to 0 and 150 respectively.

We’ll need three formulas to translate our parameters into something we can pass to the subreport. Both the Providers and FinancialClasses parameters need to be concatenated into a pipe-delimited string. So create a @ProviderPVIDString formula and throw this in there:

Create a @FinancialClassMIdString formula and do the same with the FinancialClass parameter. Finally, because for some reason I couldn’t get the boolean parameter to work with the stored procedure, create a @ShowDetail formula and put this in there:

I don’t remember why I had to do that, but obviously I did, so save yourself a headache and assume there was a good reason for it.

We suppress all report sections except the report footer, and that’s where we’ll put our Panel subreport. Continued in Part 2!

Leave a Reply