1. iClassPro Support
  2. Knowledgebase
  3. Data Warehouse

What is the Power BI Template? (Student Participation Reports)

The features described in this article are only available to customers with iClassPro subscription plans that provide access to the Data Warehouse.

IN THIS ARTICLE:
Learn more about the different Student Participation-related reports in the Power BI Template provided with your Data Warehouse account, what information is included, and how it is calculated.

What is Power BI?

Power BI is a collection of software services, apps, and connectors from Microsoft™ that allow you to pull together different data resources and manipulate them to prepare reports, charts, and/or tables to view the information you need more easily. This can make it easier to obtain metrics that may not be easily available using the reports built into iClassPro, such as comparing data from several different locations or accounts in a single view.

Using Power BI’s Power Query you can manipulate report data in different ways than is allowed in iClassPro. For example, if you have several Class Levels that begin with the same prefix (such as Dolphins – Beginner, Dolphins – Intermediate, and Dolphins – Advanced), iClassPro’s Level Summary Report will treat each of these as a separate Class Level. However, in Power BI you can group these Class Levels by their shared prefix and therefore calculate enrollment data for all of the “Dolphins” Class Levels together.

What is the Power BI Template?

iClassPro’s ready-to-publish Power BI starter template consists of over 30 pages of reports incorporating tables and charts focusing on the following topics:

  • Enrollments
  • Student Demographics
  • Year-to-Date Progress
  • Charges and Payments
  • Entry Points
  • Exit Points
  • Family and Student Participation
  • Customer Portal Usage

By referring to the queries provided in the starter template, you can learn more about the database schema and how we are calculating specific metrics. From here, you can add, delete, or edit queries as needed to fit the needs of your business.

Definitions

  • “ACTIVE” (shown in all capital letters) refers to an enrollment type. “Active” (shown in mixed-case or all lower-case letters) refers to an enrollment status.
    • An “active” enrollment status means that the start date of a specific enrollment must be equal to or prior to the start of a defined date range or specific date, and the enrollment drop date is equal to or PRIOR to the end of a defined date range or specific date.
  • Whenever it is used, the term "Customer Portal" actually refers to any customer self-service option (both the web-based Customer Portal and Mobile/Branded App).
  • A “slicer” is a "filter" that appear at the top of a report and allows users to amend the criteria used to calculate the results being displayed.
    • A “relative slicer” is a slicer where all dates are relative to the current date (today). Options for these slicers use a modifying article such as Next/Last/This etc.
  • A “data point” is a block on a bar/column, or a single result within any chart.
  • A “legend” is a section in a chart that explains the meaning of particular colors, indicators, etc.

Important Notes

  • As these reports are based on data retrieved from the Data Warehouse, data as of the “Current Date” could actually be up to 24 hours old depending on when your data was last updated.
  • Certain reports have default values for dates/date ranges. If needed, these can be changed by adjusting the date/date range slicer at the top of the report.
    • If a default value exists, this will be noted in the report description.
  • Unless noted otherwise, all reports will default to showing data divided by Site/Location if multiple Locations exist in your account.
  • To be included in the "Exit Age" results, a student must have no activity within the past 90 days (i.e., their most recent drop date must be at least 90 days prior to the current date).
  • Whenever multiple options exist (such as Site/Location,Gender, Program, Level, etc.), the report can be limited to a single option by selecting it from the appropriate slicer on the report. Multiple options can be selected by holding down CTRL/COMMAND and clicking on the option(s) to enable/disable them
    • NOTE: For the purposes of analyzing data/performing calculations, selecting ALL options or selecting NO options will be treated in the same manner.
  • CTRL/COMMAND refers to keys on PC/Mac keyboards, respectively.
  • If a vertical line appears on a chart or graph, this indicates the average value across all Sites/Locations to be used for comparison.
  • When viewing charts and graphs, additional options may be shown in the upper right-hand corner. (Explanations are given below from L-R):
    power_bi_filters.png
    • [Up arrow] will move up one level in the data hierarchy. (For example, if you are viewing specific student data, this would return to looking at data for the Site/Location as a whole).
    • [Down arrow] will enable you to “drill down” into the data by clicking on a specific data point on the chart/graph.
    • [Two down arrows] will move down one level in the data hierarchy. (For example, if you are viewing Site/Location data as a whole, this would “drill down” to look only at the specific student data used to calculate the totals).
    • [“Split” down arrow] expands the data to add an additional hierarchy level to the current view. (For example, if you are viewing Site/Location data as a whole, this would “drill down” to look only at both this information AND the specific student data used to calculate the totals).
    • [Funnel] Filters on Visual - shows the filters that are currently applied to the data reflected on the chart/graph.
    • [Expand window] Focus mode - will “zoom in” to show ONLY the selected chart/graph, hiding all other information on the report. (To exit this view, click the “Back to report” link in the upper left-hand corner of the window.)
    • [...] More options - there are several options here, but some of the most useful will be:
      • Export data - allows you to export the data related to the selected chart/graph in CSV format.
      • Show as a table - will display the data related to the selected chart/graph as a table.
      • Remove - allows you to remove the chart/graph completely from the report. (This is not recommended as this will permanently alter the template and you will not be able to restore the chart/graph if you wish to use it later.)
      • Spotlight - toggles “Spotlight” mode, which highlights only the selected chart/graph and dims the rest of the report. (To exit this mode, simply select “Spotlight” again to toggle it off.)
      • Sort axis - allows you to change the sort order of items along the axis of the chart/graph.
  • Wherever they exist on a report, indicators (such as filled shapes or arrows) can be configured to show trends based on your specified range of values. For more information on using conditional formatting to set up these indicators, see the “Apply conditional formatting in tables and matrixes” document from Microsoft.

