Creating your Own Reports in Genius

Introduction

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 learnt 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 in 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 [e-mail] 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 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 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 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 number of assignments behind 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 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 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