Report and Query Builder - Genius CE & Enterprise

Introduction 

Genius CE & Enterprise offer 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 CE & Enterprise - 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 CE & Enterprise, tailoring the user experience to your needs. 

Using the Genius CE & Enterprise Report Builder 

For administrators less familiar with SQL, we recommend starting with the Report Builder included in Genius CE & Enterprise. 

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 CE & Enterprise 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 CE & Enterprise 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 CE or Enterprise.  

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,exitdate)>getdate()-7 order by 1