sqlExports Overview

sqlExports is a powerful way to get your data out of PowerSchool.  With it you can export almost any data you need out of PS and export from tables you see in DDE and tables you don't see in DDE, such as health, incidents, extended tables, and more.  You can query the database before doing an export and either do a one-time export or schedule it to run hourly, daily, or weekly.  There are also a wide range of output methods - the export can be to the report queue, it can be saved to a local or network drive on your PS server, it can be FTP'd to a server, or included in an email.

Here are some examples of how it could be used:

bullet blue alt   You need a daily export of current staff for a vendor.  You can export staff via AutoSend but it exports all staff and there's no way to query for just current staff.  sqlExports will let you find just the current staff and export their data.
bullet blue alt   You want to run an export and want to use system variables and parameters rather than create several different exports.  sqlExports uses your sqlReports reports and thus allows you to enter different information for each export.
bullet blue alt   You need a nightly export of data to be used by another server in your district to update some of it's data.  If that server is networked to your PS server you can save directly to it or FTP to it.
bullet blue alt   You want to send an email to certain staff members of any new adds or drops from the day before.  sqlExports can send emails with the list of students in the email body.
bullet blue alt   An admin wants to create an export of data but it's a lot of data and they'd rather be working in another part of PS while it exports.  With sqlExports the export will be sent to the report queue where they can use it once it's completed, or they can schedule the export to happen after work and have it waiting for them in their report queue in the morning.


How it Works

sqlExports takes the power of sqlReports and the power of the built-in reporting engine and merges them together.   sqlReports will allow you to pull data from any data table in PS and in ways that DDE, quick exports, export templates, and AutoSend will never be able to do.  The reporting engine allows you to export data one time or on a scheduled basis to the report queue, a local file, via FTP, or within emails.  The two together form the backbone of sqlExports - a powerful tool that lets you get the data you need.

Below is a short overview of sqlExports (video resides on YouTube):

 

sqlExports FAQ

How much does sqlExports cost?

There is a one-time initial purchase price of 10 cents per active student in the district, which includes the initial purchase of sqlExports and support and access to updates for the first year.  After the first year, product support and access to updates can be purchased for another year at 5 cents per active student in the district.  Support does not cover the creation of or troubleshooting of any reports created by the user for use with sqlExports. Price Guarantee - if PS ever releases a version that breaks the reporting engine and make sqlExports useless, you'll receive a prorated refund of your purchase or support for that year.

Is sqlExports included as part of a sqlReports Site Subscription?

No - it's a separate purchase.

Is a sqlReports Site Subscription required to use sqlExports?

No - it'll work with the free version of sqlReports or the site subscription version.

Do I need to be on a specific version of sqlReports to use it?

Yes - you must be on at least version 4.3 or higher of sqlReports because there are some files that are replaced and they are based on the 4.3 version.

Do I need to be on a specific version of PowerSchool to use it?

Yes - you must be on at least PowerSchool 9 or higher.

Can sqlExports do sFTP?

No - sqlExports can only do regular FTP.  PowerSchool added sFTP to AutoSend in 7.2, but did not add it to the part of the program that sqlExports is based on, the reporting engine.  If you host your own PowerSchool server, you could use sqlExports to save the export on the server or ftp it to another local server, and then use Window's task scheduler or a 3rd party program to run a sFTP client that takes the file and securely FTP's it.

I'm hosted by PowerSchool - which parts of sqlExports can I use?

Since hosted customers don't have access to the hard drive on the server, you won't be able to access any files saved using the Folder Path option.  Since that is the most powerful part of sqlExports, hosted customers are generally discouraged from purchasing sqlExports because the Lite version (see below) does basically what they'll be able to do with the full version, which is save things to the report queue instead.

What are the scheduling options?

You can schedule an export to happen ASAP or at a specific time. If you want to schedule it to run on a regular basis, you can choose hourly, daily, week days, weekly, bi-weekly, or monthly.

Will all of my sqlReports reports work with sqlExports?

99% of them should.  Reports with "square bracket" coding in the sql will not work with sqlExports. That includes ~[if] commands and the ~[temp.table.current.selection:students] current selection command. The if commands though you should be able to replace with CASE commands.  There's no work around for the current selection command.

How do I order sqlExports?

Contact Matt with the number of active students in the district and he will send you a quote to use for ordering purposes.

Where do I find information on renewing the sqlExports support?

Visit the Renewal FAQ under the Subscriptions menu.


sqlExports Lite is a free version of sqlExports that allows you to see sqlExports in action.  It has the following limitations compared to the full version:

Feature sqlExports Lite sqlExports
Output Location Only saves the file to the report queue Save the export to the report queue or on a local or network drive on your server, ftp it, or receive results in an email.
File Type Only creates tab delimited files. Create tab or comma delimited files.
Report Parameters Does not allow the entry of parameters. Once you choose a report you can fill in any parameters that go with the report.
Report Run Access Ignores any Run Access or Exclude From List report settings. Only lists reports tied to an admin's group/role and does not list those marked as Exclude From List.
Report Setup You must have a report group called sqlExports and the reports used with sqlExports Lite in it. The full version allows you to mark which reports you’d like to use with sqlExports, regardless if they're in a group or not.
Support A readme in the download and the FAQ below. Support and upgrades are included for the first year.  Can be bought for following years.

 

Even though it lacks the full funtionality of sqlExports, you could still use the lite version to save data from some of your sqlReports reports to the report queue.  Just make sure they're in the sqlExports report group (you can either create it or it'll be created if you import the current staff report that's part of the download) and it doesn't contain any parameters or square bracket coding (see the readme for more information - not being able to use square bracket coding occurs with the full version as well).  Your report can still contain system variables, such as ~(curschoolid) or ~(curyearid), but with sqlExports Lite it can't use parameters.

Files:
sqlExports Lite v. 1.1 (5/13/2016) Size: File Size 70.87 KB Downloads: Download 833 Download

sqlExports Lite FAQ

Do I need to be on a specific version of sqlReports to use it?

Yes - you must be on sqlReports 4 or higher.

Do I need to load this for every report I want to export?

No, you just load it once and choose the report you wish to use when you run it.  To use with another report, just run it again and choose the report.

Can I automate the exports?

Yes, when you run it choose to run it at a specific time and enter a time and date that's in the future.  Once it's in the report queue, click on the job name and on that page you'll be able to choose from a drop-down menu to run the report hourly, daily, weekly, etc.