Creating Reports from SEVIS RTI and Building the Open Doors Report

1
Creating Reports from SEVIS RTI and Building
the Open Doors Report
Presented by Yanina I. Morero, University of
FloridaMaria Lourdes (Joy) M. Ira, Florida State
University

2
Background

• University of Florida
• fsaATLAS (Campus Datalink and Batch)
• Florida State University
• fsaATLAS (Batch)
• www.ufic.ufl.edu/session/SEVISandOpenDoors.ppt

3
Overview

• SEVIS Reports
• SEVIS Lists
• Comparing SEVIS and University data
• Combining SEVIS and University data
• Building the Open Doors Report

4
SEVIS Reports
5
To export data from SEVIS RTI you have to
generate a report in TEXT format. Detailed
Reports list students names - Summary
Reports count students
6
To export the SEVIS Report to Excel, you have to
select and copy the whole list
Select from menu 1. Edit - Select All 2. Edit
- Copy (Control-A)
(Control-C) www.ufic.ufl.edu/session/notepadrepor
t.txt
7
To import the SEVIS report into Excel, you paste
what you copied from SEVIS on the first cell of
your spreadsheet (A1)
Select from menu Edit - Paste
(Control-V)
8
Before you proceed, you need to erase some extra
spaces You will replace with
Select from menu Edit Replace or press Contro
l-H
9
In Find what, you type And In Replace
with, you type
Then, Click Replace All
10
Now, you do not have extra-spaces that could
affect later comparisons. You are ready to
separate the data into columns
11
First, you select the whole column. Then, you
select from the menu Data Text to Columns
12
A dialog box is going to pop-up Convert Text to
Columns Wizard
In this first step, you choose Delimited and
click Next gt
13
In the second step, you determine the Delimiter
and click Next gt
14
Finally, you click Finish in the third step.
15
Now, your data is distributed in columns!
16
With this list, you can look for duplicate
records Change of Level
(You may register the initial record in RTI as
soon as possible the old active record is going
to be deactivated and will be removed from the
Alert Active students requiring registration).
17
To find out how many records are duplicated, you
can count how many times each SEVIS ID appears in
your SEVIS list. You can do this using the
function COUNTIF
18
(No Transcript)
19
(When you press F2 Excel highlights the cells
containing the range and the criteria)
20
Now, you can copy this function to each row, but
first you need to hold your range constant you
have to add a symbol before the column and/or
row identifications (Press F4 and Excel will
automatically add the symbols) ColumnRow
21
Each result is going to tell you how many times a
SEVIS ID is on your list once (1 active or
initial) or twice (2 active and initial )
22
You can refine your list to show only the
duplicated record by using AutoFilter
Select the whole spreadsheet by clicking on the
left-top corner and go to Data Filter -
AutoFilter
left-top corner
23
Now, you can see drop-down menus in each column
header you can choose how to filter your data,
based on the information stored in each column
24
By clicking on the down-arrow, you can see your
options
25
If you select 2 in your first column, you
are going to see all the duplicate records
26
SEVIS Lists
27
SEVIS also provides other lists that you can use
individually or combine to make a more
comprehensive list of students in various types
of status.
28
Student Lists in SEVIS
29
To export one of the Students List from SEVIS,
you select the data by using your mouse. Right
click and select copy, or simultaneously press
Ctrl C.
30
To paste the data in Excel, you click with your
mouse on the cell where you want to paste the
list and right click and select paste, or
simultaneously press Ctrl v.
31
To remove all formatting, select all data columns
and copy the data. Then go to an empty cell,
right click and select Paste Special.
Select data by clicking on all data columns
32
In the Paste Special window, you check Values
and click OK.
33
All the formatting has been removed. You can use
AutoFilter to examine the list.
34
Another option, after you copy the data from
SEVIS and try to paste into Excel, is to choose
Paste Special - Indicate Text and click
OK.
35
The data is pasted with no formatting, but the
information is separated by 2 or 3
spaces.Replace these or (3 or 2
spaces) with a special character like .Then
separate the text into columns Data - Text to
Columns
36
Once you have all the lists you want in Excel,
you can compare

• SEVIS data with your University Data.
• SEVIS data --alerts and lists
• University data from term to term

37
Some applications

• Compare the list of those whose I-20s are
  Expiring (Alert Students Within 45 Days of
  Program End Date) with List of Active Students
  with Optional Practical Training to isolate those
  who have not applied for OPT. (caution not
  complete list)
• You can compare the list of those with expiring
  documents to the list of students who applied for
  graduation.
