Introduction 2.x
Seeing something new? Click here to scroll down to the Classic version of this article
As we described in our article about Enrollment Reports in Genius, the System has over 50 built-in reports that you can use to “pull” information about enrollments, completions, learner engagement, and performance across your entire learning initiative. Reports in Genius can also be filtered and saved, allowing you to quickly reduce the amount of information that is made available as a result of running a report to get to the information you need.
However, one of the more frequent questions that our users have is: “How do I create my own reports using Genius?” In this article, we provide you with a brief overview of the two methods available for you to create your own reports in Genius. In the first one, the Genius Report Builder, Administrators, and other authorized users can create Reports using a graphical user interface that is easy to interpret and use. Alternatively, users can also build their own SQL queries for a completely open-ended reporting experience.
In both cases, you can use the Reports you create in Genius to create visualizations and ‘widgets’ that you can use in the role-based dashboards included with Genius. This is a powerful way to tailor the user experience in our Solution.
When you are finished with this article, you will have learned how to use Genius to create your own reports using both the graphical Report Builder and the code-based SQL Query Builder. Both of these features require practice, so we will be adding additional worked examples over time in order to make it easier for you to create your own reports as your confidence with Genius increases over time.
The Genius Report Builder
For Administrators who are not SQL savvy, we highly recommend that you begin creating your own Reports through the Report Builder included with Genius. To access the Report Builder, click on the Reports tab, and then scroll down until you see the Report Builder menu item on the sidebar on the left-hand side of your screen. Through the Report Builder, you can pick and choose fields you want through a graphical user interface, and then determine filter criteria to reduce the size of the information pulled from your database. You can then save the report for future use.
When you save the report, you'll see it listed in both the “Report Builder” and under the “Run Reports” menu item in the sidebar of the Reports tab. In both options, you can see the SQL statement code that was generated by the "Report Builder", as well as the result of running the Report you just created. You can use this SQL code to customize the role-based dashboards in your instance of Genius:
The Genius SQL Query Builder
Administrators who are SQL savvy can write their own SQL statement and save it to be run on a regular basis using the “Query Builder”. These reports can be exposed to the user's Genius dashboard or emailed to them - providing you with a powerful tool to tailor the user experience in your instance of Genius.
Once you have the SQL statement, you can create a "Subscription" that can be sent out to many emails on a regular basis. To create a Subscription, simply click on the Add new subscription link that is available when you click on the Subscription menu item under the Custom Reports heading at the very bottom of the sidebar that appears when you click on the Reports tab in Genius.
We have included both a Data Structures diagram and a complete Data Dictionary with this article. We have also included a quick Guide to SQL Statements in Genius with this article, which provides you with a “quick start” guide to creating your own SQL statements through the Genius Query Builder. This should make it easier for you to start building the Reports you need to provide actionable information to the stakeholders in your learning initiative.
The Genius Support Team is available and at your disposal to help you create the reports and visualizations required by your organization. Please contact us at support@geniussis.com and we will be happy to provide you with expert-level advice on how to make the most out of the Genius reporting experience.
Examples of Useful SQL Statements
In this section, we provide you with a few examples of custom SQL statements that produce useful information and can help you create new queries or tailor these queries to the needs of your own learning initiative. We note that some of these are visualizations that you can use to tailor the role-based dashboards generated by your instance of Genius.
Enrollments active by affiliation
NOTE: This is a Pie Chart to track the number of enrollments active YTD
select tbl.Name+' ('+CONVERT(varchar,tbl.Total)+'%)' as [Label],tbl.Total as [Value] from (
select affiliations.name,ROUND(count(*) /
cast((select COUNT(*) from Enrollments where status='ACTIVE' and year(StartDate)=year(getdate())) as Float)*100,2) as [Total]
from enrollments
inner join students on students.studentindex=enrollments.studentindex
inner join affiliations on affiliations.affiliationindex=students.affiliationindex
where
year(startdate)=year(getdate())
and enrollments.status='ACTIVE'
group by affiliations.name
) as tbl
Enrollments completed by affiliation
NOTE: This is a Pie Chart to track the number of enrollments completed YTD
select tbl.Name+' ('+CONVERT(varchar,tbl.Total)+'%)' as [Label],tbl.Total as [Value] from (
select affiliations.name,ROUND(count(*) /
cast((select COUNT(*) from Enrollments where status='COMPLETED' and year(StartDate)=year(getdate())) as Float)*100,2) as [Total]
from enrollments
inner join students on students.studentindex=enrollments.studentindex
inner join affiliations on affiliations.affiliationindex=students.affiliationindex
where year(exitdate)=year(getdate())
and year(enrollments.startdate)=year(getdate())
and enrollments.status='COMPLETED'
group by affiliations.name
) as tbl
Enrollments completed by term
NOTE: This is a Pie Chart to track the number of enrollments completed YTD
select tbl.Name+' ('+CONVERT(varchar,tbl.Total)+'%)' as [Label],tbl.Total as [Value] from (
select LMSTerms.name,ROUND(count(*) /
cast((select COUNT(*) from Enrollments where status='COMPLETED' and year(StartDate)=year(getdate())) as Float)*100,2) as [Total]
from enrollments
inner join sections on sections.sectionindex=enrollments.sectionindex
inner join lmsterms on lmsterms.lmstermindex=sections.lmstermindex
where year(exitdate)=year(getdate())
and year(enrollments.startdate)=year(getdate())
and enrollments.status='COMPLETED'
group by lmsterms.name
) as tbl
Enrollments by status
NOTE: This is a Pie Chart to track the number of enrollments by status YTD
select Status Label, count(*) Value
from vEnrollmentsFilter
where (StartDate >= DateAdd(year, -1, GetDate()) or Status = 'ACTIVE')
--FilterEnrollments
group by Status
Enrollments by pace
NOTE: This is a Pie Chart to track the number of assignments behind the pace
declare @MinRange varchar(60) = (select Value from Parameters where Name = 'PACING_YELLOW_WEEKS_BEHIND');
declare @MaxRange varchar(60) = (select Value from Parameters where Name = 'PACING_RED_WEEKS_BEHIND');
declare @enr table (EnrollmentIndex int, AssigBehind float);
insert into @enr
select EnrollmentIndex, coalesce(AssignmentsBehind,0)
from vEnrollmentsFilter WITH (NOEXPAND)
where status = 'ACTIVE'
--FilterEnrollments
;
select 'On Pace' Label, count(*) Value, '#009933' Color
from @enr
where AssigBehind < @MinRange
UNION ALL
select @MinRange + ' - ' + @MaxRange + '% Behind' Label, count(*) Value, '#FFCC00'
from @enr
where AssigBehind between @MinRange and @MaxRange
UNION ALL
select '> ' + @MaxRange + '% Behind' Label, count(*) Value, '#CC0000'
from @enr
where AssigBehind > @MaxRange
Total Learner History by Month, YTD
NOTE: This is a Bar Chart to track the number of unique enrollments (Learners) by month YTD.
declare @auxtab table (NumMonth int,NameMonth varchar(2056),NumEnrollments int)Insert into @auxtab (NumMonth,NameMonth,NumEnrollments)select 1,'Jan' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=1 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 2,'Feb' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=2 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 3,'Mar' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=3 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 4,'Apr' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=4 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 5,'May' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=5 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 6,'Jun' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=6 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 7,'Jul' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=7 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 8,'Aug' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=8 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 9,'Sep' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=9 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 10,'Oct' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=10 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 11,'Nov' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=11 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 12,'Dez' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=12 and DATEPART(year,startdate)=DATEPART(year,GETDATE())
select [NameMonth] as [Month],NumEnrollments as [# of Unique Enrollments] from @auxtab
Total Enrollment history by Month YTD
NOTE: This is a Bar Chart to track the number of enrollments by month YTD
declare @auxtab table (NumMonth int,NameMonth varchar(2056),NumEnrollments int)Insert into @auxtab (NumMonth,NameMonth,NumEnrollments)select 1,'Jan' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=1 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 2,'Feb' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=2 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 3,'Mar' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=3 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 4,'Apr' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=4 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 5,'May' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=5 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 6,'Jun' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=6 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 7,'Jul' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=7 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 8,'Aug' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=8 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 9,'Sep' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=9 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 10,'Oct' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=10 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 11,'Nov' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=11 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 12,'Dez' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=12 and DATEPART(year,startdate)=DATEPART(year,GETDATE())
select Namemonth as [Month], (select sum(NumEnrollments) from @auxtab t2 where t2.NumMonth <= t.NumMonth ) as [# of Enrollments]from @auxtab t;
Completion by Employee – List
select top 10 s.LastName+', '+s.FirstName as Employee, aff.Name as Manager, COUNT(*) as Completionsfrom Students sinner join Enrollments e on e.StudentIndex=s.StudentIndexinner join Sections a on e.SectionIndex=a.SectionIndexinner join Courses c on a.CourseIndex=c.CourseIndexinner join Affiliations aff on s.AffiliationIndex=aff.AffiliationIndexwhere e.Status='COMPLETED' group by s.LastName+', '+s.FirstName, aff.Nameorder by 3 desc
Completion by Manager - List
select Manager,Enrollments, Completions, CONVERT(varchar,rate)+'%' as Rate from(select top 10 Manager,Enrollments, Completions, 100*Completions/(Enrollments+1) as Rate from(select Affiliations.Name as Manager,(select COUNT(*)from Students sinner join Enrollments e on e.StudentIndex=s.StudentIndexinner join Sections a on e.SectionIndex=a.SectionIndexinner join Courses c on a.CourseIndex=c.CourseIndexinner join Affiliations aff on s.AffiliationIndex=aff.AffiliationIndexwhere e.Status='COMPLETED' and aff.AffiliationIndex=affiliations.AffiliationIndex) as Completions,(select COUNT(*)from Students sinner join Enrollments e on e.StudentIndex=s.StudentIndexinner join Sections a on e.SectionIndex=a.SectionIndexinner join Courses c on a.CourseIndex=c.CourseIndexinner join Affiliations aff on s.AffiliationIndex=aff.AffiliationIndexwhere aff.AffiliationIndex=affiliations.AffiliationIndex) as Enrollmentsfrom Affiliations) as tbl order by 4 desc) as tbl2
Time to Complete – Pie Chart
select '<30 days' as 'Label',(select count(*) from enrollments where Status='completed' and DATEDIFF([day],startdate,exitdate) <30) as 'Value'unionselect '30-40 days',(select count(*) from enrollments where Status='completed' and DATEDIFF([day],startdate,exitdate) between 30 and 40) as [Value]unionselect '40-50 days',(select count(*) from enrollments where Status='completed' and DATEDIFF([day],startdate,exitdate) between 40 and 50) as [Value]unionselect '50+ days',(select count(*) from enrollments where Status='completed' and DATEDIFF([day],startdate,exitdate)>50) as [Value]
Weekly Completion by Affiliation
select affiliation as [Affiliation], course as [Course], COUNT(student) as [# of Learners], AVG(grade) as [Average Grade] from venrollments where convert(datetime,exitdate)>(getdate()-7) group by affiliation, course order by 1
Weekly Completion by Learner
select student as [Learner], affiliation as [Affiliation], COUNT(course) as [# of Courses], AVG(grade) as [Average Grade] from venrollments where convert(datetime,exitdate)>(getdate()-7) group by student, affiliation order by 1
Weekly Completion
select student, affiliation, course, grade from venrollments where convert(datetime,exitdate)>getdate()-7 order by 1
Introduction 1.x
As we described in our article about Enrollment Reports in Genius, the System has over 50 built-in reports that you can use to “pull” information about enrollments, completions, learner engagement, and performance across your entire learning initiative. Reports in Genius can also be filtered and saved, allowing you to quickly reduce the amount of information that is made available as a result of running a report to get to the information you need.
However, one of the more frequent questions that our users have is: “How do I create my own reports using Genius?” In this article, we provide you with a brief overview of the two methods available for you to create your own reports in Genius. In the first one, the Genius Report Builder, Administrators, and other authorized users can create Reports using a graphical user interface that is easy to interpret and use. Alternatively, users can also build their own SQL queries for a completely open-ended reporting experience.
In both cases, you can use the Reports you create in Genius to create visualizations and ‘widgets’ that you can use in the role-based dashboards included with Genius. This is a powerful way to tailor the user experience in our Solution.
When you are finished with this article, you will have learned how to use Genius to create your own reports using both the graphical Report Builder and the code-based SQL Query Builder. Both of these features require practice, so we will be adding additional worked examples over time in order to make it easier for you to create your own reports as your confidence with Genius increases over time.
The Genius Report Builder
For Administrators who are not SQL savvy, we highly recommend that you begin creating your own Reports through the Report Builder included with Genius. To access the Report Builder, click on the Reports tab, and then scroll down until you see the Report Builder menu item on the sidebar on the left-hand side of your screen. Through the Report Builder, you can pick and choose fields you want through a graphical user interface, and then determine filter criteria to reduce the size of the information pulled from your database. You can then save the report for future use.
When you save the report, you'll see it listed in both the “Report Builder” and under the “Run Reports” menu item in the sidebar of the Reports tab. In both options, you can see the SQL statement code that was generated by the "Report Builder", as well as the result of running the Report you just created. You can use this SQL code to customize the role-based dashboards in your instance of Genius:
The Genius SQL Query Builder
Administrators who are SQL savvy can write their own SQL statement and save it to be run on a regular basis using the “Query Builder”. These reports can be exposed to the user's Genius dashboard or emailed to them - providing you with a powerful tool to tailor the user experience in your instance of Genius.
Once you have the SQL statement, you can create a "Subscription" that can be sent out to many emails on a regular basis. To create a Subscription, simply click on the Add new subscription link that is available when you click on the Subscription menu item under the Custom Reports heading at the very bottom of the sidebar that appears when you click on the Reports tab in Genius.
We have included both a Data Structures diagram and a complete Data Dictionary with this article. We have also included a quick Guide to SQL Statements in Genius with this article, which provides you with a “quick start” guide to creating your own SQL statements through the Genius Query Builder. This should make it easier for you to start building the Reports you need to provide actionable information to the stakeholders in your learning initiative.
The Genius Support Team is available and at your disposal to help you create the reports and visualizations required by your organization. Please contact us at support@geniussis.com and we will be happy to provide you with expert-level advice on how to make the most out of the Genius reporting experience.
Examples of Useful SQL Statements
In this section, we provide you with a few examples of custom SQL statements that produce useful information and can help you create new queries or tailor these queries to the needs of your own learning initiative. We note that some of these are visualizations that you can use to tailor the role-based dashboards generated by your instance of Genius.
Enrollments active by affiliation
NOTE: This is a Pie Chart to track the number of enrollments active YTD
select tbl.Name+' ('+CONVERT(varchar,tbl.Total)+'%)' as [Label],tbl.Total as [Value] from (
select affiliations.name,ROUND(count(*) /
cast((select COUNT(*) from Enrollments where status='ACTIVE' and year(StartDate)=year(getdate())) as Float)*100,2) as [Total]
from enrollments
inner join students on students.studentindex=enrollments.studentindex
inner join affiliations on affiliations.affiliationindex=students.affiliationindex
where
year(startdate)=year(getdate())
and enrollments.status='ACTIVE'
group by affiliations.name
) as tbl
Enrollments completed by affiliation
NOTE: This is a Pie Chart to track the number of enrollments completed YTD
select tbl.Name+' ('+CONVERT(varchar,tbl.Total)+'%)' as [Label],tbl.Total as [Value] from (
select affiliations.name,ROUND(count(*) /
cast((select COUNT(*) from Enrollments where status='COMPLETED' and year(StartDate)=year(getdate())) as Float)*100,2) as [Total]
from enrollments
inner join students on students.studentindex=enrollments.studentindex
inner join affiliations on affiliations.affiliationindex=students.affiliationindex
where year(exitdate)=year(getdate())
and year(enrollments.startdate)=year(getdate())
and enrollments.status='COMPLETED'
group by affiliations.name
) as tbl
Enrollments completed by term
NOTE: This is a Pie Chart to track the number of enrollments completed YTD
select tbl.Name+' ('+CONVERT(varchar,tbl.Total)+'%)' as [Label],tbl.Total as [Value] from (
select LMSTerms.name,ROUND(count(*) /
cast((select COUNT(*) from Enrollments where status='COMPLETED' and year(StartDate)=year(getdate())) as Float)*100,2) as [Total]
from enrollments
inner join sections on sections.sectionindex=enrollments.sectionindex
inner join lmsterms on lmsterms.lmstermindex=sections.lmstermindex
where year(exitdate)=year(getdate())
and year(enrollments.startdate)=year(getdate())
and enrollments.status='COMPLETED'
group by lmsterms.name
) as tbl
Enrollments by status
NOTE: This is a Pie Chart to track the number of enrollments by status YTD
select Status Label, count(*) Value
from vEnrollmentsFilter
where (StartDate >= DateAdd(year, -1, GetDate()) or Status = 'ACTIVE')
--FilterEnrollments
group by Status
Enrollments by pace
NOTE: This is a Pie Chart to track the number of assignments behind the pace
declare @MinRange varchar(60) = (select Value from Parameters where Name = 'PACING_YELLOW_WEEKS_BEHIND');
declare @MaxRange varchar(60) = (select Value from Parameters where Name = 'PACING_RED_WEEKS_BEHIND');
declare @enr table (EnrollmentIndex int, AssigBehind float);
insert into @enr
select EnrollmentIndex, coalesce(AssignmentsBehind,0)
from vEnrollmentsFilter WITH (NOEXPAND)
where status = 'ACTIVE'
--FilterEnrollments
;
select 'On Pace' Label, count(*) Value, '#009933' Color
from @enr
where AssigBehind < @MinRange
UNION ALL
select @MinRange + ' - ' + @MaxRange + '% Behind' Label, count(*) Value, '#FFCC00'
from @enr
where AssigBehind between @MinRange and @MaxRange
UNION ALL
select '> ' + @MaxRange + '% Behind' Label, count(*) Value, '#CC0000'
from @enr
where AssigBehind > @MaxRange
Total Learner History by Month, YTD
NOTE: This is a Bar Chart to track the number of unique enrollments (Learners) by month YTD.
declare @auxtab table (NumMonth int,NameMonth varchar(2056),NumEnrollments int)Insert into @auxtab (NumMonth,NameMonth,NumEnrollments)select 1,'Jan' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=1 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 2,'Feb' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=2 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 3,'Mar' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=3 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 4,'Apr' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=4 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 5,'May' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=5 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 6,'Jun' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=6 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 7,'Jul' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=7 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 8,'Aug' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=8 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 9,'Sep' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=9 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 10,'Oct' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=10 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 11,'Nov' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=11 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 12,'Dez' as [Month],COUNT(distinct studentindex) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)<=12 and DATEPART(year,startdate)=DATEPART(year,GETDATE())
select [NameMonth] as [Month],NumEnrollments as [# of Unique Enrollments] from @auxtab
Total Enrollment history by Month YTD
NOTE: This is a Bar Chart to track the number of enrollments by month YTD
declare @auxtab table (NumMonth int,NameMonth varchar(2056),NumEnrollments int)Insert into @auxtab (NumMonth,NameMonth,NumEnrollments)select 1,'Jan' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=1 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 2,'Feb' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=2 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 3,'Mar' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=3 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 4,'Apr' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=4 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 5,'May' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=5 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 6,'Jun' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=6 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 7,'Jul' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=7 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 8,'Aug' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=8 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 9,'Sep' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=9 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 10,'Oct' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=10 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 11,'Nov' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=11 and DATEPART(year,startdate)=DATEPART(year,GETDATE())unionselect 12,'Dez' as [Month],COUNT(*) as [NumOfEnrollments] from enrollmentswhere DATEPART(month,startdate)=12 and DATEPART(year,startdate)=DATEPART(year,GETDATE())
select Namemonth as [Month], (select sum(NumEnrollments) from @auxtab t2 where t2.NumMonth <= t.NumMonth ) as [# of Enrollments]from @auxtab t;
Completion by Employee – List
select top 10 s.LastName+', '+s.FirstName as Employee, aff.Name as Manager, COUNT(*) as Completionsfrom Students sinner join Enrollments e on e.StudentIndex=s.StudentIndexinner join Sections a on e.SectionIndex=a.SectionIndexinner join Courses c on a.CourseIndex=c.CourseIndexinner join Affiliations aff on s.AffiliationIndex=aff.AffiliationIndexwhere e.Status='COMPLETED' group by s.LastName+', '+s.FirstName, aff.Nameorder by 3 desc
Completion by Manager - List
select Manager,Enrollments, Completions, CONVERT(varchar,rate)+'%' as Rate from(select top 10 Manager,Enrollments, Completions, 100*Completions/(Enrollments+1) as Rate from(select Affiliations.Name as Manager,(select COUNT(*)from Students sinner join Enrollments e on e.StudentIndex=s.StudentIndexinner join Sections a on e.SectionIndex=a.SectionIndexinner join Courses c on a.CourseIndex=c.CourseIndexinner join Affiliations aff on s.AffiliationIndex=aff.AffiliationIndexwhere e.Status='COMPLETED' and aff.AffiliationIndex=affiliations.AffiliationIndex) as Completions,(select COUNT(*)from Students sinner join Enrollments e on e.StudentIndex=s.StudentIndexinner join Sections a on e.SectionIndex=a.SectionIndexinner join Courses c on a.CourseIndex=c.CourseIndexinner join Affiliations aff on s.AffiliationIndex=aff.AffiliationIndexwhere aff.AffiliationIndex=affiliations.AffiliationIndex) as Enrollmentsfrom Affiliations) as tbl order by 4 desc) as tbl2
Time to Complete – Pie Chart
select '<30 days' as 'Label',(select count(*) from enrollments where Status='completed' and DATEDIFF([day],startdate,exitdate) <30) as 'Value'unionselect '30-40 days',(select count(*) from enrollments where Status='completed' and DATEDIFF([day],startdate,exitdate) between 30 and 40) as [Value]unionselect '40-50 days',(select count(*) from enrollments where Status='completed' and DATEDIFF([day],startdate,exitdate) between 40 and 50) as [Value]unionselect '50+ days',(select count(*) from enrollments where Status='completed' and DATEDIFF([day],startdate,exitdate)>50) as [Value]
Weekly Completion by Affiliation
select affiliation as [Affiliation], course as [Course], COUNT(student) as [# of Learners], AVG(grade) as [Average Grade] from venrollments where convert(datetime,exitdate)>(getdate()-7) group by affiliation, course order by 1
Weekly Completion by Learner
select student as [Learner], affiliation as [Affiliation], COUNT(course) as [# of Courses], AVG(grade) as [Average Grade] from venrollments where convert(datetime,exitdate)>(getdate()-7) group by student, affiliation order by 1
Weekly Completion
select student, affiliation, course, grade from venrollments where convert(datetime,exitdate)>getdate()-7 order by 1
Comments
0 comments
Please sign in to leave a comment.