# Presentation: 436829

## 216H – Extracting and Exporting Pharmacy Data

- (Absolute Power)

## 		Extracting and Exporting Pharmacy Data 	(Absolute Power)

- Presenters:
- Brett Vess, MBA CCP
- Health Systems Specialist
- Asheville, VAMC, North Carolina
- Mike Martinko, R.Ph.
- Pharmacy Applications Coordinator
- VAMC Chillicothe, Ohio

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 		(Absolute Power) Class Description

- This Pharmacy sponsored hands-on class focuses on Pharmacy VistA files and file structures to extract data. The class also provides an overview on methods of exporting data into Excel, as well as tips and tricks on how to arrange the data to present user friendly information.

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 	(Absolute Power)

- How to have
- Absolute
- Power
- over your pharmacy output data

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 		(Absolute Power) Class Objectives

- Differentiate between Fileman output reports and VistA reports available in the various Pharmacy software packages.
- Illustrate possible uses of several Pharmacy VistA “canned” reports as they may pertain to possible user scenarios.
- Offer a hands-on exercise by importing a VistA fixed-width report into Excel.

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 		(Absolute Power) Class Objectives

- Utilize numerous Excel tools to enhance and complement the displayed data.
- Import a Fileman delimited output file into Excel for use as a mail merge.
- Complete an example of a possible drug recall patient letter using mail merge.
- Present an overview of the Provider Profiling Program developed for VISN 5.

- 216H - Extracting & Exporting Pharmacy Data

## 	Defining Delimited Reports and	Fixed Width Reports

- Importing into Excel converts text into a spreadsheet requires breaking text into columns and rows.
- Delimited
- Text is separated by a “delimiter,” usually an  “@”  or  “^”  to set column width.
- Example: Fileman report output.
- Fixed Width
- Columns are set by the user, and require the text to be lined up vertically.
- Example: “Canned” VistA report output.

- 216H - Extracting & Exporting Pharmacy Data

## 	VistA Software Package Reports 	(“Canned” Reports)

- Explain when a ‘canned’ report will suffice over a fileman search
- Advantages of canned reports
- Faster
- Less work intensive for user
- Less stress on system resources
- Disadvantages of canned reports
- May not print in optimum order
- May have unwanted spaces or columns
- May include undesired information

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 	(Absolute Power)

- Each pharmacy software package has its own set of output reports.
- Each set of package reports has its own characteristics, advantages, and limitations.
- Outpatient can be prescriber-specific
- IV can be patient-specific
- Unit dose can sort or print using multiple criteria

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 	(Absolute Power)

- Scenario #1: Outpatient drug cost and quantity monitoring requested for gross errors.

- 216H - Extracting & Exporting Pharmacy Data

## 	Outpatient Reports: Output Reports	Cost Analysis Reports

**	**
**Select Output Reports Option: ****Cost Analysis Reports**
**   ****	****  ****Clinic Costs**
**          ****Division Costs by Drug**
**          ****Drug Costs**
**          ****Drug Costs by Division**
**          ****Drug Costs by Division by Provider**
**          ****Drug Costs by Provider**
**          ****High Cost Rx Report**
**          ****Patient Status Costs**
**          ****Pharmacy Cost Statistics Menu ...**
**          ****Provider by Drug Costs**
**          ****Provider Costs**
**          ****Request Statistics**

- 216H - Extracting & Exporting Pharmacy Data

## 	Outpatient Reports: Output Reports	Cost Analysis Reports

**	**
**Select Output Reports Option: Cost Analysis Reports**
**   ****	****  ****Clinic Costs**
**          ****Division Costs by Drug**
**          ****Drug Costs**
**          ****Drug Costs by Division**
**          ****Drug Costs by Division by Provider**
**          ****Drug Costs by Provider**
**          ****High Cost Rx Report        ****<<<<<<<<<<<**
**          ****Patient Status Costs**
**          ****Pharmacy Cost Statistics Menu ...**
**          ****Provider by Drug Costs**
**          ****Provider Costs**
**          ****Request Statistics**

- 216H - Extracting & Exporting Pharmacy Data

## 	Output Reports: Cost Analysis Reports	High Cost Rx Report

**	**
**Select Cost Analysis Reports Option:  ****High Cost Rx Report**
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Output Reports: Cost Analysis Reports	High Cost Rx Report

**	**
**Select Cost Analysis Reports Option:  High Cost Rx Report**
** **
**Beginning Date: ****6/1/07****  (JUN 01, 2007)**
** **
**Ending Date: ****6/30/07****  (JUN 30, 2007)**

- 216H - Extracting & Exporting Pharmacy Data

## 	Output Reports: Cost Analysis Reports	High Cost Rx Report

**	**
**Select Cost Analysis Reports Option:  High Cost Rx Report**
** **
**Beginning Date: 6/1/07  (JUN 01, 2007)**
** **
**Ending Date: 6/30/07  (JUN 30, 2007)**
**Dollar Limit :  (0-9999): 30// **

- 216H - Extracting & Exporting Pharmacy Data

## 	Output Reports: Cost Analysis Reports	High Cost Rx Report

**	**
**Select Cost Analysis Reports Option:  High Cost Rx Report**
** **
**Beginning Date: 6/1/07  (JUN 01, 2007)**
** **
**Ending Date: 6/30/07  (JUN 30, 2007)**
**Dollar Limit :  (0-9999): 30//   ****400**

- 216H - Extracting & Exporting Pharmacy Data

## 	Output Reports: Cost Analysis Reports	High Cost Rx Report

**	**
**Select Cost Analysis Reports Option:  High Cost Rx Report**
** **
**Beginning Date: 6/1/07  (JUN 01, 2007)**
** **
**Ending Date: 6/30/07  (JUN 30, 2007)**
**Dollar Limit :  (0-9999): 30// 400**
**DEVICE: HOME//**

- 216H - Extracting & Exporting Pharmacy Data

## 	Output Reports: Cost Analysis Reports	High Cost Rx Report

**Fills That Cost at Least $400 for the Period: JUN  1,2007 to JUN 30,2007Page 1**
**Run Date: JUL 23,2007**
** **
**Rx #       Drug                                       QTY  Un.Cost    Total Cost**
**--------------------------------------------------------------------------------**
** ****0000000   PEGINTERFERON ALFA-2B 150MCG/0.5ML SYR       4   120.930       483.72**
** ****0000000   EPOETIN ALFA,RECOMB 20,000UNT/ML INJ         4   103.635       414.54**
** ****0000000A  ENOXAPARIN 80MG/0.8ML INJ SYRINGE 0.8ML     20   25.243        504.86**
** ****0000000   OXCARBAZEPINE 600MG TAB                    180    2.377        427.84**
***0000000   CINACALCET HCL 60MG TAB                     90   11.778       1059.99**
***0000000   ETANERCEPT 50MG/ML INJ SURECLICK             8   170.938      1367.50**
** ****0000000   PEGINTERFERON ALFA-2B 120MCG/0.5ML SYR       4   119.840       479.36**
** ****0000000C  RISPERIDONE 37.5MG/VIL INJ SA SUSP           2   244.730       489.46**
** ****0000000A  OLANZAPINE 10MG TAB                         90    6.444        579.96**
** ****0000000   PEGINTERFER ALFA-2A 180MCG/0.5ML SYR 4PK     1   479.140       479.14**
** ****0000000A  QUETIAPINE FUMARATE 300MG TAB              180    2.420        435.51**
** ****0000000B  OLANZAPINE 20MG TAB                         90   11.589       1043.01**
***0000000   ETANERCEPT 50MG/ML INJ SURECLICK             4   170.938       683.75**
***0000000A  QUETIAPINE FUMARATE 300MG TAB              180    2.420        435.51**
** ****0000000   LOPINAVIR 200MG/RITONAVIR 50MG TAB         180    3.580        644.40**

- 216H - Extracting & Exporting Pharmacy Data

## 	Output Reports: Cost Analysis Reports	High Cost Rx Report

- Sort by drug name, then by cost or amount dispensed to find possible outliers

- 216H - Extracting & Exporting Pharmacy Data

## 	Output Reports: Cost Analysis Reports	High Cost Rx Report (in Excel)

- 216H - Extracting & Exporting Pharmacy Data

## 	Output Reports: Cost Analysis Reports	High Cost Rx Report (in Excel)

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 	(Absolute Power)

