Exercise 4 - Student Data

Exercise 4: Export a report

In this exercise, you will:

  • Run a report from the Undergraduate Applicants dashboard
  • Export the report data to a .CSV file that you can open in Excel
  • Use Excel functionality to do further analysis and create a chart to tell the story

Exercise

  1. Once you are logged in to Cal Answers, there are a few different ways to open another dashboard. One way is to select the Home hyperlink from the menu at the top right corner of the Cal Answers application window to open the Cal Answers home page with the list of subject areas, expand the section and select a dashboard.

    Another way is to use the Dashboards menu. Select the Dashboards menu and scroll down to the Student Applicants section. Select Undergraduate Applicants to open that dashboard.

  2. Select the UG Apps by Multiple Fields report. 

  3. The University reported a record breaking number of new freshman admitted from underrepresented minority groups in 2020-21. You’ll use this dashboard to explore that data. Make the following selections and then select Apply:
  • Academic Year: 
  • Semester:
  • Applicant Status:
  • Applicant Type:

4. Scroll to the bottom of the report and select the blue two-headed arrow to display all of the rows in the report.

5. Next you’ll export the data so you can do your own analysis with it in Excel. Select the Export link below the report and the display navigation icons.

Export link below report

6. Select Data and then select CSV.

7. Be patient. When you export to CSV, all of the detailed data is exported, not just the few columns and summary data you see presented on screen.

8. Select OK to acknowledge the completion of the Export process.

9. The file is saved to the Downloads folder. You can go to that folder or you can double-click the file icon in the lower left corner of the window to open the file.

10. Explore the detailed data in Excel. You’ll notice some columns that aren’t in the report shown on screen. To learn about this data, you can explore the Data Dictionary in the Wiki. From the Cal Answers Training page, select the link for Student Applicants(link is external) in the Wiki section. You can also find a link to the Wiki on the Overview tab of the Undergraduate Applicants dashboard.

11. In Excel, you may want to create a pivot table to generate your own aggregations and formulas to calculate rates of change over time and other metrics of interest.

12. Select all of the data and create a pivot table. In the pivot table, select:

  • Academic Year for the columns
  • Ucb Level 1 Ethnic Desc for the rows
  • Applicant Headcounts for the values. The value should be set to Sum.

13. Create formulas to calculate the increase/(decrease) in students from 2018-19 to 2019-20 and 2019-20 to 2020-21. Then create formulas to calculate these changes as percentages.

14. Select the data from the percentage change section and use it to create a stacked bar chart so you can easily compare by ethnicity and year.


Next Steps