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: (309) 827-6026, fax: (309) 829-4661, e-Mail: junsicker@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. • Chestnut’s 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 assessment’s 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. Excel Worksheet Page Different Excel Spreadsheet Word Document PowerPoint Presentation FrontPage Web Page Access Table Any Microsoft Office Product that you want to use to create a report. How’s 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. We give you the Variable Name The Response Type Code which relates to which answer in the Response Type was recorded The Response that was recorded or the actual answer to the question The Response Type of the question to tell you what type of question it was The Response Label which puts the response into context within the response Type The Short Text of the question The Long Text of the question 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 Let’s 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. Wouldn’t 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, it’s 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 Examples…Some 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 Examples…Some of the Basics To copy data from one worksheet to another  myWkbk.Worksheets("Sheet1").Range("A1:B25").Select Selection.Copy Destination:= myWkbk.Worksheets("Sheet2").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. Define the Variables you will need Define Word as the application you want to open Open the correct template in Word Define the name of the Word template to open Code Examples…Some 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\myTemplate.dot“ Set appWord = CreateObject("Word.Application") Set docDoc = appWord.Documents.Add(strTemplate) 

20. Define the Variables you will need If xlName's name exists in document, then put value in place of bookmark Set up a loop to check through all the named ranges on a workbook Loop through to check the next named range until all the named ranges are checked Code Examples…Some 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?