Student Participation

Entry Age

powerbi_students01.png

This report includes:

  • Calculations of the “Average Entry Age” and “Average Partipation” (in months) of a student based on the information provided in the other charts/tables.
  • A chart showing the “Age at First Enrollment.” This age is always calculated based on the student’s birthdate and the start date of their first enrollment in your database.
  • Tables showing the “Age at Entry” and “Average Duration by Entry Age.”
    • The “Age at Entry” is a percentage of students who began their enrollment at a specific age between 1-20 years. This percentage is calculated based on the student’s birthdate and the start date of their first enrollment in your database.
    • The “Average Duration by Entry Age” is the average number of months a student was actively enrolled from the start date of their first enrollment to the drop date of their last enrollment. This amount is given for each “Age at Entry” between 1-20 years.
      • NOTE: If the student has an active enrollment without an assigned drop date, the total of active months enrolled is calculated based on the start date of their first enrollment to the current date.

This report allows you to see at a glance the average Entry Age and duration of enrollment in months for students enrolled at selected Location(s).

Entry Level

powerbi_students02.png

This report includes:

  • A table showing the “Student Enrollment - Entry Level.” This reflects the number of students whose first enrollment is in a class associated with a specific Class Level.
    • A graphical representation for each of these metrics is presented to the right of the table (based on the number of total students whose first enrollments were in classes associated with the specified Class Level).
  • The “Entry Level Not Defined” area will display the total number of students whose first enrollment was in a class without a Class Level attached. If no students have first enrollments that meet these criteria, the word “(Blank)” will be displayed here (as shown in the screenshot above).

This report allows you to see at a glance which Class Level(s) are the most common point of entry for students at your Site(s)/Location(s) for any selected year/month.

Exit Age

powerbi_students03.png

This report includes:

  • Calculations of the “Average Exit Age” and “Average Partipation” (in months) of a student based on the information provided in the other charts/tables.
  • A chart showing the “Age at Date of Last Class.” This age is always calculated based on the student’s birthdate and the drop date of their final enrollment in your database.
  • Tables showing the “Age at Exit” and “Average Duration by Exit Age.”
    • The “Age at Exit” is a percentage of students who ended their enrollment at a specific age between 1-20 years. This percentage is calculated based on the student’s birthdate and the drop date of their final enrollment in your database.
    • The “Average Duration by Exit Age” is the average number of months a student was actively enrolled from the start date of their first enrollment to the drop date of their final enrollment. This amount is calculated by Gender.

Note that to be included in the "Exit Age" results, a student must have no activity within the past 90 days (i.e., their most recent drop date must be at least 90 days prior to the current date).

This report allows you to see at a glance the average Exit Age and duration of enrollment in months for students enrolled at at selected Location(s).

Exit Level

powerbi_students04.png

This report includes:

  • A table showing the “Student Exit Level.” This reflects the number of students whose final enrollment is in a class associated with a specific Class Level.
  • A graphical representation for each of these metrics is presented to the right of the table (based on the number of total students whose final enrollments were in classes associated with the specified Class Level).
  • The “Exit Level Not Defined” area will display the total number of students whose final enrollment was in a class without a Class Level attached. If no students have final enrollments that meet these criteria, the word “(Blank)” will be displayed here (as shown in the screenshot above).
    • Note that to be included in the "Exit Level" results, a student must have no activity within the past 90 days (i.e., their most recent drop date must be at least 90 days prior to the current date).

