Transcript Developing Custom GAIN Reports with ReportToExcel David Smith, B.G.S., Michael Vacca, B.Sc.

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. 2702003-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: [email protected]
1
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.
2
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.
3
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.
4
The Situation – Custom Reports (continued)
Each of these requires specialized software and
advanced programming skills:
-
Crystal Reports:
o
o
o
-
Microsoft Access Basic:
o
-
Full or Core GAIN printouts;
Personal Feedback Reports (PFR);
S9 Grid printouts;
Individual Clinical Profile (ICP) Reports; and
Microsoft Visual Basic
o
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:
-
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.
6
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.
7
How’s This All Work?
ABS
Report To Excel
Excel Template
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.
8
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
9
What Does the Excel Template Look Like?
We give you the Variable Name
The Response
Type
Code Label which puts
Thewas
Response
Type of
theResponse
question
The Response that
The
which
relatestype
tothe
which
answer
The Short Text recorded
of the question
to answer
tell
you what
ofresponse
question
or the actual
into context within
in
the
Response
Type
was
The Long Text of the question
it
was
to the question
the response Type
recorded
10
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)
11
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
12
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.
13
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…
14
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.
15
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
16
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.
17
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
18
Code Examples…Some of the Basics
To open MSWord from Excel
Dim appWord as Object
Define
Word
asofthe
Define
the
Variables
Open
the
correct
Define
the
name
the
Dim docDoc as Object
application
you
want
you
willin
need
template
Word
Word
template
to
open
Dim strTemplate as String
to open
strTemplate = "C:\Program
Files\ABS\Reports\myTemplate.dot“
Set appWord = CreateObject("Word.Application")
Set docDoc = appWord.Documents.Add(strTemplate)
19
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
Set
up a the
loop
toexists
check
IfDefine
xlName's
name
in
Variables
through
named
document,
then
put
value in
you all
willthe
need
placeon
of bookmark
ranges
a workbook
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
Loop through to check the
next named range until all the
named ranges are checked
20
Report To Excel
Questions?
21