Grade Reporting for Campus, College and Departments Grade Reporting for Campus, College and Departments This database is linked to the student and isiscodesets databases on the AIS Data Warehouse. There are four data tables used to create the nine reports that are available by downloading this database. The dbo_ccoll, dbo_cdept, and cbo_clocn data tables, from the isiscodesets database, are used to display report description names for campus location, college, and department code values. The dbo_tcm table, from the student database, is used to determine if a Grade Scan Form has been assigned for the course for the current semester, to exclude courses with no expected grades, and to filter the end date for the course to be less than the current system date of the machine running the query. The reports are based on six queries (q_Campus, q_College, q_Department, q_Campus_No_Activity, and q_College_No_Activity). These six queries extract data from the dbo_tcm table, the three isiscodesets tables, and build a number of calculated fields. The reports allow the user to determine the status of grades entered for a campus, college, or department.   Queries These demonstration queries are intended to help the user access the data in order to retrieve a desired result from a database query. q_Prompt_Campus query will return a list of course sections that are determined by the criteria entered by the user. The query can be refined to provide a list of course sections based on any combination of the following: code_loc (prompt for campus location code (i.e., AA, AN, BD, BK, BR, CL, DE, DN, DS, FE, HN, HY, KP, MA, MK, NK, OZ, PC, SL, SV, UP, WB, WD, WS, XC, XP, XS, XW, or YK)) qnty_csec_expected_grades (criteria default: <>0) q_Prompt_College query will return a list of course sections that are determined by the criteria entered by the user. The query can be refined to provide a list of course sections based on any combination of the following: code_loc (prompt for campus location code (i.e., AA, AN, BD, BK, BR, CL, DE, DN, DS, FE, HN, HY, KP, MA, MK, NK, OZ, PC, SL, SV, UP, WB, WD, WS, XC, XP, XS, XW, or YK) code_csec_coll (prompt for college code (i.e., AA, AB, AG, AL, BA, BC, BL, CA, CC, CM, DU, ED, EM, EN, GN, GV, HD, HH, HP, IC, ID, IS, LA, LW, MD, MS, SC, UN, XX) qnty_csec_expected_grades (criteria default: <>0) q_Prompt_Department query will return a list of course sections that are determined by the criteria entered by the user. The query can be refined to provide a list of course sections based on any combination of the following: code_loc (prompt for campus location code (i.e., AA, AN, BD, BK, BR, CL, DE, DN, DS, FE, HN, HY, KP, MA, MK, NK, OZ, PC, SL, SV, UP, WB, WD, WS, XC, XP, XS, XW, or YK) code_csec_acdt (prompt for department code (i.e., see isiscodesets table cdept for code values) qnty_csec_expected_grades (criteria default: <>0) q_Campus_No_Activity query will return a list of course sections that are determined by the criteria entered by the user. The query can be refined to provide a list of course sections based on any combination of the following: code_univ_yr_sem (prompt for semester (i.e., 200203FA) code_loc (prompt for campus location code (i.e., AA, AN, BD, BK, BR, CL, DE, DN, DS, FE, HN, HY, KP, MA, MK, NK, OZ, PC, SL, SV, UP, WB, WD, WS, XC, XP, XS, XW, or YK) date_csec_grade_form_produced (criteria default: Is Not Null) days (calculated (IIf([qnty_csec_stud_graded]=0,DateDiff("d",[date_csec_end],Now()),Null, criteria default: Is Not Null)) q_College_No_Activity query will return a list of course sections that are determined by the criteria entered by the user. The query can be refined to provide a list of course sections based on any combination of the following: code_univ_yr_sem (prompt for semester (i.e., 200203FA) code_loc (prompt for campus location code (i.e., AA, AN, BD, BK, BR, CL, DE, DN, DS, FE, HN, HY, KP, MA, MK, NK, OZ, PC, SL, SV, UP, WB, WD, WS, XC, XP, XS, XW, or YK) code_csec_coll (prompt for college code (i.e., AA, AB, AG, AL, BA, BC, BL, CA, CC, CM, DU, ED, EM, EN, GN, GV, HD, HH, HP, IC, ID, IS, LA, LW, MD, MS, SC, UN, XX) date_csec_grade_form_produced (criteria default: Is Not Null) days (calculated (IIf([qnty_csec_stud_graded]=0,DateDiff("d",[date_csec_end],Now()),Null, criteria default: Is Not Null))   q_Extract_tcm query will return a list of course sections that are determined by the criteria entered by the user. This query is invoked by the other queries and joined with additional tables. The calculated code_loc field is derived for report display purposes and joined with the dbo_clocn table in the other queries. The query can be refined to provide a list of course sections based on any combination of the following: code_univ_yr_sem (prompt for semester (i.e., 200203FA) date_csec_end (criteria default: <Now()) numb_csec_grade_form_beg (criteria default: <>0) calculated code_loc (Mid([dbo_tcm]![code_csec_loc],1,2))   The queries can be modified to change the criteria in order to retrieve a specific population of records by selecting the query from the Queries Tab in the Grade Report Database and selecting the "Design" button (i.e., you can remove the prompt criteria and replace the prompt with a code for a campus, college, or department value). The query will be displayed in the Design View. The Design View is where the query can be modified to retrieve the desired population of data records. Note: The calculated code_loc field (Mid([dbo_tcm]![code_csec_loc],1,2)) is derived to create the join with the dbo_clocn data table.         Reports Report to Determine Courses that have Ended with No Grading Activity The reports can be viewed by selecting the Reports Tab in the Grade Report Database and selecting the "Preview" button. Once the a report is displayed it can be either printed or exported to Microsoft Word. If you would like to modify the report design, select the "Design" button. Select one of the following reports: Campus_Level_Report_for_Courses_Requiring_Grade_Entry_Detail College_Level_Report_for_Courses_Requiring_Grade_Entry_Detail The campus level report will prompt the user for a semester and campus code. The college level report will prompt the user for a semester, campus code, and a college code. Note:    The date_csec_grade_form_produced field criteria is set to "Is Not Null." This criteria validates that a scan form has been produced for the course(s) and that the course is ready for grading. The calculated days field ([qnty_csec_stud_graded]=0,DateDiff("d",[date_csec_end],Now()),Null) criteria is set to "Is Not Null." This field is calculated to validate that no grades have been entered for the course and calculates the number of days from the course end date. The criteria filters the query to select only the records with no grading activity. Once the prompted criteria has been entered, the report will run and display.   Report to Display Grade Entry Activity Detail The reports can be viewed by selecting the Reports Tab in the Grade Report Database and selecting the "Preview" button. Once the a report is displayed it can be either printed or exported to Microsoft Word. If you would like to modify the report design, select the "Design" button. Select one of the following reports: Campus_Level_Report_for_Grade_Entry_Detail Department_Level_Report_for_Grade_Entry_Detail Campus_Level_Report_for_Grade_Entry_by_College_Detail The campus level report will prompt the user for a semester and campus code. The department level report will prompt the user for a semester, campus code, and a department code. The Campus_Level_Report_for_Grade_Entry_by_College_Detail report groups the data by college. Note:    The qnty_csec_expected_grades field criteria is set to "<>0." This criteria validates that a course has at minimum one student for grading (students can have a status of registered, late drop, or withdrawal). The calculated code_loc field (Mid([q_Extract_tcm]![code_csec_loc],1,2)) is derived to create the join with the dbo_clocn data table. The calculated count fields are used to sum counts for the report. Once the prompted criteria has been entered, the report will run and display.   Report to Display Grade Entry Activity Summary Counts The reports can be viewed by selecting the Reports Tab in the Grade Report Database and selecting the "Preview" button. Once the a report is displayed it can be either printed or exported to Microsoft Word. If you would like to modify the report design, select the "Design" button. Select one of the following reports: Campus_Level_Report_for_Grade_Entry_by_College_Summary Campus_Level_Report_for_Grade_Entry_Summary College_Level_Report_for_Grade_Entry_Summary Department_Level_Report_for_Grade_Entry_Summary The campus level report will prompt the user for a semester and campus code. The college level report will prompt the user for a semester and college code. The department level report will prompt the user for a semester, campus code, and a department code. The Campus_Level_Report_for_Grade_Entry_by_College_Summary report groups the data by college. Note:    The qnty_csec_expected_grades field criteria is set to "<>0." This criteria validates that a course has at minimum one registered student for grading (students can have a status of registered, late drop, or withdrawal). The calculated code_loc field (Mid([q_Extract_tcm]![code_csec_loc],1,2)) is derived to create the join with the "dbo_clocn" data table. The calculated count fields are used to sum counts for the report. Once the prompted criteria has been entered, the report will run and display. Click on Download Here to download the MS Access Database.

Printer friendly view