VISTA reports, Maximizing Excel

1. VISTA reports, Maximizing Excel Andrew Henriksen RPh PBM/CMOP

2. Objectives • Learn how to set up a text extract • Import a VISTA report into Excel • Review methods to analyze data

3. 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

4. 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…)

5. Creating a text extract using reflection

6. 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

7. Creating a text extract using reflection After the report has run, Turn Logging OFF (unclick the box), then Click OK

8. 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)

9. Trim the top and bottom information from the capture so that only the data you want to import remains

10. Trimmed so only the text you want to import remains Everything is in columns (dotted line reflects truncated rows for demonstration purposes)

11. Next, Open Excel. Select Data tab

12. 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

13. Import text file Select the report, click the import button

14. 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

15. 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

16. 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

17. 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

18. Import Data Select a location and click OK

19. Imported Text

20. Insert Row for a header row

21. Type in Column Namesand Add a Color, Home Tab Type in each column header name and add a background color

22. Filter Function, Data Tab

23. 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

24. 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

25. Sort function Sort any column

26. report sorted by RX Cost high to low

27. Filter by Text

28. Custom Auto Filter - begins with

29. Filtered for text beginning with gabapentin All rows beginning with “gabapentin”

30. Pivot table

31. Create Pivot Table

32. Pivot Table

33. Pivot table - select fields, drag to rows

34. Pivot table - select fields, drag to columns

35. Pivot Table - Change Value Field Settings…

36. Pivot Table - Change Value Field Settings… Summarize by, Number Format

37. Pivot table

38. Using Sheet As A Template -Refreshing The Data, Right Click, Refresh

39. Pivot Table -Refresh

40. Questions?