• You can compare a list of enrollment from term to
  term to isolate those not enrolled and can
  further compare this list to a list of students
  who have graduated.

38
Comparing data from SEVIS and University databases
39

• You can convert other lists to Excel files and
  work simultaneously with them to compare
  information stored in different databases
  (comparing lists).

40
Ex. This list has been generated in fsaAtlas
(ISSS database)

• Passport Last Name
• Passport First Name
• SEVIS ID
• Campus Id
• Database Status DataLink Active
• Profile Status
• Current Profile
• Profile Type
• Profile SubType
• Profile Start Date
• Profile End Date
• Major Field
• Degree Sought
• Level of Education
• Level of Education If Other
• Full-Time Status
• Current Session End Date
• Next Session Start Date

41
Sometimes, you may want to have certain
information in your list but you may not want to
see it you can HIDE/UNHIDE any COLUMN/ROW as
you wish
42
You can work with two lists, side by side and
count if one record from the SEVIS list is on
the ISSS list
Do not forget to hold the range CONSTANT
43
Your function may return different values 0,1,
2, 3, etc 0 SEVIS ID is not in our database
(possible problem! No entry in our database -
different SEVIS ID none)
44

• In a similar way, you can count if a SEVIS ID
  from the ISSS database is ACTIVE or INITIAL in
  SEVIS.
• Possible results 0, 1, 2
• 0 SEVIS ID may not exist (different program) or
  record may be in a COMPLETED, TERMINATED, or in
  DEACTIVATED status
• (good time to archive and
• update records in our database!)

45
You can also compare some specific information
like PROGRAM END DATE (I-20/DS-2019 Expiration
date) To do this, you can use the function
EXACT Are the expiration dates EXACTLY the same
in both lists?
46
The EXACT function is going to return FALSE
and TRUE results TRUE it is true that the
PROGRAM END DATES are exactly the same in SEVIS
and ISSS FALSE they are not exactly the same
check and correct. (These dates need to be
accurate. Advisers need to know students are in
status. Also, you may be using the ISSS database
to generate email alerts to notify students about
I-20/DS-2019 expirations.)
47
Other applications

• Auditing
• Majors
• Names
• Country of Citizenship

48
Combining data from SEVIS and University Databases
49
By using the Vlookup function, data from the
University list can be included on the SEVIS list
SEVIS List
University List
50
We are going to use the SEVIS IDs to lookup
(look for) the corresponding SEVIS IDs in the
University list and import the UIDs to the SEVIS
list
The University list has to have the SEVIS IDs on
its first column and has to sorted by them in
ascending order. To Sort, click from the menu
bar Data Sort And then select Sort by
SEVIS Check Ascending Check Header row And
click OK
51
It is recommended that both tables are sorted in
ascending order by SEVIS ID
52
To use Vlookup Function

• On the SEVIS list, select the cell where you want
  to import the corresponding value (i.e.
  University ID number)
• Go to Insert Function and select the Vlookup
  function from the list to indicate the arguments
  for this function
• The Lookup_value is the common value in the two
  data sets (the SEVIS ID). Select the cell that
  contains the SEVIS ID on the SEVIS list

53
Table_array is the selected range from which to
retrieve the data (i.e. the University List).
Select the whole University list or just select
from the first row the cells with the data that
you want to include and press Shift End ? to
select the whole set (bottom of the list) Press
F4 so that the Table_array is an absolute range.
!!!!!!!
54
Col_index_num is the column number from the
University table from which you wish to import
the corresponding value. The UID is in the
column number 2
55

• Range_lookup is set to 0 or FALSE to find the an
  exact match (the exact SEVIS ID)

56

• Once you click OK, the corresponding University
  ID will appear in the cell where the Vlookup
  function was inserted. If the lookup value is not
  found, the function will return the value of
  N/A.
• To find the UID for the other SEVIS IDS, copy
  this cell down until the end of the SEVIS list.

57

• In the same way, we can bring the students email
  to the SEVIS lists.
• You can apply any filter to this list and email
  to all or some of these students (by copying the
  emails in the Bcc box of a new email in Outlook)
• You can merge emails, documents, or letters from
  this list

58
Building theOpen Doors Report
59

• First, you need to build a table with all the
  information requested in the Open Doors Report
• If your office does not store the information
  for all visa types, you may request a list from
  the University Database including all non-US
  citizens.
• You may need to eliminate PR from this list and
  some other special cases (it depends on your
  University system).

