Teacher Dashboard 365 Synergy SIS Extract

Just in case anyone else needs the Teacher Dashboard 365 extract for Synergy SIS, I’m sharing it.

Just make sure the students’ “displayName” in active directory is the students full name (Last, First) and their AD account “userPrincipalName” is their PermID@Office365Ending.  Change the @Office365Ending variable to your sites Office 365 domain name.

 

SET NOCOUNT ON;

--Sample Teacher Dashboard 365 Extract
--http://www.teacherdashboard365.com/
DECLARE @Office365Ending NVARCHAR(255)
SET @Office365Ending = '@SCHOOLOFFICE365.org'

SELECT 
       EPC_SCH_YR_SECT.SECTION_GU AS [guid]
       ,EPC_CRS.COURSE_SHORT_TITLE + N' ' + STAFF_PERSON.LAST_NAME + N' ' + STAFF_PERSON.FIRST_NAME  + N' ' + CAST(EPC_SCH_YR_SECT.PERIOD_BEGIN AS NVARCHAR(2)) AS [name]
       ,EPC_CRS.COURSE_SHORT_TITLE + N' ' + STAFF_PERSON.LAST_NAME + N' ' + STAFF_PERSON.FIRST_NAME + N' ' + CAST(EPC_SCH_YR_SECT.PERIOD_BEGIN AS NVARCHAR(2)) + ' ' + EPC_SCH_YR_SECT.SECTION_ID  + ' ' + EPC_SCH_YR_SECT.TERM_CODE AS [description]
       ,(SELECT TOP 1(
                     STUFF(
                                  (SELECT DISTINCT ';' + EPC_STU.SIS_NUMBER +  @Office365Ending
                                  FROM rev.EPC_STU
                                         INNER JOIN
                                                rev.EPC_STU_YR ON EPC_STU_YR.STUDENT_GU=EPC_STU.STUDENT_GU
                                         INNER JOIN
                                                rev.EPC_STU_SCH_YR ON EPC_STU_SCH_YR.STUDENT_SCHOOL_YEAR_GU = EPC_STU_YR.STU_SCHOOL_YEAR_GU
                                         LEFT JOIN 
                                                rev.REV_ORGANIZATION_YEAR ON REV_ORGANIZATION_YEAR.ORGANIZATION_YEAR_GU = EPC_STU_SCH_YR.ORGANIZATION_YEAR_GU
                                         LEFT JOIN
                                                rev.REV_YEAR ON EPC_STU_SCH_YR.YEAR_GU = REV_YEAR.YEAR_GU
                                         LEFT JOIN
                                                rev.REV_PERSON AS STUDENT_PERSON ON EPC_STU.STUDENT_GU = STUDENT_PERSON.PERSON_GU
                                         LEFT JOIN
                                                rev.REV_ORGANIZATION ON REV_ORGANIZATION_YEAR.ORGANIZATION_GU = REV_ORGANIZATION.ORGANIZATION_GU   
                                         LEFT JOIN     
                                                rev.EPC_SCH ON EPC_SCH.ORGANIZATION_GU=REV_ORGANIZATION_YEAR.ORGANIZATION_GU
                                         LEFT JOIN
                                                rev.EPC_CA_STU ON EPC_CA_STU.STUDENT_GU=EPC_STU.STUDENT_GU
                                         LEFT JOIN 
                                                rev.EPC_STU_CLASS ON EPC_STU_CLASS.STUDENT_SCHOOL_YEAR_GU = EPC_STU_SCH_YR.STUDENT_SCHOOL_YEAR_GU
                                  WHERE
                                         REV_YEAR.SCHOOL_YEAR = (SELECT  SCHOOL_YEAR  FROM rev.SIF_22_Common_CurrentYear)
                                         AND REV_YEAR.EXTENSION = 'R'  
                                         AND EPC_STU_SCH_YR.STATUS IS NULL 
                                         AND EPC_STU_CLASS.SECTION_GU = EPC_SCH_YR_SECT.SECTION_GU
                                         AND EPC_STU_CLASS.ENTER_DATE <= GETDATE() 
                                         AND (EPC_STU_CLASS.LEAVE_DATE IS NULL OR EPC_STU_CLASS.LEAVE_DATE >=GETDATE())
                                  FOR XML PATH('')
                                  )
                           ,1,1,'') 
                           )) AS [students]
              ,LOWER(ISNULL(REV_USER.LOGIN_NAME,'')) +  @Office365Ending + ISNULL((SELECT TOP 1(
                     STUFF(
                                  (SELECT DISTINCT ';' + LOWER(USER1.LOGIN_NAME) +  @Office365Ending
                                  FROM [rev].[EPC_SCH_YR_SECT_STF]
                                                LEFT JOIN
                                                       rev.EPC_STAFF_SCH_YR AS STAFF_SCH_YR ON STAFF_SCH_YR.STAFF_SCHOOL_YEAR_GU = [EPC_SCH_YR_SECT_STF].STAFF_SCHOOL_YEAR_GU 
                                                LEFT JOIN
                                                       rev.REV_USER AS USER1 ON USER1.USER_GU=STAFF_SCH_YR.STAFF_GU             
                                  WHERE [EPC_SCH_YR_SECT_STF].SECTION_GU=EPC_SCH_YR_SECT.SECTION_GU
                                         AND EPC_SCH_YR_SECT_STF.START_DATE <=GETDATE()
                                         AND (EPC_SCH_YR_SECT_STF.END_DATE >= GETDATE() OR EPC_SCH_YR_SECT_STF.END_DATE IS NULL)
                                         AND USER1.LOGIN_NAME IS NOT NULL
                                  FOR XML PATH('')
                                  )
                           ,1,0,'')
                           )),'') AS [teachers]
              
