The information below applies to both sqlReports Classic (the free version of sqlReports) and sqlReports Premium (a version available as part of a subscription). sqlReports Classic is based on the original sqlReports created by Dean Dahlvang that was just for use in the admin portal. sqlReports Premium includes additional features that Matt Freund has added, such as a teacher portal version, a charting feature known as sqlCharts, and some other additional items. The data is the screenshots below is fictional.
sqlReports allows you to create custom reports 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.
The SQL Behind the Report
Below is an example of the SQL area of a report setup:
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.
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.
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 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. Plus you can give users access to specific reports - no need for them to see links to see reports they'll never use.
- 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.
- Ease-of-Use - there's just one plugin to install and importing reports is easy to do. It's also easy to modify reports via the sqlReports edit area - no need to try to customize a page to make changes to a report.
- 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.
- Continuous Development - it's actively updated and checked on whenever new SIS releases come out.
Frequently Asked Questions
Is there a free version of sqlReports?
Yes - sqlReports Classic is similar to Dean Dahlvang's original version in that it's for the admin portal only. You don't need to buy anything, but you do need an account on this site to download it. Accounts are free and if you don't have one, you can apply for one by filling out the application form under the Login menu.
Can I use sqlReports even if I'm hosted?
Yes - sqlReports will work on hosted or self-hosted environments.
Does sqlReports work with newer versions of the SIS?
Yes - currently supported versions of sqlReports are tested with new versions of the SIS when they are released. Below is a compatibility chart with regards to sqlReports and SIS versions.
|SIS Version||Compatible sqlReports Versions|
|12.x, 19.x||sqlReports 6 - 12|
|20.4 - 20.11.0.x||sqlReports 11 - 12|
How do I know which version of sqlReports I'm running?
You can find the version of sqlReports you're running by looking at the bottom of the sqlReports pages.
I'm new to sqlReports - does installing the plugin add any reports?
No - the install only adds the customization itself. The reports themselves are stored in the Gen table and there's no way to install them with a plugin and you will either need to create a new report or use the import feature to import one.
After an upgrade, do I have to rebuild reports I made in earlier versions?
No - every version of sqlReports saves the reports the same way in the Gen table in the database. Any report you created in an earlier version will show up automatically in newer versions of sqlReports.
I get a Cross-Site Scripting Error with sqlReports - why?
Make sure you're running a compatible version of sqlReports with the version of SIS you're running (see the chart above). You can also get the error on any version of sqlReports if one is using improper HTML anywhere in a sqlReports report. Consult the user guide in sqlReports for more information regarding the improper HTML coding, or if you have an active account on the site, view the Cross-Site error part of the Basic Reports Troubleshooting article on this more information.
Which versions of sqlReports are supported?
- Any version of sqlReports 6 or higher is currently supported as long as it's compatible with the SIS you're running (for example, sqlReports 6 isn't supported on any SIS above 20.x).
- Starting January 1, 2022, sqlReports versions 6 - 10 will no longer be supported (click here for more information).
- Starting January 1, 2023, support will end for the free version of sqlReports (click here for more information).
I have a plugin with the name Power_Pack_EWS_sqlReports5 - is it from this site?