ssrs 2008 final report totals

Question

ssrs 2008 final report totals

dianerstein 8713

SSCrazy

Points: 2197
More actions
June 2, 2017 at 9:02 pm

#402127

In an existing ssrs 2008 report, I want to add final report totals. I want the total line to be the final total for all the lines displayed on the report. Right now when I right click on the calendarnames and select 'add total', I see the totals for all the data just brought into the report. I just want to see the totals for only the calendar names that have been selected.
For example, if I have the calendarnames of Adams Elem, Gross High, and Josylyn Middle School, I only want the totals to be for those 3 schools. I do not want to see the totals
for all the schools in the school district. Can you tell me what I can do to solve my problem? Here is a picture of what my ssrs report looks like in design view:

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63435 More actions · Answer 1

dianerstein 8713 - Friday, June 2, 2017 9:02 PM
In an existing ssrs 2008 report, I want to add final report totals. I want the total line to be the final total for all the lines displayed on the report. Right now when I right click on the calendarnames and select 'add total', I see the totals for all the data just brought into the report. I just want to see the totals for only the calendar names that have been selected.
For example, if I have the calendarnames of Adams Elem, Gross High, and Josylyn Middle School, I only want the totals to be for those 3 schools. I do not want to see the totals
for all the schools in the school district. Can you tell me what I can do to solve my problem? Here is a picture of what my ssrs report looks like in design view:

I see the totals for all the data just brought into the report. I just want to see the totals for only the calendar names that have been selected.
For example, if I have the calendarnames of Adams Elem, Gross High, and Josylyn Middle School, I only want the totals to be for those 3 schools. I do not want to see the totals
For example, if I have the calendarnames of Adams Elem, Gross High, and Josylyn Middle School, I only want the totals to be for those 3 schools. I do not want to see the totals

This is like pin the tail on the donkey. Does your report have parameters where you select only the schools you want? If you filter for only the selected schools, then that's what your stored procedure should return. Kinda hard to tell what's happening though, because you didn't post the stored procedure.
If you want report totals, can't you put them in the report footer?

dianerstein 8713 SSCrazy Points: 2197 More actions · Answer 2

This is in response to your answer:
1. I will go find the stored procedure and post the stored procedure here. It is a stored procedure used by lots of reports so I do not plan to change it unless I need to.
2. You mentioned putting report totals in the report footer. I can do that. However can you tell me what the benefit would be to put the report totals in the report footer is? if that actually only gets the results from what is pulled into the report, do you need any special logic? If so, what should that logic be?

pietlinden SSC Guru Points: 63435 More actions · Answer 3

Are you adding the data to a tablix? I can't tell from your report design. If you're doing that, you can just add a totals line outside the group(s).

dianerstein 8713 SSCrazy Points: 2197 More actions · Answer 4

Thanks for your assistance so far!
I want to mention the following in regards to your responses:
1. No I am not adding total lines to outside groups. Can you tell me when to use outside groups and how to use them?
2. In a comment or 2 later, I will show you the stored procedure of how I modified the stored procedure to obtain the data I needed. When looking at the logic in the stored procedure, this report I am referring to is @report=2. When I look at the data obtained from my version of the stored procedure when I ran it, I see that the data is repeated lots of times.
Bascially the counts are repeated a lot for the same calendared.
Could you show me how to setup the outside groups and only summing the counts by unique calendariD?

Here are a couple of examples:
calendarID                      studentCounts
7935                                   450
7935                                   450
7986                                     850
7935                                   450
7902                                   999
7935                                   450
7902                                   999
Listed below is the stored procedure that is being used:

USE [test]

--GO
--debug
DECLARE @endYear SMALLINT = 2018,
@calendarID VARCHAR(8000) = '7935,7896,7902,7936,7919',
@grade VARCHAR(8000) = 'KG,01,02,03,04,05,06',
@report TINYINT = 2,
@serviceType varchar(01)= 'P',
@stateExclude bit =NULL
--
--end debug

--CREATE PROCEDURE [dbo].[spFallOpeningLetter] (@endYear SMALLINT,
--                                              @calendarID VARCHAR(8000), --accepts single or multiple values
--                                              @grade VARCHAR(8000), --accepts single or multiple values
--                                              @report TINYINT, -- 0 = Student Letter, 1 = Student List, 2 = Student Counts
--             @serviceType varchar(1), -- P = Primary, S = Partial, N = Special Ed Services
--             @stateExclude bit) -- 0 = Not State Excluded, 1 = State Excluded, null = Both Selected

