The Kareo Blog is about more than company news. We’ll be using the Kareo Blog to share tips and tricks that show you how to get the most out of Kareo. In this post, I’ll show you how to use the Kareo Add-In for Microsoft Excel to build a custom report of your top 10 referring physicians of new patients.
Before we get started, you’ll need to complete a few pre-requisite steps. First, make sure you have Microsoft Excel 2007. This is a requirement for using the Kareo Add-In for Microsoft Excel. Second, download and install the Kareo Add-in For Microsoft Excel 2007. Third, you may also want to read Section 2 of the Kareo Guide to Building Custom Reports Using the Kareo Add-In for Microsoft Excel to make sure you have security permission to access this feature.
In this tutorial, we’d like to build a custom report showing the top 10 referring physicians of new patients. Here is the general approach. We’ll start by gathering a list of charges, then we’ll filter the list of charges by procedure codes that correspond with new patient visits (i.e. codes 99201-99205), we’ll total the patients by referring physician, and finally, we’ll limit our report to just the top 10 referring physicians. Here are the steps to do this:
Step 1 – Gather the Data. The first step is to gather the data you need to prepare the custom report. Start Microsoft Excel 2007. If you have installed the Kareo Add-In for Microsoft Excel properly, you will see a Kareo button under the Add-Ins main menu tab across the top of the screen. Click on the Kareo button and select the New Query… menu item. From the Select Data to Query screen, select Charges and click Next button. From the Select Fields to Return screen, select the following fields: Patient Name, Service Start Date, Referring Provider Name, Procedure Code, Units, and Total Charges and click the Next button. From the Select Parameters and Run Query screen, enter the following parameters: Service Start Date = “07/01/2009”, Service End Date = “07/31/2009”, and Procedure Code = “99201-99205” and click the Finish button.
Step 2 – Build the Report. Now that you have the data you need, the next step is to use the custom reporting features of Microsoft Excel to build the report. Use your mouse to select cell A2 from the worksheet with the data that you prepared in step 1 above. Click on the Insert main menu tab across the top of the screen and then click on the PivotTable button. From the Create PivotTable dialog box, click the OK button. From the Pivot Table Field List on the right edge of the screen, use your mouse to drag-and-drop the ReferringProviderName field into the Row Labels box below. From the Pivot Table Field List on the right edge of the screen, use your mouse to drag-and-drop the Units field into the Values box below. From the worksheet, click on the drop-down button on the right edge of the Row Labels cell located in cell A3, then click on the Value Filters menu item, then click on the Top 10… menu item.
That’s all there is to it. Now you have a report of your top 10 referring physicians of new patients. The lessons learned in this tutorial can be applied to building any type of custom report using the Kareo Add-In for Microsoft Excel. If you’d like more information or additional tutorials on this powerful feature, read the Kareo Guide to Building Custom Reports Using the Kareo Add-In for Microsoft Excel or watch our video demonstration on Building Custom Reports with Kareo.