Developing Custom GAIN Reports with ReportToExcel

1
Developing Custom GAIN Reports with ReportToExcel

• David Smith, B.G.S., Michael Vacca, B.Sc. and
  Dionna Christian
• Chestnut Health Systems, Bloomington, IL
• Workshop Presentation for the Joint Meeting on
  Adolescent Treatment Effectiveness
• Baltimore, Maryland
• March 29, 2006, Baltimore B Room
• Preparation of this presentation was supported by
  funding from the Center for Substance Abuse
  Treatment (CSAT Contract no. 270-2003-00006). The
  contents of this presentation are the opinions of
  the authors and do not reflect the views or
  policies of the government. Available on line at
  www.chestnut.org/LI/Posters or by contacting Joan
  Unsicker at 720 West Chestnut, Bloomington, IL
  61701, phone [phone], fax (309)
  829-4661, e-Mail junsicker_at_chestnut.org

2
This workshop will..

• Introduce the new ReportToExcel add-in for ABS
  by
• Summarizing the challenge of using GAIN data
  outside ABS.
• Demonstrating how ReportToExcel makes it possible
  to export GAIN case data to an Excel file.
• Examining several sample reports developed with
  ReportToExcel.
• Studying samples of custom report code.

3
The Challenge

• Create a tool that will make it easy for users of
  the GAIN Software tool, ABS, to develop custom
  reports or data analysis
• Based on individual or aggregate case data and
• Using readily available Microsoft Office
  applications like Word and Excel.

4
The Situation Custom Reports

• ABS is capable of generating several powerful
  reports from individual case data
• Full or Core GAIN printouts
• Personal Feedback Reports (PFR)
• S9 Grid printouts
• Individual Clinical Profile (ICP) Reports and
• GAIN Recommendation and Referral Summaries (GRRS)
  clinical narrative reports.

5
The Situation Custom Reports (continued)

• Each of these requires specialized software and
  advanced programming skills
• Crystal Reports
• Full or Core GAIN printouts
• Personal Feedback Reports (PFR)
• S9 Grid printouts
• Microsoft Access Basic
• Individual Clinical Profile (ICP) Reports and
• Microsoft Visual Basic
• GAIN Recommendation and Referral Summaries (GRRS)
  clinical narrative reports.

6
The Situation Custom Reports (continued)

• Each of these requires specialized software and
  advanced programming skills
• Chestnut has offered to train and share code, but
  few if any have actually developed or customized
  reports locally.
• Chestnuts limited resources have meant that very
  few custom reports have been built.

7
The Solution Report To Excel

• Now, with Report to Excel you can export an
  assessments worth of ABS data into an Excel
  spreadsheet.

• Once in Excel, you can write code to
• create an ad hoc report about that data
• create a graph
• write a custom narrative
• even compile multiple cases worth of information
  into an aggregate set to be used for even more
  complex reporting.

8
Hows This All Work?
Report To Excel
Excel Template
ABS
9
The Excel Template

• The Excel template will provide a location for
  all of the assessment information for one case to
  be displayed so it can be accessed for report
  building
• Once the data is loaded into the Excel template,
  it can be manipulated just like any other Excel
  data
• From the Excel template, VBA macros can be
  written not only to manipulate the data, but also
  to open other office applications and pass data
  into those applications

10
What Does the Excel Template Look Like?
11
The Excel Template

• We give you all the information that you will
  need so that when you are working with the Excel
  Template you will know
• Exactly which question from the assessment you
  are dealing with
• The answer that was recorded for that question
  during the assessment
• What that answer means within the context of that
  question (i.e. 0 No for a Yes/No question)

12
Example Reports

• Lets switch over to the actual application and
  take a look at some examples of the kinds of
  reports that Report to Excel can generate

13
GAIN-Q Scoring Sheet

• The GAIN-Q scoring sheet is a great tool for
  helping to interpret the results of a GAIN-Q
  interview
• Once you get the data from the assessment entered
  into the Excel file.
• Now it happens in just a couple clicks of your
  mouse.

14
Aggregate Data Analysis

• Aggregate Data Analysis is easy to accomplish if
  you have SPSS and know how to use it.

Wouldnt it be great if there was an easy way to
get it into a format more people were familiar
with, like Excel?
The problem is that because of the data
structure, its just never been easy to export
ABS data into an easy to use format like an Excel
spreadsheet.
Until now
15
Special Studies Questions Narrative

• The addition of the ability to add additional
  questions to an assessment was a great
  functionality improvement for many sites.
• However, many of these sites have been frustrated
  with trying to integrate this additional data
  into the existing ABS reports.
• In this example we show you how based upon a set
  of special study questions, we can generate a
  narrative in word that can be appended to the end
  of the G-RRS.

16
Code Examples

• Report to Excel will provide you with the data
  for an assessment loaded into an Excel
  spreadsheet.
• From there, you can use any of the tools
  available to you in Microsoft Office to
  manipulate that data, reformat it, run
  comparisons or calculations using it, etc.
• Our examples use combinations of Excel macros,
  Visual Basic for Applications (VBA) and Word
  templates

17
Code ExamplesSome of the Basics

• To Open another workbook in Excel
•  
• Dim myWkbk As Workbook
• Set myWkbk Workbooks.Open("C\Program
  Files\ABS\Reports\myFile.xls")
• where myFile.xls is the name of the other
  workbook file you want to open.

18
Code ExamplesSome of the Basics

• To copy data from one worksheet to another 
• myWkbk.Worksheets("Sheet1").Range("A1B25").Select
  
• Selection.Copy Destination myWkbk.Worksheets("Sh
  eet2").Range("C1") 
• where you specify the range of data to be copied
  in the first line and which worksheet to copy it
  to in the second line

19
Code ExamplesSome of the Basics

• To open MSWord from Excel 
• Dim appWord as Object
• Dim docDoc as Object
• Dim strTemplate as String 
• strTemplate "C\Program Files\ABS\Reports\myTemp
  late.dot
• Set appWord CreateObject("Word.Application")
• Set docDoc appWord.Documents.Add(strTemplate) 

20
Code ExamplesSome of the Basics

• To Search named ranges in Excel and send values
  to Word bookmarks 
• Dim wb as Workbook
• Dim xlName as Range.Name 
• For each xlName in wb.Name    
• If docWord.Bookmarks.Exists(xlName.Name)
  Then           
• docWord.Bookmarks(xlName.Name).Range.Text
  Range(xlName.Value)    
• End If
• Next xlName

21
Report To Excel

• Questions?