sqlReports Overview

Since 2009, PowerSchool users have been able to easily create interactive custom reports within PowerSchool by using SQL and a free customization called sqlReports.  Dean Dahlvang released a free PowerSchool customization called sqlReports in March of 2009, which he described as "an ad hoc reporting add-on that can turn simple to medium sql queries in to web based reports in PowerSchool."  Users could write reports to access data from anywhere in the database, and it became one of the most popular customizations in use today.  Dean would continue to improve on sqlReports and release newer versions, with sqlReports 3 being the last one he released.

Free, Popular, and Powerful

sqlReports is free and runs as a customization within PowerSchool, so there's no need to set up data transfers to 3rd party vendors, create additional accounts or logins, or buy additional servers just to analyze and view the data that's sitting right there on your PowerSchool server.  Once you use sqlReports, you'll see why it's one of the most popular and powerful customizations ever created for PowerSchool!

sqlReports 4 and 5

In 2013, Dean turned over development to Matt Freund, and with coding assistance from Bob McGregor, sqlReports 4 was born and first released on June 1, 2013.  Charts were added in sqlReports 4, along with a teacher portal version.  sqlReports 5 was released on December 9, 2016, and ushered in the use of HTML5 buttons to replace the Flash buttons that were standard with sqlReports 4.

Admin Portal Overview

With sqlReports you can access almost anything in your PS database and use sqlReports to build reports on the data, use it as an export tool, or use it as a way to create current selections.  As a reporting tool you can create reports that do not exist in PS and make them interactive by asking admins for input.  You can even create simple charts based on your reports.  As an export tool you can build simple or complex reports and users can save the information in comma or tab format.  No need to use DDE, quick export, or report templates - admins can run reports you create and export the results on the screen or filter the results before exporting.  You can also build student based reports that have a 'Make Current Selection' button to easily use the results of the report as the current selection.  The reports are tabular style and based on:

 bullet blue alt  SQL - structured query language which allows admins access to data from several tables at once and even from tables not seen in DDE
 bullet blue alt System Variables - PowerSchool specific coding, such as ~(curschoolid) or ~(curyearid), which makes the reports specific to the user
 bullet blue alt User Defined Parameters - when the admin runs the report they are asked to supply information, such as dates and other information

Those three things make sqlReports very powerful, very flexible, and very useful for yourself or other admins.  The results page is built using DataTables coding and contains buttons which provide admins with several options on what to do with the results.  Below is an example of a report - the data is from a fictional database from PowerSchool:


In the example above, an admin could run the report and then make the students the current selection, filter the data, copy or export it, or print it to a printer or pdf.  If an admin would rather have the information exported automatically rather than pressing the CSV or Tab button, the sqlExport product can do that for them.  It's also easy to create the output as either a single series or multi-series chart using the sqlCharts feature. 

Teacher Portal Overview

The teacher portal version allows you to make your sqlReports reports available to teachers in the PowerTeacher Portal.  Teachers cannot edit any reports, but they can run reports you give them access to in much the same manner as an admin runs sqlReports reports. 

Below are some of the benefits to using the teacher version:

bullet blue alt You harness the power of sqlReports - create the reports like you would for an admin but make them available for teachers.
bullet blue alt You can create reports not available in the gradebook or are too tedious to create as object reports.
bullet blue alt It allows you to create student rosters containing data that teachers can export.
bullet blue alt Create reports covering a wide range of data - class rosters, student test scores, gradebook data, etc.
bullet blue alt Reports can include charts and there's a version of sqlCharts for the teachers as well.

Following are some screenshots of the teacher portal version - they were created from fictional data in a sample database provided by PowerSchool.  When a teacher logs into the PowerTeacher Portal, there's a new link for sqlReports 4 in the left side navigation menu (one can change the link wording to something other than sqlReports 4 if needed):

navigation menu

Once a teacher clicks on the link, a page listing reports that have been made available to that teacher appear.  Reports will appear in report groups, and if the output of the report is a chart, the C icon appears to the right of the report title.  Once a teacher clicks on a report, the parameters page will appear:

report menu

Any parameters you've set up will appear and the teacher clicks submit to run the report:

parameters page

You can make the export buttons available to teachers, enable column filtering or searching, use row grouping, etc.:

report output