--AS

DECLARE @ActiveEndYear SMALLINT = (SELECT endYear FROM OPS.dbo.SchoolYear WITH (NOLOCK) WHERE active = 1)

--Creating all temp tables used throughout the entire query since all are referenced for the final dataset. Dynamically inserting based on the report selected.

--Only parsing @calendarID once then joining to this temp table throughout the procedure for increased performance
IF OBJECT_ID('tempdb..#ParsedCalendars') IS NOT NULL DROP TABLE #ParsedCalendars

CREATE TABLE #ParsedCalendars (calendarID INT)
INSERT INTO #ParsedCalendars
SELECT [_id]
FROM OPS.dbo.fn_splitString(@calendarID)

--Only parsing @grade once then joining to this temp table throughout the procedure for increased performance
IF OBJECT_ID('tempdb..#ParsedGrades') IS NOT NULL DROP TABLE #ParsedGrades

CREATE TABLE #ParsedGrades (grade VARCHAR(4))
INSERT INTO #ParsedGrades
SELECT [_id]
FROM OPS.dbo.fn_splitString(@grade)

IF OBJECT_ID('tempdb..#Student') IS NOT NULL DROP TABLE #Student

CREATE TABLE #Student (personID INT,
                       studentNumber VARCHAR(15),
                       enrollmentID INT,
                       grade VARCHAR(4),
                       calendarID INT,
                       calendarName VARCHAR(30),
                       schoolType VARCHAR(5),
                       schoolName VARCHAR(40),
                       schoolAddress VARCHAR(50),
                       schoolCity VARCHAR(25),
                       schoolState VARCHAR(2),
                       schoolZip VARCHAR(10),
                       calendarStructure VARCHAR(3),
                       ECSE BIT,
                       firstName VARCHAR(50),
                       middleName VARCHAR(50),
                       lastName VARCHAR(50),
                       fullName VARCHAR(104),
                       gender CHAR(1),
                       CorrespondenceLanguage VARCHAR(100))

IF OBJECT_ID('tempdb..#Homeroom') IS NOT NULL DROP TABLE #Homeroom

CREATE TABLE #Homeroom (personID INT,
                        studentNumber VARCHAR(15),
                        serviceType VARCHAR(1),
                        enrollmentID INT,
                        calendarID INT,
                        schoolID INT,
                        teacherPersonID INT,
                        teacherFirstName VARCHAR(50),
                        teacherMiddleName VARCHAR(50),
                        teacherLastName VARCHAR(50),
                        teacherName VARCHAR(104),
                        sectionTeacherDisplay VARCHAR(102),
                        roomID INT,
                        roomName VARCHAR(10),
                        courseID INT,
                        courseHomeroom BIT,
                        sectionID INT,
                        sectionHomeroom BIT,
                        rosterID INT,
                        trialID INT,
                        periodID INT,
                        periodScheduleID INT,
                        structureID INT,
                        termID INT)

IF OBJECT_ID('tempdb..#Household') IS NOT NULL DROP TABLE #Household

CREATE TABLE #Household (personID INT,
                         enrollmentID INT,
                         calendarID INT,
                         householdID INT,
                         addressLine1 VARCHAR(105),
                         city VARCHAR(24),
                         [state] VARCHAR(2),
                         zip VARCHAR(10),
                         householdPhone VARCHAR(25))

IF OBJECT_ID('tempdb..#Schedule') IS NOT NULL DROP TABLE #Schedule

CREATE TABLE #Schedule (personID INT,
                        enrollmentID INT,
                        calendarID INT,
                        scheduleName VARCHAR(20),
                        startTime VARCHAR(10),
                        endTime VARCHAR(10),
                        firstDay VARCHAR(10),
                        firstDay_WeekDayEnglish VARCHAR(10),
                        firstDay_MonthEnglish VARCHAR(10),
                        firstDay_WeekDaySpanish VARCHAR(10),
                        firstDay_MonthSpanish VARCHAR(10),
                        firstDay_Day VARCHAR(2),
                        firstDay_Year VARCHAR(4))

IF OBJECT_ID('tempdb..#CustomCalendar') IS NOT NULL DROP TABLE #CustomCalendar

CREATE TABLE #CustomCalendar (calendarID INT,
                              BreakfastStartTimeEnglish VARCHAR(256),
                              BreakfastStartTimeSpanish VARCHAR(256),
                              SWTSParticipation VARCHAR(256),
                              SWTSNameEnglish VARCHAR(256),
                              SWTSNameSpanish VARCHAR(256),
                              SWTSDateEnglish VARCHAR(256),
                              SWTSDateSpanish VARCHAR(256),
                              SWTSTimeEnglish VARCHAR(256),
                              SWTSTimeSpanish VARCHAR(256),
                              SWTSLocationEnglish VARCHAR(256),
                              SWTSLocationSpanish VARCHAR(256),
                              HomeroomsOnFOL VARCHAR(256))

IF OBJECT_ID('tempdb..#StudentCount') IS NOT NULL DROP TABLE #StudentCount

CREATE TABLE #StudentCount (calendarID INT,
StudentCount DECIMAL)

IF OBJECT_ID('tempdb..#StudentAddressCount') IS NOT NULL DROP TABLE #StudentAddressCount

CREATE TABLE #StudentAddressCount (calendarID INT,
StudentAddressCount DECIMAL)

IF OBJECT_ID('tempdb..#SpanishSpeakingStudentCount') IS NOT NULL DROP TABLE #SpanishSpeakingStudentCount

CREATE TABLE #SpanishSpeakingStudentCount (calendarID INT,
SpanishCount DECIMAL)

IF OBJECT_ID('tempdb..#NonSpanishSpeakingStudentCount') IS NOT NULL DROP TABLE #NonSpanishSpeakingStudentCount

CREATE TABLE #NonSpanishSpeakingStudentCount (calendarID INT,
NonSpanishCount DECIMAL)

INSERT INTO #Student
SELECT Person.personID, Person.studentNumber, Enrollment.enrollmentID, Enrollment.grade, Calendar.calendarID, Calendar.name AS calendarName,
       School.[type] AS schoolType, School.name AS schoolName, School.[address] AS schoolAddress, School.city AS schoolCity,
       School.[state] AS schoolState, School.zip AS schoolZip,
       CASE
         WHEN vCalendarSchool.calendarPrefix = 'EC' THEN 'EC'
         ELSE 'REG'
       END AS calendarStructure,
       CASE
         WHEN Enrollment.specialEdSetting IN ('6', '16') THEN 1
         ELSE 0
       END AS ECSE,
       Ident.firstName, Ident.middleName, Ident.lastName,
       CASE
         WHEN Ident.middleName IS NOT NULL THEN (Ident.firstName + ' ' + LEFT(Ident.middleName, 1) + '. ' + Ident.lastName)
         ELSE Ident.firstName + ' ' + Ident.lastName
       END AS fullName,
       Ident.gender, COALESCE(CorresLang.name, 'English')
FROM OPS.dbo.Person AS Person WITH (NOLOCK)
JOIN OPS.dbo.[Identity] AS Ident WITH (NOLOCK)
    ON Person.personID = Ident.personID
   AND Ident.identityID = Person.currentidentityID
JOIN OPS.dbo.Enrollment AS Enrollment WITH (NOLOCK)
    ON Enrollment.personID = Person.personID
JOIN OPS.dbo.Calendar AS Calendar WITH (NOLOCK)
    ON Calendar.calendarID = Enrollment.calendarID
JOIN (SELECT EnrollmentMax.personID, EnrollmentMax.serviceType, EnrollmentMax.endYear, MAX(EnrollmentMax.startDate) AS startDate, CalendarMax.summerSchool
        ,EnrollmentMax.StateExclude
        FROM OPS.dbo.Enrollment AS EnrollmentMax WITH (NOLOCK)
          JOIN OPS.dbo.Calendar AS CalendarMax WITH (NOLOCK)
            ON CalendarMax.calendarID = EnrollmentMax.calendarID
        WHERE EnrollmentMax.endYear = @endYear
          AND EnrollmentMax.serviceType = @serviceType -- 'P'
          AND CalendarMax.summerSchool = 0
    AND (EnrollmentMax.StateExclude = @stateExclude OR @stateExclude IS NULL)

        GROUP BY EnrollmentMax.personID, EnrollmentMax.serviceType, EnrollmentMax.endYear, CalendarMax.summerSchool,EnrollmentMax.StateExclude) AS MaxEnrollment
    ON Enrollment.personID = MaxEnrollment.personID
   AND Enrollment.startDate = MaxEnrollment.startDate
   AND Enrollment.serviceType = MaxEnrollment.serviceType
   AND Enrollment.endYear = MaxEnrollment.endYear
   AND Calendar.summerSchool = MaxEnrollment.summerSchool
JOIN OPS.dbo.School AS School WITH (NOLOCK)
    ON School.schoolID = Calendar.schoolID
JOIN CampusOps.dbo.vCalendarSchool AS vCalendarSchool
    ON vCalendarSchool.calendarID = Enrollment.calendarID
JOIN #ParsedCalendars AS ParsedCalendars
    ON Calendar.calendarID = ParsedCalendars.calendarID
JOIN #ParsedGrades AS ParsedGrades
    ON Enrollment.grade = ParsedGrades.grade
LEFT JOIN (SELECT CustomStudent.personID, CampusDictionary.name
             FROM OPS.dbo.CustomStudent AS CustomStudent WITH (NOLOCK)
               JOIN OPS.dbo.CampusDictionary AS CampusDictionary WITH (NOLOCK)
                 ON CustomStudent.value = CampusDictionary.code
                AND CustomStudent.attributeID = CampusDictionary.attributeID
             WHERE CampusDictionary.attributeID = 525) AS CorresLang
    ON CorresLang.personID = Enrollment.personID
WHERE ((Enrollment.endYear = @ActiveEndYear AND COALESCE(Enrollment.endDate, GETDATE()) >= GETDATE()) --Active School Year
          OR
       (Enrollment.endYear > @ActiveEndYear AND Enrollment.endDate IS NULL)) --Future School Years

--SELECT * FROM #Student ORDER BY studentNumber --debug

IF @report IN (0, 1)
BEGIN
INSERT INTO #Homeroom
EXEC CampusOps.dbo.spHomeroom @endYear, @calendarID, @serviceType, NULL

DELETE #Homeroom
WHERE periodScheduleID IN (SELECT periodScheduleID
FROM OPS.dbo.PeriodSchedule WITH (NOLOCK)
WHERE name LIKE '%B%') --Keep only "A" day schedules

--SELECT * FROM #Homeroom
END

INSERT INTO #Household
SELECT vRelationships.studentPersonID, Student.enrollmentID, Student.calendarID, vHouseholdAddress.householdID, vHouseholdAddress.addressLine,
        vHouseholdAddress.city, vHouseholdAddress.[state], vHouseholdAddress.zip, vHouseholds.householdPhone
FROM CampusOps.dbo.vRelationships AS vRelationships
JOIN CampusOps.dbo.vHouseholdAddress AS vHouseholdAddress
    ON vHouseholdAddress.householdID = vRelationships.householdID
JOIN CampusOps.dbo.vHouseholds AS vHouseholds
    ON vHouseholds.householdID = vHouseholdAddress.householdID
JOIN #Student AS Student
    ON Student.personID = vRelationships.studentPersonID
WHERE vRelationships.activeRelationship = 1
AND vRelationships.householdRelationship = 1
AND (vRelationships.relationshipGuardian = 1 OR vRelationships.relationship = 'Step Parent')
AND vRelationships.relationshipMailing = 1
AND vHouseholdAddress.activeAddress = 1
AND vHouseholdAddress.mailingAddress = 1

--Delete students that do not have a mailing address
DELETE #Student
WHERE personID NOT IN (SELECT personID FROM #Household)

--SELECT DISTINCT * FROM #Household ORDER BY personID, addressLine1

IF @report = 0
BEGIN
INSERT INTO #Schedule
SELECT Roster.personID, Roster.enrollmentID, Roster.calendarID, Roster.PeriodScheduleName AS scheduleName,
         LTRIM(SUBSTRING(CONVERT(VARCHAR(20), MIN(Period.starttime), 9), 13, 5) + ' ' + SUBSTRING(CONVERT(VARCHAR(30), MIN(Period.starttime), 9), 25, 2)) AS starttime,
         LTRIM(SUBSTRING(CONVERT(VARCHAR(20), MAX(Period.endtime), 9), 13, 5) + ' ' + SUBSTRING(CONVERT(VARCHAR(30), MAX(Period.endtime), 9), 25, 2)) AS endtime,
         CONVERT(VARCHAR(10), Term.startDate, 101) AS firstDay, DATENAME(WEEKDAY, Term.startDate) AS firstDay_WeekDayEnglish, DATENAME(MONTH, Term.startDate) AS firstDay_MonthEnglish,
         CampusOps.dbo.fnWeekDaySpanish(Term.startDate) AS firstDay_WeekDaySpanish, CampusOps.dbo.fnMonthSpanish(Term.startDate) AS firstDay_MonthSpanish,
         DATEPART(DAY, Term.startDate) AS firstDay_Day, DATEPART(YEAR, Term.startDate) AS firstDay_Year
FROM CampusOps.dbo.vRoster AS Roster
    JOIN OPS.dbo.Period AS Period WITH (NOLOCK)
      ON Period.periodID = Roster.periodID
     AND Period.periodScheduleID = Roster.periodScheduleID
    JOIN OPS.dbo.Term AS Term WITH (NOLOCK)
      ON Roster.termID = Term.termID
     AND Roster.termScheduleID = Term.termScheduleID
    JOIN #ParsedCalendars AS ParsedCalendars --Performed faster returning all students for the selected calenders vs. JOINing to #Student
      ON Roster.calendarID = ParsedCalendars.calendarID
WHERE Roster.TermSeq = 1
    AND Roster.RosterEndDate IS NULL
GROUP BY Roster.calendarID, Roster.personID, Roster.enrollmentID, Term.startDate, Roster.PeriodScheduleName

--SELECT * FROM #Schedule

INSERT INTO #CustomCalendar
SELECT Calendar.calendarID, LTRIM(RTRIM(CC1612.BreakfastStartTimeEnglish)), LTRIM(RTRIM(CC1614.BreakfastStartTimeSpanish)),
         LTRIM(RTRIM(CC1615.SWTSParticipation)), LTRIM(RTRIM(CC1619.SWTSNameEnglish)), LTRIM(RTRIM(CC1620.SWTSNameSpanish)),
         LTRIM(RTRIM(CC1621.SWTSDateEnglish)), LTRIM(RTRIM(CC1622.SWTSDateSpanish)), LTRIM(RTRIM(CC1624.SWTSTimeEnglish)),
         LTRIM(RTRIM(CC1625.SWTSTimeSpanish)), LTRIM(RTRIM(CC1626.SWTSLocationEnglish)), LTRIM(RTRIM(CC1627.SWTSLocationSpanish)),
         LTRIM(RTRIM(CC1628.HomeroomsOnFOL))
FROM OPS.dbo.Calendar AS Calendar WITH (NOLOCK)
    INNER JOIN #ParsedCalendars AS ParsedCalendars
      ON Calendar.calendarID = ParsedCalendars.calendarID
    LEFT JOIN (SELECT calendarID, value AS BreakfastStartTimeEnglish
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1612) AS CC1612
      ON CC1612.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS BreakfastStartTimeSpanish
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1614) AS CC1614
      ON CC1614.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS SWTSParticipation
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1615) AS CC1615
      ON CC1615.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS SWTSNameEnglish
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1619) AS CC1619
      ON CC1619.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS SWTSNameSpanish
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1620) AS CC1620
      ON CC1620.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS SWTSDateEnglish
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1621) AS CC1621
      ON CC1621.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS SWTSDateSpanish
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1622) AS CC1622
      ON CC1622.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS SWTSTimeEnglish
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1624) AS CC1624
      ON CC1624.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS SWTSTimeSpanish
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1625) AS CC1625
      ON CC1625.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS SWTSLocationEnglish
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1626) AS CC1626
      ON CC1626.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS SWTSLocationSpanish
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1627) AS CC1627
      ON CC1627.calendarID = Calendar.calendarID
    LEFT JOIN (SELECT calendarID, value AS HomeroomsOnFOL
               FROM OPS.dbo.CustomCalendar WITH (NOLOCK)
               WHERE attributeID = 1628) AS CC1628
      ON CC1628.calendarID = Calendar.calendarID

--SELECT * FROM #CustomCalendar
END

IF @report = 2
BEGIN
INSERT INTO #StudentCount
SELECT calendarID, CONVERT(DECIMAL, COUNT(DISTINCT personID))
FROM #Student
GROUP BY calendarID

INSERT INTO #StudentAddressCount
SELECT DistinctAddresses.calendarID, SUM(DistinctAddresses.StudentAddressCount)
FROM (SELECT calendarID, personID, CONVERT(DECIMAL, COUNT(DISTINCT addressLine1)) AS StudentAddressCount
FROM #Household
GROUP BY calendarID, personID) AS DistinctAddresses
GROUP BY DistinctAddresses.calendarID INSERT INTO #SpanishSpeakingStudentCount
SELECT calendarID, CONVERT(DECIMAL, COUNT(DISTINCT personID))
FROM #Student
WHERE CorrespondenceLanguage = 'Spanish'
GROUP BY calendarID

