Report Workaround: Credit Spreadsheet Report (video)

Note: This article contains video content that will not display or is not optimized to display correctly in the Foundry on-site support widget. For best results, we recommend reading the original in the Knowledge Base. Click the pop-out button at the top right of the widget to open the full article.

Overview

This article details a temporary workaround for users who need a spreadsheet report of credits earned in a particular time period. The relevant reports include the following:

• Credit Spreadsheet goal
• Credit Spreadsheet earned
• Credit Spreadsheet remaining
• Credit Spreadsheet summary (this report essentially includes data from the three above reports)

Longer-tenured users may have all 4 in their menu, while newer users likely have only the Credit Spreadsheet Summary Report after we determined this was the most relevant and most often used report.

Contents

Overview Video

Phase 1: UserID

Phase 2: Terms

Phase 3: Generate Course Code Report

Phase 4: Student User Export

Phase 5: Combine Reports and add Formula

If you are not trying to generate a spreadsheet report of credits earned during a custom time period, you most likely don't need this workaround.

READ THIS FIRST!

This workaround contains several steps, but essentially five "phases." Those steps which require Admin credentials are indicated with (ADMIN) in parentheses.

1. We ensure our student records each contain a unique UserID, as distinct from the Username students use to log in to Foundry. (ADMIN)
2. We add terms that mimic the custom time frame we want to run the report. (ADMIN)
3. We run the Course Code Report*, which contains all fractional credits earned by each student, listed by the experience in which they were earned, so some reports may have several rows for each student. It also contains each student's unique UserID.
4. We export the current student list, which also contains the UserID. (ADMIN)
5. We will paste these reports together into a single spreadsheet and add a formula that sums each student's credits and reports them in a single row.

Advisor-only users: If an Admin user has already created the necessary terms, and if you have an up-to-date student export list, you can run the Course Code Spreadsheet Export as often as necessary and repeat this process without additional Admin credentials. 

*Note: If your site does not contain the Course Code Spreadsheet Export, it likely contains the Advanced Course Code Spreadsheet Export, which works as well. The ACC report contains more features and a slightly different order, but the critical fields are the same: Student (or user) ID and Credits Earned.

 

Overview Video

Phase 1: UserID (ADMIN)

Ensure all the students you want to report have a unique UserID.

This has historically been an optional field for Foundry users, although many schools use it to submit reports from Foundry to state agencies. If you have to update ID's for many student users, we suggest using the batch upload feature - read here for more.

Phase 2: Terms (ADMIN)

Define a new Term for the desired reporting period.

The Credit Spreadsheet report was meant to work with adjustable time frames (ex: 09/01/2020-12/30/2020). The report we use to mimic it, however, is contained on the Transcripts menu, and runs using your pre-defined terms (ex: Semester 1 2019-2020). If you want a different time frame that you don't currently have, you need to add it as a term. The length of the term doesn't matter, as long as the data you want to capture is within the start and end dates you choose. For instructions on managing terms, read here. 

Phase 3: Course Code Report

Generate the Course Code Report

1. In the Advisor role, navigate to Quick Actions > Generate Transcripts
2. Select 'Course Code Spreadsheet Export'
3. Select all desired students
4. Select desired term
5. Click 'Generate transcript.'

Once the transcript finishes, click the link to download it to your desktop. Open it to confirm it contains all the students on whom you want to report.

Phase 4: Student User Export (ADMIN)

Export your current student users. 

6. Admin > Users
7. Filter roles > student
8. Click the blue checkbox at the top left of the list to select all students, then click 'Export Selected'

Phase 5: Combine Reports and add Formula

Copy and paste the users into your transcript spreadsheet

9. Copy that entire result and paste it into a new tab on the transcript spreadsheet.
(I renamed the tabs 'All Users' and 'Student Credits' but you don't have to as long as you substitute the tab names into the formula below)
10. Insert column into the new tab (because it has only one row per student)
(I named the column 'total credits earned')

At this point, check to make sure you have no blanks in your UserID field. If you have students with credits earned but blanks in the UserID field, follow steps 11-13. If you have no blanks, skip to step 14.

11. Filter on the UserID column to show only the students with blanks in the field.
12. With the filter on, select all blank cells in the column.
13. With the target cells selected, right-click any cell and choose 'Clear Contents.' (this step will allow the formula to work with blank UserIDs, by clearing Foundry formatting from the spreadsheet that you don't see in the cell).  

Input the Sum formula

14. Into the first student row in that new column, paste this exact formula:

=IF(ISBLANK('All Users'!C2),0,ROUND(SUMIF('Student Credits'!A:A,'All Users'!C2,'Student Credits'!H:H),2))

What does the formula do?

Specifically, it adds each student's credits by matching the UserID from one tab to the other, ignoring any blank UserID cells, and rounds them to 2 decimal places. You can substitute a '0' at the end of the formula to round to the nearest whole number, or a '1' to round to the nearest tenth, etc. 

Copy (or 'fill') the formula from the first cell down

15. Select the first cell, drag to the bottom, then select Edit > fill down (or cmd-D) - this will copy the formula into all the cells.

Labels: spreadsheet, transcript,