This report allows you to see at a glance which Class Level(s) are the most common point of exit for students at your Site(s)/Location(s) for any selected year/month.

Combined Entry/Exit Level

 

powerbi_students05.png

This report includes:

  • A graph showing “Student enrollment - Entry Level.” This graph reflects the number of students whose first enrollment is in a class associated with a specific Class Level.
  • The “Entry Level Not Defined” area will display the total number of students whose first enrollment was in a class without a Class Level attached. If no students have first enrollments that meet these criteria, the word “(Blank)” will be displayed here.
  • A graph showing “Student enrollment - Exit Level.” This graph reflects the number of students whose final enrollment is in a class associated with a specific Class Level.
  • The “Exit Level Not Defined” area will display the total number of students whose final enrollment was in a class without a Class Level attached. If no students have final enrollments that meet these criteria, the word “(Blank)” will be displayed here.

Note that to be included in the "Exit Level" results, a student must have no activity within the past 90 days (i.e., their most recent drop date must be at least 90 days prior to the current date).

This report allows you to see at a glance which Class Level(s) are the most common point of entry and/or exit for students at your Site(s)/Location(s) for any selected year/month.

Entry Program

powerbi_students06.png

This report includes:

  • A table showing the “Student Enrollment - Entry Program.” This reflects the number of students whose first enrollment is in a class associated with a specific Program.
    • A graphical representation for each of these metrics is presented to the right of the table (based on the number of total students whose first enrollments were in classes associated with the specified Program).

This report allows you to see at a glance which Program(s) are the most common point of entry for students at your Site(s)/Location(s) for any selected year/month.

Exit Program

powerbi_students07.png

This report includes:

  • A table showing the “Student Enrollment - Exit Program.” This reflects the number of students whose final enrollment is in a class associated with a specific Program.
    • A graphical representation for each of these metrics is presented to the right of the table (based on the number of total students whose final enrollments were in classes associated with the specified Program).

Note that to be included in the "Exit Program" results, a student must have no activity within the past 90 days (i.e., their most recent drop date must be at least 90 days prior to the current date).

This report allows you to see at a glance which Program(s) are the most common point of exit for students at your Site(s)/Location(s) for any selected year/month.

Combined Entry/Exit Program

powerbi_students08.png

This report includes:

  • A table showing the “Student Enrollment - Entry Program.” This reflects the number of students whose first enrollment is in a class associated with a specific Program.
    • A graphical representation for each of these metrics is presented below the table (based on the number of total students whose first enrollments were in classes associated with the specified Program).
  • A table showing the “Student Enrollment - Exit Program.” This reflects the number of students whose final enrollment is in a class associated with a specific Program.
    • A graphical representation for each of these metrics is presented below the table (based on the number of total students whose final enrollments were in classes associated with the specified Program).

Note that to be included in the "Exit Program" results, a student must have no activity within the past 90 days (i.e., their most recent drop date must be at least 90 days prior to the current date).

This report allows you to see at a glance which Program(s) are the most common point of entry and/or exit for students at your Site(s)/Location(s) for any selected year/month.

Overall Participation (Student Participation)

powerbi_students09.png

This report includes:

  • The “Average Participation (mths)” is the average number of months a student was actively enrolled from the start date of their first enrollment to the drop date of their final enrollment. This amount is calculated by Gender.
    • NOTE: If the student has an active enrollment without an assigned drop date, the total of active months enrolled is calculated based on the start date of their first enrollment to the current date.
  • A graph showing the average “Participation Timeframe by Site.” This reflects the average number of months a student was actively enrolled from the start date of their first enrollment to the drop date of their final enrollment.
    • NOTE: If the student has an active enrollment without an assigned drop date, the total of active months enrolled is calculated based on the start date of their first enrollment to the current date.
  • A table showing “Participation by Gender.” This reflects the average number of months a student was actively enrolled from the start date of their first enrollment to the drop date of their final enrollment. This is broken down by gender and also includes a “Total” column with the average participation as a whole. The final row of this table calculates an average participation for all enrollments broken down by gender with an additional column for the average participation as a whole.
    • NOTES:
      • The “Total” column is based on the original values of the sum of the total duration in months for all enrollments divided by the total number of enrollments. Therefore, it is important to note that this value will not match the value given if you simply average the gender totals, as that calculation does not use the complete dataset.
        • This is also true for the totals given in the final row, as these totals are also calculated based on the complete dataset.
      • If the student has an active enrollment without an assigned drop date, the total of active months enrolled is calculated based on the start date of their first enrollment to the current date.
  • A table showing the “% of Intake.” This reflects the percentage of total students who begin taking classes at a specified age (between 1-20 years).

