*Please note that if you have customized your instance of Genius, your site might operate differently.
Introduction
Genius offers over 50 built-in reports to help you gather information on various aspects such as:
Enrollments
Completions
Learner engagement
Performance
These reports can be filtered and saved, allowing you to quickly narrow down the information to what you need.
Genius Report Builder
Administrators and other authorized users can create reports using an intuitive graphical user interface. Alternatively, users can build their own SQL queries for a more open-ended reporting experience. Both methods allow you to create visualizations and widgets for role-based dashboards in Genius, tailoring the user experience to your needs.
Using the Report Builder
For administrators less familiar with SQL, we recommend starting with the Report Builder included in Genius.
To access the Report Builder:
Click on the Reports tab.
Scroll down to the Report Builder menu item on the left sidebar.
Building a Report in the Report Builder:
If you make edits to an existing report that was built with the Report Builder, select the report from the dropdown list in the Step 1 section. If you build a new report from scratch, select [Creating New Report].
In the Step 2 section, determine which fields are required for your report. These fields will become the columns in the report.
If you know the name of the field you would like to add, it is often easier to search for the field using the Filter Available Fields.
Add the fields to the Selected Fields area by clicking on the field you would like to add to the report and clicking on the arrow button pointing towards the Selected Fields.
The arrow buttons and the “x” to the right of the Selected Fields is used to organize/remove the Selected Fields. The order they are listed is the order of the columns in the completed report.
The Step 3 section is where we add the required filters to reduce the shown information to only the required information.
Use the dropdown list in the first field in Step 3 to choose the variable to filter by.
Use the dropdown list in the second field to add the required logic needed to make the filter work as planned.
The third field is a blank textbox that is used to enter the value we are filtering by in correlation with the field chosen in the first field of Step 3.
For example: If creating a report that shows the enrollments of every learner in a specific affiliation, I would select “Learners – Affiliation” in the first field as the field we are filtering by, “Is” in the second field, and then type out the name of the Affiliation in the third field.
It is possible to add more than one filter to a report in the Report Builder. To do this, click the “+” button to the right of the filter you would like to enter. This will lock in that filter and allow a second filter to be created. If you would like to remove a filter, click on the “-“ button.
Test the report by click the “Get Data” button and viewing the report at the bottom of the screen.
If the report shown does not need any edits, save the report in the Step 5 section by adding a name to the “Report Name” field and clicking the “Save” button.
Saved reports will appear in both the “Report Builder” and “Run Reports” menu items in the Reports tab sidebar. You can view the SQL statement generated by the Report Builder and the results of the report you created.
Query Builder
Introduction to the Query Builder
The Query Builder is a feature in Genius that allows users to create reports using SQL queries. This method provides a more open-ended reporting experience compared to the graphical user interface, enabling users to write custom SQL queries to extract and analyze data according to their specific needs.
Benefits of the Query Builder
Flexibility: The Query Builder allows users to write custom SQL queries, providing a more open-ended reporting experience compared to the graphical user interface. This flexibility enables users to extract and analyze data according to their specific needs1.
Customization: Users can tailor their reports to include specific data points and criteria, ensuring that the reports meet their unique requirements. This level of customization is particularly useful for complex reporting needs1.
Advanced Data Analysis: By using SQL queries, users can perform advanced data analysis and create detailed reports that may not be possible with the standard built-in reports. This capability is beneficial for users who need to delve deeper into their data1.
Efficiency: The Query Builder streamlines the process of creating reports by allowing users to write and execute SQL queries directly within the Genius platforms. This efficiency can save time and effort, especially for users who are proficient in SQL1.
Integration: Reports created using the Query Builder can be integrated with role-based dashboards in Genius CE & Enterprise, allowing users to create visualizations and widgets that enhance the overall user experience.
Using the Query Builder
To construct your report utilizing SQL, it is essential to know the names of the variables you intend to include and the respective tables to which they belong. This information is available in the "Data Dictionary" report within the Query Builder. Begin by selecting and executing the Data Dictionary report from the "Select a Report" field in the Query Builder.
After determining the variables, choose [Creating New Report] from the “Select a report” dropdown list to begin with a blank template. Construct your SQL report utilizing the variables in the Query field. Upon completion, click the “Run Report” button to generate the report and verify that all data is accurately displayed.
Saving the Report and Role Permissions
When saving a report in the Query Builder, there are a few things to keep in mind.
Should the report be available on the main menu on the left of the screen. How should the data be displayed.
List
Pie Chart
Line Chart
Bar Chart
What roles should the report be available to.
If no roles are selected in the “Available to Roles” section, then the report will only be available to the creator of the report.
Once the required fields are filled in, click the “Save Changes” button to save the report.
Required Fields
Name of the report
The report format
A working query
Query Subscriptions
Once you have the SQL statement, you can create a "Subscription" that can be sent out to many different 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.
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.
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 studenttoaffiliation on students.studentindex=studenttoaffiliation.StudentIndex
inner join affiliations on affiliations.affiliationindex=studenttoaffiliation.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 studenttoaffiliation on students.studentindex=studenttoaffiliation.StudentIndex
inner join affiliations on affiliations.affiliationindex=studenttoaffiliation.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,exi