INSERT INTO #NonSpanishSpeakingStudentCount
SELECT calendarID, CONVERT(DECIMAL, COUNT(DISTINCT personID))
FROM #Student
WHERE CorrespondenceLanguage <> 'Spanish'
GROUP BY calendarID
END

SELECT DISTINCT Student.personID, Student.studentNumber, Student.lastName, Student.firstName, Student.middleName, Student.fullName,
                Student.grade, Student.gender, Student.calendarStructure, Student.ECSE, Student.CorrespondenceLanguage, Schedule.scheduleName,
                Schedule.firstDay, Schedule.startTime, Schedule.endTime, Schedule.firstDay_WeekDayEnglish, Schedule.firstDay_MonthEnglish,
                Schedule.firstDay_WeekDaySpanish, Schedule.firstDay_MonthSpanish, Schedule.firstDay_Day, Schedule.firstDay_Year,
                Homeroom.roomName, HomeRoom.teacherName, Household.addressLine1, Household.city, Household.[state], Household.zip, Household.householdPhone,
                Student.calendarID, Student.calendarName, Student.schoolType, Student.schoolName, Student.schoolAddress,
                Student.schoolCity, Student.schoolState, Student.schoolZip, CustomCalendar.BreakfastStartTimeEnglish, CustomCalendar.BreakfastStartTimeSpanish,
                CustomCalendar.SWTSParticipation, CustomCalendar.SWTSNameEnglish, CustomCalendar.SWTSNameSpanish, CustomCalendar.SWTSDateEnglish,
                CustomCalendar.SWTSDateSpanish, CustomCalendar.SWTSTimeEnglish, CustomCalendar.SWTSTimeSpanish, CustomCalendar.SWTSLocationEnglish,
                CustomCalendar.SWTSLocationSpanish, CustomCalendar.HomeroomsOnFOL,
                COALESCE(StudentCount.StudentCount, 0) AS StudentCount, COALESCE(StudentAddressCount.StudentAddressCount, 0) AS StudentAddressCount,
                COALESCE(SpanishCount.SpanishCount, 0) AS SpanishCount,
                COALESCE(CONVERT(DECIMAL(7,2), SpanishCount.SpanishCount / StudentCount.StudentCount * 100), 0) AS SpanishPercentage,
                COALESCE(NonSpanishCount.NonSpanishCount, 0) AS NonSpanishCount,
                COALESCE(CONVERT(DECIMAL(7,2), NonSpanishCount.NonSpanishCount / StudentCount.StudentCount * 100), 0) AS NonSpanishPercentage
FROM #Student AS Student
LEFT JOIN #StudentCount AS StudentCount
    ON Student.calendarID = StudentCount.calendarID
LEFT JOIN #StudentAddressCount AS StudentAddressCount
    ON Student.calendarID = StudentAddressCount.calendarID
LEFT JOIN #SpanishSpeakingStudentCount AS SpanishCount
    ON Student.calendarID = SpanishCount.calendarID
LEFT JOIN #NonSpanishSpeakingStudentCount AS NonSpanishCount
    ON Student.calendarID = NonSpanishCount.calendarID
LEFT JOIN #Homeroom AS Homeroom
    ON Student.enrollmentID = Homeroom.enrollmentID
   AND Student.personID = Homeroom.personID
   AND Student.calendarID = Homeroom.calendarID
LEFT JOIN #Schedule AS Schedule
    ON Student.personID = Schedule.personID
   AND Student.calendarID = Schedule.calendarID
   AND Student.enrollmentID = Schedule.enrollmentID
LEFT JOIN #Household AS Household
    ON Student.personID = Household.personID
   AND Student.calendarID = Household.calendarID
   AND Student.enrollmentID = Household.enrollmentID
LEFT JOIN #CustomCalendar AS CustomCalendar
    ON Student.calendarID = CustomCalendar.calendarID

--debug
--ORDER BY Student.schoolName, Student.calendarName, Student.grade, Student.lastName, Student.firstName, Household.addressLine1
--end debug

--Clean up all temp tables
IF OBJECT_ID('tempdb..#ParsedCalendars') IS NOT NULL DROP TABLE #ParsedCalendars

IF OBJECT_ID('tempdb..#ParsedGrades') IS NOT NULL DROP TABLE #ParsedGrades