- Scenario #2: To assist with purchasing drugs ahead (end of year funds become available), identify high cost/high volume outpatient dispense drugs for local fills only.

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**Select Pharmacy Coordinator Menu Option: ****Manual Pharmacy Statistics**

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**Select Pharmacy Coordinator Menu Option: Manual Pharmacy Statistics**
**The Pharmacy Benefits Management (PBM) report will extract**
**statistics from one or more of the following files:**
**                   ****(files are listed below)**
**Is this the monthly report?  **

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**Select Pharmacy Coordinator Menu Option: Manual Pharmacy Statistics**
**The Pharmacy Benefits Management (PBM) report will extract**
**statistics from one or more of the following files:**
**                   ****(files are listed below)**
**Is this the monthly report?   ****NO**

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**Select Pharmacy Coordinator Menu Option: Manual Pharmacy Statistics**
**The Pharmacy Benefits Management (PBM) report will extract**
**statistics from one or more of the following files:**
**                   ****(files are listed below)**
**Is this the monthly report? n  NO**
** **
**Select Start Date:**

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**Select Pharmacy Coordinator Menu Option: Manual Pharmacy Statistics**
**The Pharmacy Benefits Management (PBM) report will extract**
**statistics from one or more of the following files:**
**                   ****(files are listed below)**
**Is this the monthly report? n  NO**
** **
**Select Start Date: ****5/1/2007****  (MAY 01, 2007)**
** **
**  ****Select End Date:**** 6/30/2007****  (JUN 30, 2007)**
** **

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**Select Pharmacy Coordinator Menu Option: Manual Pharmacy Statistics**
**The Pharmacy Benefits Management (PBM) report will extract**
**statistics from one or more of the following files:**
**                   ****(files are listed below)**
**Is this the monthly report? n  NO**
** **
**Select Start Date: 5/1/2007  (MAY 01, 2007)**
** **
**  ****Select End Date: 6/30/2007  (JUN 30, 2007)**
** **
**Do you want a copy of this report sent to you in a MailMan message? NO//  **** YES**

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**Select one or more of the following:**
**1.   IVs**
**2.   Unit Dose**
**3.   AR/WS**
**4.   Prescription**
**5.   Procurement**
**6.   Controlled Substances**
**7.   Patient Demographics**
**8.   Outpatient Visits**
**9.   Inpatient PTF Records**
**10.  Provider Data**
**11.  Allergies/Adverse Events**
**Vitals/Immunizations Information**
**The Provider Data report may take an extended amount of time to run.**
**It is recommended that it be run during off peak hours.**
**Select the code(s) associated with the data requested:**

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**Select one or more of the following:**
**1.   IVs**
**2.   Unit Dose**
**3.   AR/WS**
**4.   Prescription**
**5.   Procurement**
**6.   Controlled Substances**
**7.   Patient Demographics**
**8.   Outpatient Visits**
**9.   Inpatient PTF Records**
**10.  Provider Data**
**11.  Allergies/Adverse Events**
**Vitals/Immunizations Information**
**The Provider Data report may take an extended amount of time to run.**
**It is recommended that it be run during off peak hours.**
**Select the code(s) associated with the data requested:   ****4**

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**You have selected:  4 - Prescription**
**                    ****13 - Laboratory Results**
**                    ****Patient Demographic Summary**
** **
**Print Summary Only? NO// **

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**You have selected:  4 - Prescription**
**                    ****13 - Laboratory Results**
**                    ****Patient Demographic Summary**
** **
**Print Summary Only? NO//   ****NO**

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**You have selected:  4 - Prescription**
**                    ****13 - Laboratory Results**
**                    ****Patient Demographic Summary**
** **
**Print Summary Only? NO//**
** **
** **
**This report will automatically run as a background job.**
**REQUESTED TIME TO RUN: NOW//**

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**You have selected:  4 - Prescription**
**                    ****13 - Laboratory Results**
**                    ****Patient Demographic Summary**
** **
**Print Summary Only? NO//**
** **
** **
**This report will automatically run as a background job.**
**REQUESTED TIME TO RUN: NOW//    **** T@2000****     (AUG 23, 2007@20:00)**

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