This report allows you to see at a glance the average participation in months for students enrolled in classes at your Site(s)/Location(s), along with a breakdown by gender and age.

Student Attendance

powerbi_students10.png

This report includes:

  • A chart showing the average “Class Attendance Per Student.” This reflects the percentage of class meetings for which students were marked “Present” compared to the total number of class meetings.
  • A chart showing the average “Camp Attendance Per Student.” This reflects the percentage of camp meetings for which students were marked “Present” compared to the total number of camp meetings.

By default, these values are only calculated for students with an active enrollment.

This report allows you to see at a glance the average class/camp attendance for students enrolled at selected Location(s).

Active Student Age Now

powerbi_students11.png

This report includes:

  • Calculations of the “Average Entry Age”, “Average Exit Age”, and “Average Participation” (in months) of a student at the selected Site(s)/Location(s). These values are calculated as follows.
    • “Average Entry Age” - always calculated based on the student’s birthdate and the start date of their first enrollment in your database.
    • “Average Exit Age” - always calculated based on the student’s birthdate and the drop date of their final enrollment in your database.
      • NOTE: To be included in the "Exit Age" results, a student must have no activity within the past 90 days (i.e., their most recent drop date must be at least 90 days prior to the current date).
    • “Average Participation” - the average number of months a student was actively enrolled from the start date of their first enrollment to their exit date (drop date of their final enrollment). This is broken down by gender.
  • A table showing Average Participation by gender. This reflects the average number of months a student was actively enrolled from the start date of their first enrollment to the drop date of their final enrollment. This is broken down by gender.
    • NOTE: The “Average Participation” total shown above this table is based on the original values of the sum of the total duration in months for all enrollments divided by the total number of enrollments. Therefore, it is important to note that this value will not match the value given if you simply average the gender totals, as that calculation does not use the complete dataset.
  • A table showing “Current Students - Age.” This reflects the number of current students at each selected Site/Location by age group (age 1-17) along with a “Total” column reflecting the current number of students enrolled at each selected Site/Location. The bottom row of the table reflects the Total for all students enrolled by each Age Group for all selected Sites/Locations.
    • NOTE: Selecting any data point in this chart will recalculate totals for all other tables/charts.
  • A bar chart showing the percentage of students enrolled in each selected Site/Location by gender.

By default, these values are only calculated for students of all genders with an active enrollment.

This report allows you to see at a glance important information related to student ages, average overall enrollment duration, and current participation data enrolled at selected Location(s).

Birthdays Next 7 Days

powerbi_students12.png

This report includes:

  • The total number of students with birthdays within the next seven days (calculated from the current date).
  • A table breaking down the number of students with birthdays within the next seven days for each Site/Location.

By default, these values are only calculated for students with an active enrollment.

This report allows you to see at a glance the number of students with upcoming birthdays (within the next seven days) that are enrolled at selected Location(s).

Time in Level (Average time spent in each Level)

powerbi_students13.png

This report includes the following information (calculated based on time spent in classes associated with a particular Class Level):

  • A table showing the “Average student duration in each Level by site”, divided by Site/Location, along with a “Total” showing the average duration (in months) across all Sites/Locations.
    • This is the average amount of time (in months) a student spends in a class associated with a specific Class Level, calculated from their start date in a class with that Level attached through the most recent drop date in any other class attached to the same Class Level.
    • The start indicator designates how a specific Site/Location is performing in relation to the top-performing Site/Location.
      • NOTE: If the student has an active enrollment without an assigned drop date, the total of active months enrolled is calculated based on the start date of their first enrollment to the current date.
  • A chart showing the “Average duration in Enrollment Level”, divided by Class Level.
    • This is a graphical representation of the data displayed in the “Total” row of the “Average student duration in each Level by site” table.

By default, these values are calculated for enrollments in all Programs at all Sites/Locations.

This report allows you to see at a glance the average time spent (in months) in class(es) associated with a specific Class Level and/or Program at selected Location(s).

Learn More!

Need more assistance?

Customer support is available at 1-877-554-6776 Mon - Fri, 9 am to 9 pm (CT) and Sat, 9 am to 6 pm (CT).