sqlReports Overview - Admin Portal

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.


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.


What It Does

sqlReports allows you to create new reports in PS that use SQL to get the data for the report, hence the name sqlReports.   You create reports that act basically like custom pages, but sqlReports keeps everything in one central location and makes a menu for you of the reports.  You can create reports for either the admin or teacher portal, plus control access to the reports based on security groups.  The menu system makes it easy to create new reports, edit reports, and is an easy way for your users to find reports. 

MainMenu

The original sqlReports was just for use in the admin portal, but Matt added a teacher version and a charting feature.  Reports can be either simple tabular reports with buttons to copy, export, or print the results, or reports can be single series or multi-series charts, with over 25 charts to choose from.


The SQL Behind the Report

Below is an example of the SQL area of a report setup.  It's actually from a free report from the Grades Reports area on this site called Current Grades - No Minimum report:

CurrGradesSQL

The report setup has an area where you add your SQL.  You follow regular SQL syntax, however, you can include parameters in the report, which can be either system variables like ~(curschoolid) or ones that you create, such as the param1 reference in the Where. 


Running the Report

Once you have the report created, users can click on the report and run it.  If you have any parameters, they will be prompted to enter them when they run the report.  In the example below, the user is prompted for three things - Term, whether or not to Use Current Selection, and a place to Enter Grades.  Every time a user runs the report, they enter the information, so that means you only need to create one report and the user can supply the parameters of the report.

CurrGradesInput

Once the user clicks on the Submit button, the report will run based on the SQL built for the report.  A page will come up displaying the results (the information below is fictional data based on a training database from the PS support site):

CurrGradesOutput


The user has the option to copy, export, or print the data, plus if the report involves students, the user can click a button to make them the current selection.  The results can be narrowed down by a search or by using column filtering.  Clicking on any of the buttons will use what's currently on the screen, so if one filtered the results to just Calculus and then clicked on the Print button, only those students showing on the screen would be included.


Advantages to Using sqlReports

  • No Need to Know How to Create Custom Pages - it uses a template system to create and edit reports and to display them.  There's no need to create custom pages and links to the pages - sqlReports and it's menu system does all of that for you.
  • No Need to Know How to Code Charts - the charting feature makes it easy to create charts.  Once you have the SQL in place, there's just a box to check to turn the output into a chart.  All the user has to do is pick the chart type and other parameters in order to create a chart.
  • It's Available for Admins or Teachers - you can create reports for yourself or for other users.  Reports can be restricted to specific security groups, so you can create reports just for principals to see, or ones that only the elementary teachers see.  The menu system makes it easy for them to find the reports.
  • Saves Time - create reports for yourself or others that replace everyday tasks.  Instead of going into DDE and doing an export over and over, just create a report that gets the data instead and click on a button to export it as a csv or text file.  Better yet, create reports for other users to run so they're not asking you to export the data all the time and they can do it themselves.
  • Continuous Development - it's actively updated and checked on whenever new PS releases come out.  Release notes and a version history can be found in the Documentation area of this site.
  • Popularity - the customization has been available since 2009 and used by hundreds of users.  The large user base makes it easy to get help and there's a good chance someone may already have a report you need, or provide you with the SQL necessary to create a report.

 

 Back to the Overviews Page

Terms of Use

By downloading any files or reports from this site, you agree to this site's Terms Of Use.