The features described in this article are only available to customers subscribed to iClassPro's Premium Plan. For more details or to request additional information about our Premium Plan, visit https://iclasspro.com/premium.
IN THIS ARTICLE:
Learn more about the different Revenue-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.
- Abbreviations:
- YTD - Year to Date. This number is the total amount of charges/payments created as of the current date within the selected year.
- EOY - End of Year. This number is the total amount of charges/payments created for the entire selected year.
- LY YTD - Last Year, Year to Date. This number is the total amount of charges/payments created as of the current date within the year previous to the selected year.
- LY EOY - Last Year, End of Year. This number is the total amount of charges/payments created as of the end of the year previous to the selected year.
NOTE: if a past year is selected, YTD totals will be based on the current date within that year. For example, if the current date is July 6th and 2015 is selected, the YTD will reflect all charges created between January 1, 2015 and July 6, 2015.
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.
- 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):
- [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.
Revenue
Total Charges & Payments (Payments and Charges)
This report contains:
- The “Family Spend” table, showing the total amount of charges created, payments taken, payments applied, outstanding balances, and unapplied payments/credit. The table also includes grand totals for all Sites/Locations.
- The “AutoPay Status” graph, reflecting the AutoPay status of families, divided by the Payment Method.
- Hovering over the area of the chart corresponding with a specific Payment Method will display an information tooltip with the exact number of families utilizing that Payment Method.
- The “Invalid AutoPay Details” table, reflecting the number of families with invalid AutoPay details stored on file.
By default, the report is limited to details of families with an active enrollment as of the current date at the selected Site(s)/Location(s).
This report allows you to see at a glance what your financial data looks like for a specific Site/Location as of the current date. It also allows you to see how many of your customers have stored payment information on file and which Payment Methods are being utilized the most.
Monthly Revenue
This report contains:
- A table showing total charges created during a specified period of time, as well as a line graph that reflects this same information.
- A separate table reflecting just the total amount of tax line items created during a specified period of time.
- NOTE: The tax line items are also included as part of the charge amounts in the table of total charges.
This report allows you to see at a glance how the amount of created charges was trending for a specific Site/Location for a selected year, quarter, month, or day. You can limit the report further by selecting one or more specific Site(s)/Location(s) to compare data between them.
Annual Performance (Annual Revenue - Growth)
This report contains:
- A “Revenue” table showing the total amount of charges created during a specified year/years.
- A “Percentage Growth” table, showing the growth trend (in percentages) based on comparing the total amount of charges created during a specified year with the same data for the previous year.
- An indicator will also be displayed that designates whether the trend is upward, downward, or continuing on a level basis.
- A “Financial Growth” table displaying the same information, but with the actual totals displayed, as well as columns giving values for “LY EOY” (Last Year, End of Year) and “EOY” (End of Year).
- NOTE: By default, this table intentionally does not account for discounts or deleted charges.
- An “Annual Revenue” pie chart. It is important to note that if more than one year is selected then the totals are cumulative for all of the selected years.
This report allows you to see at a glance how the amount of created charges was trending for one or more selected years. You can limit the report further by selecting one or more specific Site(s)/Location(s) to compare data between them.
YTD Progress (Year to Date Targets)
This report contains:
- A YTD and EOY graph comparing the total amount of charges created as of the current date (YTD) within the selected year with the total amount of charges created for the entire year (EOY).
- Percentage of YTD vs LY YTD. Reflects the percentage of charges created as of the current date during the selected year vs. the same date range for the previous calendar year.
- Percentage of EOY vs LY EOD. Reflects the percentage of charges created as of the end of the selected calendar year vs. the end the previous calendar year.
- Note that if the current year is selected, the EOY amount will always equal the YTD.
- A chart showing YTD vs EOY vs LY YTD. This chart compares the YTD amount of the selected year with the EOY amount. A benchmark is set using the LY YTD, allowing you to look at how these numbers compare to total charges historically.
- A table showing EOY vs. YTD totals, with Grand Totals for all Sites/Locations.
- A Percentage Calculation for YTD vs LY YTD table. Reflects the percentage of change for the current YTD when compared to the same date range for the previous calendar year. An indicator is displayed to allow you to easily see whether this trend is up, down, or steady.
This report allows you to see at a glance how the amount of created charges was trending for one or more selected years. (Note that if you select multiple years, the “YTD vs EOY vs LY YTD graph will reflect the total concatenated amount for all selected years.)
Annual Revenue (Date Range Annual Charges)
This report contains:
Slicers to set a start/end date for the report. Alternatively, you can use the date range selector below these slicers to set these dates.
- A table showing the total amount of charges created for each year in the selected date range. The table also includes grand totals for all Sites/Locations.
- A table displaying an indicator to designate whether the total charges are trending upward, downward, or continuing on a level basis when compared to the previous year.
- A table reflecting the total amount of tax line items for each year.
- A graph reflecting the total amount of charges created for each year in the selected date range.
- Hovering over the area of the chart corresponding with a specific year will display an information tooltip with the exact amount of charges created for that year.
This report allows you to see at a glance what your financial data looks like for a specific Site/Location for a selected year within the defined date range. It also allows you to see how these numbers are trending when compared to the previous year.