**You have selected:  4 - Prescription**
**                    ****13 - Laboratory Results**
**                    ****Patient Demographic Summary**
** **
**Print Summary Only? NO//**
** **
** **
**This report will automatically run as a background job.**
**REQUESTED TIME TO RUN: NOW//    **** ****T@2000     (AUG 23, 2007@20:00)**
**Subj: PBM TIMING for report MAY 01, 2007 to JUN 30, 2007 from 538 CHILL**
**[#20095178] 07/24/07@15:09  7 lines**
**From: PHARMACIST,ONE   In 'IN' basket.   Page 1**
**--------------------------------------------------------------------------------------------------------------**
**Prescription                 JUL 24,2007@13:54   JUL 24,2007@15:04   ****1 hrs, 10 min**
**Laboratory Results      JUL 24,2007@15:04   JUL 24,2007@15:09   ****0 hrs,  5 min**
**---------------------------------------------------------------------------------------------------------------**

- 216H - Extracting & Exporting Pharmacy Data

## Manual Pharmacy StatisticsPrescription (Outpatient)

-  								        **Total     Total Qty**
**Drug Name                                      Partials    Fills  Refills     Cost     Dispensed**
**------------------------------------------------------------------------------------------------**
**A & D OINT                                         1        3        0         3.84       240.00**
**ABSORBASE TOP OINT                                 0        2        0         9.08       908.00**
**ACARBOSE 25MG TAB                                  0        1        0        94.77       270.00**
**ACARBOSE 50MG TAB                                  0        5        1       209.25       555.00**
**ACETAMINOPHEN 325MG TAB                            1       65        8        54.62     13006.00**
**ACETIC ACID 0.25% IRRG SOLN                        0        1        0         4.00      2000.00**
**ACETYLCYSTEINE 10% PF INHL SOLN                    0        0        1         8.51        90.00**
**ACYCLOVIR 200MG CAP                                0        7        1        33.86       875.00**
**ACYCLOVIR 400MG TAB                                0       10        0        33.62       675.00**
**ACYCLOVIR 5% OINT                                  1        3        0       240.40        75.00**
**ADALIMUMAB 40MG/0.8ML INJ PEN KIT *#               1        5        0      6606.80        20.00**
**ADALIMUMAB 40MG/0.8ML INJ SYRINGE KIT *#           0        0        2      2612.04         8.00**
**ALLKARE PROTECTIVE BARRIER PAD C#0374-44 #         0        0        1         9.40       100.00**
**ALLOPURINOL 100MG TAB                              1       14        4        54.81      1755.00**
**ALLOPURINOL 300MG TAB                              0        5        1        23.00       420.00**
**ALOE VESTA 3-N-1 CLEANSING TOP FOAM *#             0        1        0         6.86       480.00**
**ALOH/MGOH/SIMTH REG STRENGTH CHEW TAB              0        1        0         5.28       100.00**
**ALOH/MGOH/SIMTH XTRA STRENGTH SUSP                 0       25        1        55.86        42.00**
**ALOSETRON HCL 0.5MG TAB *#                         0        1        0       245.54        60.00**
**ALPRAZOLAM 0.5MG TAB                               0       48       25       490.06     11112.00**

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 	(Absolute Power)

- Scenario #3: Need to identify dispense drugs by quantity dispensed for inclusion in inpatient unit dose packaging hardware

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Supervisor’s Menu 	Management Reports

**Select Supervisor's Menu Option: Management Reports Menu**
** **
** **
**          ****AMIS (Cost per Ward)**
**          ****Drug (Cost and/or Amount)   **
**          ****PRovider (Cost per)**
**          ****Service (Total Cost per)**
**          ****Total Cost to Date  (Current Patients)**

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Supervisor’s Menu 	Management Reports

**Select Supervisor's Menu Option: Management Reports Menu**
** **
** **
**          ****AMIS (Cost per Ward)**
**          ****Drug (Cost and/or Amount)   ****<<<<<<<<<<<**
**          ****PRovider (Cost per)**
**          ****Service (Total Cost per)**
**          ****Total Cost to Date  (Current Patients)**

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE:**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: ****6/30/07****  (JUN 30, 2007)**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: **
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ****DISPENSE DRUG**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: DISPENSE DRUG**
**Select Dispensed Drug: ALL// **
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: DISPENSE DRUG**
**Select Dispensed Drug: ALL//   ****[RETURN]**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: DISPENSE DRUG**
**Select Dispensed Drug: ALL//**
**Sort drugs by DISPENSED DRUG, COST, or AMOUNT DISPENSED: **
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: DISPENSE DRUG**
**Select Dispensed Drug: ALL//**
**Sort drugs by DISPENSED DRUG, COST, or AMOUNT DISPENSED: ****AMOUNT DISPENSED**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: DISPENSE DRUG**
**Select Dispensed Drug: ALL//**
**Sort drugs by DISPENSED DRUG, COST, or AMOUNT DISPENSED: AMOUNT DISPENSED**
**Print all drugs costing at least?   (ALL)**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: DISPENSE DRUG**
**Select Dispensed Drug: ALL//**
**Sort drugs by DISPENSED DRUG, COST, or AMOUNT DISPENSED: AMOUNT DISPENSED**
**Print all drugs costing at least?   (ALL)**
** **
**Print all drugs with a dispensing amount of at least?   (ALL)**
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: DISPENSE DRUG**
**Select Dispensed Drug: ALL//**
**Sort drugs by DISPENSED DRUG, COST, or AMOUNT DISPENSED: AMOUNT DISPENSED**
**Print all drugs costing at least?   (ALL)**
** **
**Print all drugs with a dispensing amount of at least?   (ALL)**
** **
**Select PRINT DEVICE:**

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

-  **UNIT DOSE DRUG COST REPORT         08/23/07  12:34**
**                         ****FROM 06/01/07 THROUGH 06/30/07**
** **
**                                                ****TOTAL UNITS              TOTAL**
**DISPENSED DRUG                                    DISPENSED               COST**
**--------------------------------------------------------------------------------**
** **
**  ****DOCUSATE NA 100MG CAP U/D                       6305.000            151.3200**
**  ****VALPROIC ACID 250MG/5ML SYRUP                   5099.000             56.5989**
**  ****ACETAMINOPHEN 325MG TAB U/D                     3989.000             71.8020**
**  ****OMEPRAZOLE 20MG EC CAP                          3432.000            1372.1136**
-   **CALCIUM 250MG/VITAMIN D 125U TAB U/D            2144.000             10.7200**
**  ****ASPIRIN 81MG EC TAB                             2049.000              7.9911**
**  ****DIVALPROEX 500MG 24HR (ER) SA TAB               1913.000            1239.0501**
-   **NITROGLYCERIN 0.4MG SL TAB U/D 25/BT            1728.000            111.2832**
**  ****POTASSIUM CHLORIDE 8MEQ SA TAB U/D              1627.000             13.0160**
**  ****RANITIDINE HCL 150MG TAB U/D                    1608.000             46.6320**
**  ****SODIUM CHLORIDE 0.9% INJ 2ML TUBEX              1342.000            392.4008**
**  ****SENNOSIDES 8.6MG TAB                            1334.000              8.5376**
**  ****BISACODYL 5MG EC TAB U/D                        1276.000              5.104**

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 	(Absolute Power)

- Scenario #4: Help is required defining budget dollars spent by drug in unit dose.

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Supervisor’s Menu 	Management Reports

**Select Supervisor's Menu Option: Management Reports Menu**
** **
** **
**          ****AMIS (Cost per Ward)**
**          ****Drug (Cost and/or Amount)   ****<<<<<<<<<<<**
**          ****PRovider (Cost per)**
**          ****Service (Total Cost per)**
**          ****Total Cost to Date  (Current Patients)**

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: ****6/30/07****  (JUN 30, 2007)**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: **
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ****ORDERABLE ITEM**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ORDERABLE ITEM**
**Select Orderable Item: ALL// **
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ORDERABLE ITEM**
**Select Orderable Item: ALL//  ****[RETURN]**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ORDERABLE ITEM**
**Select Orderable Item: ALL// **
**Display the dispense drugs?  Yes//**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ORDERABLE ITEM**
**Select Orderable Item: ALL// **
**Display the dispense drugs?  Yes//  ****N   (No) **
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ORDERABLE ITEM**
**Select Orderable Item: ALL// **
**Display the dispense drugs?  Yes//  N   (No)**
**Sort drugs by  ORDERABLE ITEM, COST, or AMOUNT DISPENSED:**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ORDERABLE ITEM**
**Select Orderable Item: ALL// **
**Display the dispense drugs?  Yes//  N   (No)**
**Sort drugs by  ORDERABLE ITEM, COST, or AMOUNT DISPENSED:  ****COST**
** **
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ORDERABLE ITEM**
**Select Orderable Item: ALL// **
**Display the dispense drugs?  Yes//  N   (No)**
**Sort drugs by  ORDERABLE ITEM, COST, or AMOUNT DISPENSED:  COST**
**Print all drugs costing at least?      (ALL)**
** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ORDERABLE ITEM**
**Select Orderable Item: ALL// **
**Display the dispense drugs?  Yes//  N   (No)**
**Sort drugs by  ORDERABLE ITEM, COST, or AMOUNT DISPENSED:  COST**
**Print all drugs costing at least?      (ALL)**
**Print all drugs with a dispensing amount of at least?      (ALL) **

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

**Select Management Reports Menu Option: Drug (Cost and/or Amount)**
** **
**Enter START DATE: ****6/1/07****  (JUN 01, 2007)**
** **
**Enter STOP DATE: 6/30/07  (JUN 30, 2007)**
**Select drugs by DISPENSE DRUG, ORDERABLE ITEM, or VA CLASS: ORDERABLE ITEM**
**Select Orderable Item: ALL// **
**Display the dispense drugs?  Yes//  N   (No)**
**Sort drugs by  ORDERABLE ITEM, COST, or AMOUNT DISPENSED:  COST**
**Print all drugs costing at least?      (ALL)**
**Print all drugs with a dispensing amount of at least?      (ALL) **
**Select PRINT DEVICE:**

- 216H - Extracting & Exporting Pharmacy Data

## 	Unit Dose:  Management Reports 	Drug (Cost and/or Amount)

-  **UNIT DOSE DRUG COST REPORT         08/23/07  11:56**
**                         ****FROM 06/01/07 THROUGH 06/30/07**
** **
**                                                ****TOTAL UNITS              TOTAL**
**ORDERABLE ITEM                                    DISPENSED               COST**
**--------------------------------------------------------------------------------**
** **
**  ****ENOXAPARIN INJ                                   333.000            6303.1640**
**  ****QUETIAPINE TAB                                  3222.000            5153.9513**
**  ****RISPERIDONE INJ,SUSP,SA                           20.000            4490.6000**
**  ****RISPERIDONE TAB                                 1635.000            4431.4697**
**  ****OLANZAPINE TAB                                   395.000            2277.9825**
**  ****EPOETIN ALFA INJ,SOLN                             22.000            2002.9837**
**  ****DIVALPROEX TAB,SA,24HR (EXTENDED RELEASE)       2433.000            1419.6981**
**  ****OMEPRAZOLE CAP,EC                               3432.000            1372.1136**
**  ****MEMANTINE TAB                                   1009.000            1341.6724**
**  ****CLOPIDOGREL TAB                                  486.000            1290.1842**
-   **TIOTROPIUM CAP,INHL                               60.000            1150.8000**
**  ****ZIPRASIDONE CAP,ORAL                             383.000            1148.1012**
**  ****DONEPEZIL TAB                                    351.000            984.8007**

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 	(Absolute Power)

- Scenario #5: A medication (LORATADINE 10MG TAB) is identified as having an increase in dispensed quantity.
- Hands-on Exercise: Identify the provider(s) who may be changing their prescribing practice.
- “Drug Costs by Provider” for Loratadine July 2007

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 	(Absolute Power)

- Presenter:
- Brett Vess, MBA CCP
- Health Systems Specialist
- Asheville, VAMC, North Carolina

- 216H - Extracting & Exporting Pharmacy Data

## Hands On – LoratadineImporting into Excel

- 216H - Extracting & Exporting Pharmacy Data

## Excel Tips and Tricks Covered

- Importing text files
- Columns and Rows Principles
- Fix Headers and Rows
- Copy and Paste
- Copy and Paste Special
- Find and Replace
- Fill down
- Sum Functions
- Average Function
- Divide Function
- Concatenate Function

- Formatting Cells
- Sort/ Filter
- Intro to the power of  Pivot Charts

- 216H - Extracting & Exporting Pharmacy Data

## Background FilemanDrug Recall

- Select PHARMACY CHIEF, SUPVR AND APPL COORD MENU Option: file Manager for Users
- Select File Manager for Users Option: search File Entries
- OUTPUT FROM WHAT FILE: **PRESCRIPTION****//**
-   -A- SEARCH FOR PRESCRIPTION FIELD: **STATUS**
-   -A- CONDITION: **EQUALS**** **
-   -A- EQUALS: **ACTIVE**
-   -B- SEARCH FOR PRESCRIPTION FIELD:    **STATUS**
-   -B- CONDITION:    **EQUALS**
-   -B- EQUALS: **SUSPENDED**

- 216H - Extracting & Exporting Pharmacy Data

## Background FilemanDrug Recall

-  -C- SEARCH FOR PRESCRIPTION FIELD: **DRUG**
-      1   DRUG
-      2   DRUG ALLERGY INDICATION
-      3   DRUG ALLERGY INGREDIENTS     (multiple)
-      4   DRUG EXPIRATION DATE
- CHOOSE 1-4: **1  DRUG**
- -C- CONDITION: **CONTAINS  **
- -C- CONTAINS: **METFORMIN**
- -D- SEARCH FOR PRESCRIPTION FIELD:
- IF: AC    STATUS EQUALS "0" (ACTIVE)
-                  and DRUG CONTAINS (case-insensitive) "METFORMIN"
- OR: BC     Or STATUS EQUALS "5" (SUSPENDED)
-                  and DRUG CONTAINS (case-insensitive) "METFORMIN"
- OR:

- 216H - Extracting & Exporting Pharmacy Data

## Background FilemanDrug Recall

- -D- SEARCH FOR PRESCRIPTION FIELD:
- IF:   **AC**    STATUS EQUALS "0" (ACTIVE)
-                  and DRUG CONTAINS (case-insensitive) "METFORMIN“
- OR: **BC**     Or STATUS EQUALS "5" (SUSPENDED)
-                  and DRUG CONTAINS (case-insensitive) "METFORMIN"
- OR:

- 216H - Extracting & Exporting Pharmacy Data

## Background FilemanDrug Recall

- STORE RESULTS OF SEARCH IN TEMPLATE:
- SORT BY: RX #// **PATIENT**
-      1   PATIENT
-      2   PATIENT INSTRUCTIONS
-      3   PATIENT STATUS
- CHOOSE 1-3: **1  PATIENT**
- START WITH PATIENT: FIRST//
-   WITHIN PATIENT, SORT BY:
- FIRST PRINT FIELD: **PATIENT;X**
-      1   PATIENT
-      2   PATIENT INSTRUCTIONS
-      3   PATIENT STATUS
- CHOOSE 1-3: **1  PATIENT**

- 216H - Extracting & Exporting Pharmacy Data

## Background FilemanDrug Recall

- THEN PRINT FIELD: **"^“**
- THEN PRINT FIELD: **PATIENT:**     (NOTE: This is a colon, not semicolon)
-   THEN PRINT PATIENT FIELD: **STREET ADDRESS [LINE 1];X  **
-   THEN PRINT PATIENT FIELD: **"^"**
-   THEN PRINT PATIENT FIELD: **STREET ADDRESS [LINE 2];X**
-   THEN PRINT PATIENT FIELD: **"^"**
-   THEN PRINT PATIENT FIELD: **CITY;X**
-      1   CITY
-      2   CITY (CIVIL)
-      3   CITY (VA)
- CHOOSE 1-3: **1  CITY**
-   THEN PRINT PATIENT FIELD: **"^"**
-   THEN PRINT PATIENT FIELD: **STATE;X**
-      1   STATE
-      2   STATE (CIVIL)
-      3   STATE (VA)
- CHOOSE 1-3: **1  STATE**

- 216H - Extracting & Exporting Pharmacy Data

## Background FilemanDrug Recall

-  THEN PRINT PATIENT FIELD: **"^"**
-   THEN PRINT PATIENT FIELD: **ZIP CODE;X**
-   THEN PRINT PATIENT FIELD: **"^"**
-   THEN PRINT PATIENT FIELD:
- THEN PRINT FIELD:
- Heading (S/C): PRESCRIPTION SEARCH// METFORMIN SEARCH
- STORE PRINT LOGIC IN TEMPLATE:
- DEVICE: 0;132;99999999999

- You will go up to Tools and do a Capture on the Information.  Go to Options, Display, go to the tab that says VT Specific and change the columns from 80 to 132.  Print will become smaller.

- This will run on your screen.  Then go back up to Tools and End the Capture

- 216H - Extracting & Exporting Pharmacy Data

## Background FilemanDrug Recall

-  THEN PRINT PATIENT FIELD: **"^"**
-   THEN PRINT PATIENT FIELD: **ZIP CODE;X**
-   THEN PRINT PATIENT FIELD: **"^"**
-   THEN PRINT PATIENT FIELD:
- THEN PRINT FIELD:
- Heading (S/C): PRESCRIPTION SEARCH// METFORMIN SEARCH
- STORE PRINT LOGIC IN TEMPLATE:
- DEVICE: 0;132;99999999999

- You will go up to Tools and do a Capture on the Information.  Go to Options, Display, go to the tab that says VT Specific and change the columns from 80 to 132.  Print will become smaller.

- This will run on your screen.  Then go back up to Tools and End the Capture

- 216H - Extracting & Exporting Pharmacy Data

## Hands-On Mailmerge Letter

- 216H - Extracting & Exporting Pharmacy Data

## 		Extracting and Exporting Pharmacy Data 	(Absolute Power)

- Go ahead....
- Ask questions...
- Make My Day.....

- 216H - Extracting & Exporting Pharmacy Data

## VISN 5  Provider Profiling for Pharmacy Introduction

- David Brett Vess, MBA
- Pharmacy Data Mgr.
- VISN 05

## 	VISN 5  Provider Profiling 	for Pharmacy - MARTINSBURG

- Established > 7 years ago (Pre-VISN data mart)
- #1 Highest in Nation in Pharmacy Cost/Unique
- Distributed through Chief of Staff; Clinical Service Chiefs
- Key Features
- Includes 3 data points: Cost per Pharmacy Unique, Cost per Rx, Rxs per Unique
- 3 month simple moving averages
- Detailed List of Rxs for each Provider
- Outliers are analyzed further
- Data is used for CBOC Contract Provider Performance

- 216H - Extracting & Exporting Pharmacy Data

## 	VISN 5  Provider Profiling 	for Pharmacy - MARTINSBURG

- 216H - Extracting & Exporting Pharmacy Data

## 	VISN 5  Provider Profiling 	for Pharmacy - MARTINSBURG

**          **

- 216H - Extracting & Exporting Pharmacy Data

## 	VISN 5  Provider Profiling 	for Pharmacy - DC

- Two Types of Reports
- PuPs: Cost per 30 day equivalent Rx
- PuPs: National Initiatives
- PuPs: Cost per 30 day equivalent Rx
- Distributed by COS; Chief of Medicine
- Variation of the Martinsburg model (use of VISN data mart)
- All Rxs Converted to 30 day equivalent for a normalized monthly comparison
- Details of Rxs only for those who are outliers

- 216H - Extracting & Exporting Pharmacy Data

## 	Example:   	DC Cost per 30 day Rx

- Two T

- 216H - Extracting & Exporting Pharmacy Data

## 	Example:   	DC – Detailed Rx Report

- Two T

- 216H - Extracting & Exporting Pharmacy Data

## 	DC  PuPS	National Initiative

**PuPs: National **Initiatives
- Clinical Champions (Cardiology, Primary Care, Geriatrics)
- Includes any or all of National Initiatives
- Specific data for Clinical Service and/or Provider
- 3 Types of Reports
- Scorecard Reports
- “Not Met” Only Reports
- Trending and Cumulative Reports
- Provider Survey to assess Provider Awareness, Education Needs, Potential Buy-In
- Educational Tools developed based on survey results
- CPRS tools menu
- Pocket Guide

- 216H - Extracting & Exporting Pharmacy Data

## 	Example:   	DC – PuPs

- 216H - Extracting & Exporting Pharmacy Data

## 	PuPs Scorecard	Results

- 216H - Extracting & Exporting Pharmacy Data

## 	PuPs Scorecard	Results

- 216H - Extracting & Exporting Pharmacy Data

## 	PuPs  “Not Met”	Reports

- 216H - Extracting & Exporting Pharmacy Data

## 	PuPs  “Not Met”	Reports

- 216H - Extracting & Exporting Pharmacy Data

## 	PuPs Trends Reports	- Beta Blocker Initiative -

**$114,000 ****Annualized Cost Avoidance**

**DC**

**Primary Care**

**Sunny Clinic**

**Provider 2**

- 216H - Extracting & Exporting Pharmacy Data

## 	Example:	DC Educational Tool

- 216H - Extracting & Exporting Pharmacy Data

## 	VAMHCs MUEs		(Medication Use Evaluations)

**Medication Use Evaluation (MUE**) widely recognized for innovation and functionality
- Use of Evidence-Based Guidelines
- At the point of decision by the Provider
- Informatics based-built into the electronic health record (CPRS)
- Feedback loop built in for Providers

- 216H - Extracting & Exporting Pharmacy Data

## 		Montelukast MUE		(Medication Use Evaluation)

- 216H - Extracting & Exporting Pharmacy Data

## Appropriateness:	Montelukast

- 216H - Extracting & Exporting Pharmacy Data

## 	VAMHCs MUIs		(Medication Use Indicators)

**Medication Use Indicators (MUIs)**
- Limited # of indicators
- Performance Period data covers 1 year
- Key features:
- Rationale for indicator included
- Provider Performance for two periods (semi-annual) covering 12 months
- Shows Provider Peer Percentile ranking
- Innovative mass delivery of personalized information

- 216H - Extracting & Exporting Pharmacy Data

## 	VAMHCs MUIs		(Medication Use Indicators)

**    **

- 216H - Extracting & Exporting Pharmacy Data

## 	Important Product	Features

- Support from COS, Clinical Leadership
- Support Clinical Service Chiefs/Clinical Champions
- Provides Providers with specific to cost/utilization data
- Provides Providers with trended data over time
- Provides Providers with their specific comparison to Peers
- Used as part of Performance Appraisal Process
- Use of Evidence-Based Guidelines in Ordering
- Includes Providers lists of patient specific data to improve performance

- 216H - Extracting & Exporting Pharmacy Data

## 	What Should Be	Included?

**    **

**Performance Monitoring and Management**
**Support from COS**
**Clinical Champions/Service Chiefs**
**Provider Specific Cost/Utilization data**
**Data trended over time**
**Peer Comparisons**
**Part of Performance Appraisal Process**

- Leadership Buy-In

- Provider Buy-In

- Patient Centered; Actionable Information;
- Needed at the point of the Clinical Decision

- Closes the Loop

**Clinical Decision Support**
**Use in Evidence Based Guidelines**
**Provide Patient List/other specific ways   to improve performance at the point of clinical decision-making**

- 216H - Extracting & Exporting Pharmacy Data

## 	Real Provider	Buy-In

** **

**Provider Specific Cost/Utilization data**
**Data trended over time**
**Peer Comparisons**

- Data related to:
- Improvement in Safe/Efficient practices
- Reducing Wasteful Use

- Also, clinical decision support solutions probably work best when they minimize barriers to Provider workflow (minimize clicks, steps, and/or time away from patient care)** **

- 216H - Extracting & Exporting Pharmacy Data

## 	Features	Comparison

**    **

- 216H - Extracting & Exporting Pharmacy Data

## 	VISN 5  Provider Profiling 	for Pharmacy

- Questions  ??

- 216H - Extracting & Exporting Pharmacy Data