FROM rev.EPC_SCH_YR_SECT 
              LEFT JOIN
                     rev.EPC_STAFF_SCH_YR ON EPC_STAFF_SCH_YR.STAFF_SCHOOL_YEAR_GU = EPC_SCH_YR_SECT.STAFF_SCHOOL_YEAR_GU 
              LEFT JOIN     
                     rev.REV_PERSON AS STAFF_PERSON  ON EPC_STAFF_SCH_YR.STAFF_GU = STAFF_PERSON.PERSON_GU 
              LEFT JOIN
                     rev.EPC_STAFF ON EPC_STAFF.STAFF_GU = EPC_STAFF_SCH_YR.STAFF_GU
              LEFT JOIN
                     rev.REV_USER ON REV_USER.USER_GU=EPC_STAFF_SCH_YR.STAFF_GU
              LEFT JOIN
                     rev.EPC_SCH_YR_CRS ON EPC_SCH_YR_CRS.SCHOOL_YEAR_COURSE_GU=EPC_SCH_YR_SECT.SCHOOL_YEAR_COURSE_GU
              LEFT JOIN
                     rev.EPC_CRS ON EPC_CRS.COURSE_GU=EPC_SCH_YR_CRS.COURSE_GU
              LEFT JOIN
                     rev.REV_ORGANIZATION_YEAR ON REV_ORGANIZATION_YEAR.ORGANIZATION_YEAR_GU=EPC_SCH_YR_SECT.ORGANIZATION_YEAR_GU
              LEFT JOIN
                     rev.REV_ORGANIZATION ON REV_ORGANIZATION.ORGANIZATION_GU=REV_ORGANIZATION_YEAR.ORGANIZATION_GU
              LEFT JOIN
                     rev.REV_YEAR ON REV_YEAR.YEAR_GU=REV_ORGANIZATION_YEAR.YEAR_GU
              LEFT JOIN
                     rev.EPC_SCH_ROOM ON EPC_SCH_ROOM.ROOM_GU=EPC_SCH_YR_SECT.ROOM_GU
WHERE         (REV_YEAR.SCHOOL_YEAR = (SELECT  SCHOOL_YEAR  FROM rev.SIF_22_Common_CurrentYear)) 
              AND REV_YEAR.EXTENSION = 'R'
              AND (ISNULL(EPC_SCH_YR_SECT.TOTAL_FEMALE,0) + ISNULL(EPC_SCH_YR_SECT.TOTAL_MALE,0)) > 0 --Sections in Use Only
              --Filter by period if needed
              --AND (EPC_SCH_YR_SECT.PERIOD_BEGIN = @PERIOD OR @PERIOD = 0)
              
              /*
              --Filter by grade level if needed
              AND (SELECT TOP 1 ALT_CODE_1 AS Grade
                           FROM rev.REV_BOD_LOOKUP_VALUES
                                  LEFT JOIN rev.REV_BOD_LOOKUP_DEF ON REV_BOD_LOOKUP_DEF.LOOKUP_DEF_GU=REV_BOD_LOOKUP_VALUES.LOOKUP_DEF_GU
                           WHERE REV_BOD_LOOKUP_DEF.LOOKUP_DEF_CODE = 'GRADE'
                                         AND REV_BOD_LOOKUP_VALUES.VALUE_CODE = EPC_SCH_YR_SECT.GRADE_RANGE_LOW) >= @GRADELOW
              AND (SELECT TOP 1 ALT_CODE_1 AS Grade
                           FROM rev.REV_BOD_LOOKUP_VALUES
                                  LEFT JOIN rev.REV_BOD_LOOKUP_DEF ON REV_BOD_LOOKUP_DEF.LOOKUP_DEF_GU=REV_BOD_LOOKUP_VALUES.LOOKUP_DEF_GU
                           WHERE REV_BOD_LOOKUP_DEF.LOOKUP_DEF_CODE = 'GRADE'
                                         AND REV_BOD_LOOKUP_VALUES.VALUE_CODE = EPC_SCH_YR_SECT.GRADE_RANGE_LOW) <= @GRADEHIGH
              */

 

Resources:

http://www.teacherdashboard365.com/

Leave a Reply

%d bloggers like this: