400 likes | 489 Views
VISTA reports, Maximizing Excel. Andrew Henriksen RPh PBM/CMOP. Objectives. Learn how to set up a text extract Import a VISTA report into Excel Review methods to analyze data. Select a Report in VISTA. Clinic Costs Division Costs by Drug Drug Costs
E N D
VISTA reports, Maximizing Excel Andrew Henriksen RPh PBM/CMOP
Objectives • Learn how to set up a text extract • Import a VISTA report into Excel • Review methods to analyze data
Select a Report in VISTA 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 Request Statistics Select Cost Analysis Reports Option: High Cost Rx Report
High Cost RX report Select Cost Analysis Reports Option: High cost Rx report Beginning Date: 0813 (AUG 13, 2011) Ending Date: 0813 (AUG 13, 2011) Dollar Limit : (0-9999): 30// 0 to capture all fills DEVICE: HOME// ;255;999999 to print to terminal; 255 characters per row; 999999 lines/pages long (don’t hit return yet…)
Creating a text extract using reflection Create a Notepad document and give it a name If saving to the same file each month, you can overwrite or append as desired
Creating a text extract using reflection After the report has run, Turn Logging OFF (unclick the box), then Click OK
Screen Capture / Log File DEVICE: HOME// ;255;999999 LINUX-IN Fills That Cost at Least $0 for the Period: AUG 13,2011 to AUG 13,2011 Page 1 Run Date: AUG 16,2011 Rx # Drug QTY Un.Cost Total Cost -------------------------------------------------------------------------------- 4944862 PHENAZOPYRIDINE HCL 200MG TAB 45 0.089 4.01 *4873953 CATH SET,INTERMIT 12FR MMG/ONEIL#RLA1223 200 2.750 550.00 *4503842B CROMOLYN NA 100MG/5ML ORAL CONC 1440 0.343 493.92 *2814739J ADULT DIAPERS (BELTED) ONE SZ. (EA) 120 0.280 33.60 4805602A ATTENDS BRIEF PULL-ON YOUTH/SMALL 160 0.490 78.45 4944865 TABLET CUTTER 1 1.180 1.18 ............................................................................ 4944945 PREDNISOLONE ACETATE 1% OPH SUSP 5 0.110 0.55 *4128186C CATHETER FOLEY 24FR 5CC ROCHESTER #19224 4 2.292 9.17 4944950 SERTRALINE HCL 100MG TAB 30 0.000 0.00 %30358554 MELOXICAM 7.5MG TAB 20 0.016 0.32 %20468922 HCTZ 25MG/LOSARTAN 100MG TAB 15 0.059 0.89 %4702312A ALCOHOL PREP PAD 100 0.009 0.92 %4942673 HYDROCHLOROTHIAZIDE 25MG TAB 15 0.007 0.11 -------------------------------------------------------------------------------- No. of Fills = 452 Total Cost = 7,937.08 -------------------------------------------------------------------------------- (* indicates a refill, % indicates a partial)
Trim the top and bottom information from the capture so that only the data you want to import remains
Trimmed so only the text you want to import remains Everything is in columns (dotted line reflects truncated rows for demonstration purposes)
Select Get External Data, From Text Note: Tool bar may appear different on your screen, you may need to expand your window for “From Text” selection to appear or select from a drop down box
Import text file Select the report, click the import button
Text Import Wizard Step 1 of 3 Delimited - when you have a comma or tab to use to identify rows Fixed width - when your data is already in neat columns without overlap
Text Import wizard Step 2 of 3 You may need to play with where the columns need to be as some rows have more characters than others
Text Import wizard Step 2 of 3 (continued When the columns are where you want them, click next (you may have to import a few times at first to determine where the spaces should be
Text Import wizard Step 3 of 3 For this report we can just click finish, others we may want to make some formatting changes here
Import Data Select a location and click OK
Type in Column Namesand Add a Color, Home Tab Type in each column header name and add a background color
Format Columns, Home Tab Click on the column header “D” and “E”, right click and Choose Format Cells… or Click number format from the work bar
Type in Header row names, format columns & Freeze Panes Choose the row below and or to the left of the column you want to freeze
Sort function Sort any column
Filtered for text beginning with gabapentin All rows beginning with “gabapentin”
Pivot Table - Change Value Field Settings… Summarize by, Number Format
Using Sheet As A Template -Refreshing The Data, Right Click, Refresh