How do I create exports for the Xello Course Planner?

This guide will help you set up the automated integration between SchoolInsight and Xello, for those using Xello’s Course Planner feature. The workflow will be as follows:

1. Obtain SFTP details from your Onboarding Manager from Xello
2. Upload school information directly to the SFTP server
3. Create reports for students, courses, and grades
4. Create scheduled exports to transfer the data to the SFTP server

Please note, an Enterprise Data Access license is required to enable data integrations.  If this is not on your current subscription, please reach out to us to add this license and gain access. 

Data needed from Xello

Before you proceed with setting up data in SchoolInsight, please contact your Xello representative and ask them for the following information:

1. Xello SFTP server address and port number
2. Your username and password for their SFTP server
3. (Optional) The directory path where the file should be stored

Send school information to Xello

Before Xello can accept other files, they will need a file containing the school information. This is the only file you will create and fill manually, and most data (school name and RCDTS codes) can be copied over from School District Admin Main > Schools. Please reach out to your Xello representative for more information on how to send them this file.

Create reports for all of the data needed

Next, you will create and save the reports listed below.

Students Report

The following directions will guide you through creating a custom report with the student information needed by Xello:

1. Go to District Main > Reports > Saved Reports
2. Create > Classic Custom Report
3. Report Type
   • Leave the defaults of Students Details Report
4. Filters
   • Add another filter, School Name
     • Set it to the school you are sending to Xello. If you are sending multiple schools, use the plus sign to add additional entries
5. Data
   • Remove the default fields
   • Set the following for Fields to Show:
     • Student Number (or Student ID if Student Number is not used at your district)
     • Student First Name
     • Student Last Name
     • Student Email Address
     • Gender
     • Birth Date (Date Interval: Daily)
     • Grade Level
     • External School ID
6. Output
   • Choose Comma Delimited (CSV) with Show Header Row checked
7. Save As…
   • Save this as something memorable like Xello Integration - Students File

Courses Report

The following directions will guide you through creating the courses report needed by Xello:

1. Go to District Main > Import/Export > Export Courses
2. Fields to Show 
   • Remove School Name
   • Add Credits
   • Add Grade Level Minimum
   • Add Grade Level Maximum
3. Where Shown?
   • Choose Comma Separated Variable with Show Header Row checked
4. Save Report
   • Save it as something memorable like Xello Integration - Courses File

Grades Report

The following directions will guide you through creating the transcript grade report needed by Xello.  As records are required across multiple academic years, you will use the Data Queries tool within SchoolInsight to create a separate report for each academic year you would like to submit to Xello.

To create the Xello grades report follow these steps:

1. 1. Go to District Main > Reports > Saved Reports
   2. Create > Custom Query
   3. Academic Year
      • Start with the Current Academic Year
   4. Create Data Query
   5. Type
      • Extract Rows of Data with Remove Duplicate Rows checked
   6. Entities & Fields 
      • Data to Query: Add Students
        • Tip: Keep any automatically added data fields in the system
      • Data to Query: Add Traditional Report Card Grades, Classes, Courses, Yearly Course Data, Grading Periods, Grading Period Types, Report Card Item.
        • Tip: When searching for each item, you can type into the box instead of scrolling through
      • Student Fields to Query: Add Student Number (or Student ID if Student Number is not used at your district)
      • Yearly Enrollment Fields to Query: Add Academic Year
      • Grade Level Types Fields to Query: Add Grade Level Type Code
      • Traditional Report Card Grades Fields to Query: Add Grade
      • Courses Fields to Query: Add Course Name, Course Number
      • Yearly Course Data to Query: Credits
      • Grading Period Types Fields to Query: Add Grading Period Type
   7. Filters
      • Report Card Item: Transcripted = Yes
      • Schools to Query Data From: Remove any elementary schools
   8. Save Only
      • Save it as something memorable, including the Academic Year in the description, like Xello Integration - Grades 2020-21 File.
   9. Repeat the steps for the prior two academic years

Rosters Report

The following directions will guide you through creating the current year roster report needed by Xello:

1. 1. Go to District Main > Reports > Saved Reports
   2. Create > Custom Query
   3. Academic Year
      1. • Use Current Academic Year
   4. Create Data Query
   5. Type
      • Extract Rows of Data with Remove Duplicate Rows checked
   6. Entities & Fields 
      • Data to Query: Add Students and remove Enrollment Entries
      • Data to Query: Add Class Rosters, Classes, Courses, Class Grading Periods, Grading Periods, Grading Period Types
        • Tip: When searching for each item, you can type into the box instead of scrolling through
      • Student Fields to Query: Add Student Number (or Student ID if Student Number is not used at your district)
      • Grade Level Types Fields to Query: Grade Level Type Code
      • Courses Fields to Query: Add Course Name, Course Number, Credits
      • Grading Period Types Fields to Query: Add Grading Period Type
   7. Filters
      
      • Schools to Query Data From: Remove any elementary or junior high schools
   8. Save Only
      • Save it as something memorable, like Xello Integration - Rosters 2020-21 File.

After the Rosters report is created and exported, a column with the heading of "Grade" will need to be manually appended to the sheet. Xello can assist the district if they need assistance with this step.

Create scheduled exports

Once you have your reports set up, you will create the exports for each report by entering the SFTP details given to you from Xello and schedule them to go out nightly. You will need to create a total of five Scheduled Exports to send Xello all the information.

1. Go to District Main > Import/Export > Scheduled Export
2. Create Scheduled Export
3. Task (this varies for each type of report):
   • For the students’ report - choose Custom Report and pick the report from the dropdown.
   • For the courses report - choose Saved Report and pick the report from the dropdown.
   • For each grade report and the roster report - choose Data Query and pick the saved query from the dropdown.
4. Enter a Description and a Time to Run the report
   • We recommend choosing a time after midnight, as our servers are less busy at this time 
5. For Academic Year, select the current year 
   • This will need updating each year to keep exported data up to date
6. For Where to Export Files, enter the login credentials provided by your Xello Representative
7. For Alert Email, enter an email for the admin to be notified if the export doesn’t run as expected
8. Set Directory to / followed by your Xello SFTP user name. If your user name is MyDistrict it should be /MyDistrict
9. For File Name, enter a unique file name ending in .csv for each report, similar to the following: 
   • For the students’ report - Student.csv
   • For the courses report - Courses.csv
   • For each grade report - name it the academic year of the report plus “Grades.csv” for example, 2021Grades.csv, 2020Grades.csv, 2019Grades.csv
   • For the roster report - CurrentStudentCourses.csv
10. Save
11. Click the down arrow next to your new task, then choose Run Now

Once your exports have run, you will be ready to check in with your Xello representative. You can let them know that the files will be on the SFTP server, and they can review them with you for accuracy.