60
You will need a similar list from your database
with information that is required for the open
doors report but is only stored in your
International Students database.
I.e. MARITAL STATUS (Single
Married) SOURCE OF FUNDS (Personal Funds
U.S. College/University)
61
Once you combine both lists, you will add some
columns where you will translate the information
to match the Open Doors terminology

• University Database New Columns with Open Doors
  Terminology
• CAMPUS ID
• STUDENT NAME
• ENROLLMENT (New Continuing Student)
• DOB
• NAT (coded) CITIZENSHIP
• GEN (coded) GENDER (Female - Male - Unknown)
• VISA (all visa codes) IMMIGRATION STATUS (F - J -
  OTHER)
• DEG (coded) LEVEL OF EDUCATION (Undergraduate
  Graduate Other)
• LEVEL (coded) ACADEMIC LEVEL (Undergraduate
  Freshman/Sophomore/Junior)
• COLL (coded) COLLEGE (for managerial questions)
• MAJ (coded) FIELD OF STUDY (XX Field Area
  Name)
• CREDITS (coded) ENROLLMENTSTATUS (Full-time /
  Part-time)
• MARITAL STATUS (Single Married)
• SOURCE OF FUNDS (Personal Funds U.S.
  College/University)

62
For this task, you will use the AutoFilter, Copy
and Paste tools For example To indicate
IMMIGRATION STATUS F VISA / J Visa / Other
Visa First, filter all the students with F1
Visa. Second, type F Visa for the first
student. Third, copy the cell where you just
typed F Visa. Fourth, select all the blank
cells under the cell where you typed F Visa
until the end of your list and PASTE. Now all
the students with a F1 visa have F Visa on the
Immigration Status column Do the same for J1
and J2. Then select all the blank cells using the
AutoFilter on the Immigration Status column and
fill all these cells with Other Visa Proceed
in the same way with all the other empty columns.
63

• To translate codes like College, Majors, Country,
  etc, you use tables with those codes (in
  alphabetical order and in the first column) and
  then use the VLOOKUP function to translate each
  code into its description.

64
The final list will look like this
The yellow columns contain the information in the
format we want.www.ufic.ufl.edu/session/opendoor
s.xls
65
To generate the Open Doors Reports, you have to
use Pivot Tables

• Go to Data PivotTable and PivotChart Report

66

• Leave the default selections and click next gt

67
Excel selects your whole table (you can change
the selection). Click next gt
68
Leave the default selection of New worksheet
and click Finish.
69

• The Pivot Table Wizard pops up and a new
  spreadsheet is inserted.

• You can see the area where your table is going
  to be and the list of fields for the pivot table.

70
To insert your data in this table, click over
each field you want to work with and drag it over
the table, in the area where you want it. Excel
calculates automatically the total of Campus ID
and organizes the information by Level of
Education and Immigration Status.
71
For each one of the tables you have to fill out
in the Open Doors Report, just keep dragging the
fields in and out and copy the results as values
in another Excel file.Pivot tables can also be
copied below and the selection of fields to be
included can be changed.When mistakes are
found, the original table can be corrected and
the pivot tables have to be refreshed (click the
! icon on the Pivot Table wizard)
72
Wrap Up

• SEVIS Reports
• Copy / Paste / Replace / Text to Column /
  countif / / AutoFilter
• SEVIS Lists
• Paste-Value / Special Paste-text / Replace
• Comparing SEVIS and University data
• Hide-Unhide / countif / exact
• Combining SEVIS and University data
• Vlookup / Merge
• Building the Open Doors Report
• AutoFilter-Copy-Paste / Vlookup / Pivot Tables

73
More Excel functions
To be able to compare the results of these
functions, ALWAYS Copy and Paste Special the
results in a new column as Values
74
Analyzing data and creating reports

• Based on program start date
• compare enrollment data with previous semesters.
• send notice to incoming students
• Use program end date
• send notice to students to extend program.
• send notice to graduating students (OPT, transfer
  and readmission during 60-day grace period)

• Number of active students
• Enrolled
• OPT (pending OPT data fix)
• Pending reinstatements
• E-mail listings
• Used to create and monitor listserv
• Use Netscape mail or other mailing list
  software/listserv to send bulk e-mail
• Other Resources
• Use BlackBoard or WebCT

75

• Challenge yourself
• Explore Excel Menu and Tool Bars
• ALWAYS TRY AND TEST
• USE the Help guy!
• Be curious but be patient.

76

• Copy of presentation available at
• http//www.ufic.ufl.edu/session/SEVISandOpenDoors.
  ppt