sqlReports Installation Guide - Free Version

 
The following only applies to the Free version of sqlReports.

Any software mentioned in this guide is trademarked by its owner.   sqlReports is a standalone customization and integration into other customization downloads without the written consent of Dean Dahlvang and Matt Freund is prohibited.

Guide last updated on 10/20/2017.

New Installation of sqlReports 5.x - Admin Portal

  • Plugin Installation
  • Copy Files Directly onto the Server


New Installation of sqlReports 5.x - Teacher Portal

  • Plugin Installation
  • Copy Files Directly onto the Server
  • Post Installation Setup


Upgrading sqlReports from an earlier version of sqlReports 4.x or 5.x

Installation Troubleshooting


New Installation of sqlReports 5.x - Admin Portal

sqlReports should only be installed on PS 10.x or higher.

Installation will depend on whether you install customizations using the Plugin Management Dashboard or copy files directly onto the server.  Both situations are covered below and one should have basic knowledge of adding customizations to PowerSchool before proceeding (you can find more information on both in the at Articles area of this site.

In the instructions below, {xxx} is a generic reference to the version number in the zip file.  The actual zip file will have an actual number.

Plugin Installation

If you’re using the plugin approach, you can install the file called plugin-sqlreports-{xxx}-Admin.zip. Installing the plugin will create a folder in CPM at /admin/sqlReports5 and install the files from the plugin in that location and create the tab for sqlReports 5 on the System Reports page.  You must have access to the plugin install page in PS to do the following:

  • Go to the PS plugin install page, which is also known as the Plugin Management Dashboard page – system, system settings, plugin management configuration – and click on the Install button.
  • On the first line of the page that comes up, click on the button to choose a file (on some browsers it may say Choose A File and some may have Browse on the button) and find the plugin-sqlReports-{xxx}-Admin.zip file from the download and install it.
  • After you install it you’ll need to enable it on the Plugin Management Dashboard page by placing a check mark in its box under Enable/Disable.
  • The files are now installed and you can use sqlReports 5 after clicking on the System Reports link on the left admin navigation menu.


Copy Files Directly onto the Server

If you’re not using plugins, create a folder in your custom web root for /admin/sqlReports5.  Then unzip the plugin-sqlReports-{xxx}-Admin.zip file and add the files from its /web_root/admin/sqlReports5 folder into your custom web root at /admin/sqlReports5 and copy the file from /web_root/admin/reports into your custom web root at /admin/reports.  The file in the /admin/reports folder should dynamically create a tab for sqlReports 5 on the System Reports page.


Post Installation Setup

After you’ve installed sqlReports for the admin portal, you access it by clicking on the System Reports link on the admin left navigation and clicking on the sqlReports 5 tab.  Before you start using sqlReports, you’ll need to click on the ‘Assign preferred admin security group’.  The preferred group will be able to create new reports, import reports, or edit existing reports.  From the drop-down choose the admin group you want to be the preferred group and then click submit.   If you need to change the group number for some reason, please contact Matt for information on how to do so.

Back to Top


New Installation of sqlReports - Teacher Portal

sqlReports for the teacher portal should only be installed on PS 10.x or higher and match the same version of sqlReports as installed for the admin portal.  Also, if you install the site subscriber version of sqlReports on the admin portal, you must use the site subscriber version on the teacher portal.

sqlReports for the teacher portal in an optional install that allows you to make your sqlReports reports available to teachers.   Teachers cannot create or edit reports, they can only run reports you assign to their group under the Run Access tab in sqlReports.  Teachers only see reports assigned to that group – they will not see reports set to blank under Run Access.

Before installing sqlReports for the teacher portal, you’ll want to assign teachers to groups in the admin portal.   Below is a recommended setup for the group setting if the teacher does not login to the admin portal.  Note that everything above the Default Group is left blank.

 Teacher Portal Security Settings

IMPORTANT: It’s best to do the above before installing sqlReports for the teacher portal as reports assigned to Group 1 may start showing up in the teacher portal and allow teachers assigned to that group by default to see information they shouldn’t see.

In the instructions below, {xxx} is a generic reference to the version number in the zip file.  The actual zip file will have an actual number.

Plugin Installation

If you’re using the plugin approach, you can install the file called plugin-sqlReports-{xxx}-Teachers.zip. Installing the plugin will create a folder in CPM at /teachers/sqlReports5 and install the files from the plugin in that location and create a link for sqlReports 5 on the teacher portal left navigation menu. You must have access to the plugin install page in PS to do the following:

  • Go to the PS plugin page, which is also known as the Plugin Management Dashboard page – system, system settings, plugin management configuration – and click on the Install button.
  • On the first line of the page that comes up, click on the button to choose a file (on some browsers it may say Choose A File and some may have Browse on the button) and find the plugin-sqlReports-{xxx}-Teachers.zip file from the download and install it.
  • After you install it you’ll need to enable it on the Plugin Management Dashboard page by placing a check mark in its box under Enable/Disable.
  • The files are now installed and teachers can use sqlReports by clicking on the sqlReports 5 link on the left admin navigation menu in the teacher portal.  The link only appears in the teacher portal menu – there is no link for sqlReports 5 in the PowerTeacher Pro menu.


Copy Files Directly onto the Server

If you’re not using plugins, create a folder in your custom web root for /teachers/sqlReports5. Then unzip the plugin-sqlReports-{xxx}-Teachers.zip file and add the files from its /web_root/teachers/sqlReports5 folder into your custom web root at /teachers/sqlReports5. You’ll need to manually add the a link to the /wildcards/teachers_nav_css.tx for sqlReports because you will not be able to use the file from the /web_root/wildcards folder in the download because it is a page fragment for a wildcard and they only work in CPM.  Below is the link to add and is also on the Sample Link file in the download for an example of the link.  You will need to turn off customizations and then turn it back on for your wildcard to update.

<li ~[if.~[gpv:tabname]=sqlReports5]class="selected"[/if]><a href="/teachers/sqlReports5/home.html?frn=~(userfrn)" id="navsqlReports" title="sqlReports">sqlReports 5</a></li>

Back to Top


Upgrading sqlReports from an earlier version of sqlReports 4.x or 5.x

This section is only for those districts that currently use sqlReports 4.x or 5.x and are upgrading to the latest sqlReports version. If you use sqlReports 3 or Custom SQL Reports (an older version that’s part of the old custom reports bundle), then this part does not apply and you need to follow the instructions above for a new install of sqlReports.

IMPORTANT:  As of PS 10, there’s an Upgrade button on the Plugin page.  DO NOT USE IT WHEN UPGRADING SQLREPORTS.  To upgrade sqlReports, delete the current plugin(s) and install the latest plugin(s).  This will not affect any reports because they are stored in the GEN table.  Deleting a sqlReports 4.x plugin will not delete the sqlReports 4 folders – you will need to manually delete them at some point in CPM if you’re moving to 5.3.0.

IMPORTANT:  Before deleting a plugin, be sure to backup any of the files that you may have changed.  For example, if you changed any of the drop-down files that come with sqlReports, be sure to make backup copies of them before deleting the plugin as the plugin will delete the files.

IMPORTANT:  If you had to adjust page permissions for the /sqlReports4/params.html page in order for the submit button to appear for some groups, you may want to visit the page permissions for it before upgrading and make note of the changes.  You will need to make the same changes for the /sqlReports5/params.html page since they're two different pages.

In the instructions below, {xxx} is a generic reference to the version number in the zip file.  The actual zip file will have an actual number.

Plugin Method – if you’re using a sqlReports 4.x plugin, delete it and then install the plugin-sqlReports-{xxx}-Admin.zip and/or plugin-sqlReports-{xxx}-Teachers.zip file from the latest download.   If you created any custom drop-down menus in sqlReports 4.x, copy them to the sqlReports5 folder(s).   If you see a duplicate sqlReports tab when you click on the System Reports link, check for a custom /admin/reports/reporttabs.html page and remove any links on it for sqlReports as the link is no longer needed because the plugin is using a page fragment to add a tab instead.  

Non-Plugin Method - follow the directions above under “Copy Files Directly onto the Server”.  If you created any custom drop-down menus in sqlReports 4.x, copy them to the sqlReports5 folder(s).  If you see a duplicate sqlReports tab when you click on the System Reports link, check for a custom /admin/reports/reporttabs.html page and remove the sqlReports link on it as it is no longer needed because there’s a page fragment with this version to add a tab instead.

Back to Top


Installation Troubleshooting

All of the Reports Suddenly Disappear from the Menu - Admin Portal – a common case is when one switches from the free version to the site subscriber version or vice versa.  Due to a change in coding between the two, one has to reset the “preferred admin security group” before the reports will appear.  Reports can also disappear when someone changes the name of a report and some characters in it cause issues in the sql that lists the reports on the menu.  In DDA, go to the Gen table and do a search on cat=sqlreports.  Look to see if all the reports are still there, and if so, look for a report that was recently added or edited and check the name or title for characters, such as double quotes, and delete either the characters or report.  If the issue is with a report group name, you’ll need to delete the report via DDA as it’s not possible to change the group name in DDA.  

All of the Reports Suddenly Disappear from the sqlReports - Teacher Portal – this can happen if there’s a mismatch in versions.  Make sure the version installed for the teacher portal matches the one used in the admin portal in both version number and type (free vs site subscriber version).

Tab Doesn’t Appear after Installing the Plugin – the install creates a tab based on the insertion point coding on the /admin/reports/reporttabs.html.  If you have a custom reporttabs.html page in place and it’s missing the coding - ~[cust.insertion_point:report.tabs] - then the tab won’t appear and you’ll need to update your custom reporttabs.html page.  See this blog post for more information.

Two sqlReports Tabs Appear – this can happen if you have a custom /admin/reports/reporttabs.html file with the tab coding on it, and then add ~[cust.insertion_point:report.tabs] to it, which then causes the tab from the page fragment to also appear.  To solve the problem, remove the sqlReports tab coding from the custom reporttabs.html page.

Teacher Portal Link Doesn’t Appear – if you’re not using CPM, you must add the link manually (see above).  If you are using CPM and do not see the link, you probably have a custom teachers_nav_css.txt file in the /wildcards folder and it’s missing the page fragment insertion code - ~[cust.insertion_point:leftnav.footer].  Either copy the coding for that command over to your custom wildcard or manually add the coding.

Manually Created Links Don’t Work – check for hard returns and remove any of them or copy the link from the Sample Link file in the download file instead.

Back to Top

 
Back to the main Documentation page.