sqlExports is a powerful way for self-hosted customers to get data out of PowerSchool.   It works with sqlReports, so if you have a report built in sqlReports and want to automate exports of it, you can do so with sqlExports.  If you answer Yes to the following, then sqlExports may be the answer to making your job easier:

  • Are you self-hosted and would like to save data to local servers?
  • Do you use sqlReports and wish you could automate the saving of the results?
  • Are you saving data to local drives and wish the process was as easy as using sqlReports?

sqlExports is an easy way to save data to local drives.  All you have to do is create a sqlReport to get the data you want, and then use sqlExports to save the data to the location you specify.  For example, you can take a report that outputs this (the information below is fictional data based on a training database from the PS support site):

datatable

and save it to either a local drive on your SIS server or to a drive mapped to the server:

sqlexportoutput

It's simple to do.  When you run sqlExports, you'll see a list of reports that have been set to work with sqlExports:

selectreporttorun

You choose the report and if there are parameters, data for the last time the report will run will appear for them or you can change them.   The parameters are saved with the export, so if the export runs on a schedule, the same parameters will be used each time the export runs.  You can also create several exports of the same report and use different parameters each time. 

parameters

You can run the exports on a schedule or just run them in the background:

processingoptions

You can choose if you want to include column headers, if you want to create tab or comma delimited files,  or if you want to wrap the data in quotes:

reportoptions

Finally, you can specify where to save the data.  In the screenshot below, the C drive represents a drive on the SIS server itself.  Files can be saved to local drives on the server or any drive mapped to the SIS server:

folderpath

The file will end up in the location specified:

serverfolder

 

Advantages to Using sqlExports

  • Integration with sqlReports - create the report in sqlReports and make sure it's returning the data you want.  Then check the box to use with sqlExports and it's ready to use with sqlExports!
  • Use sqlReports Parameters - if your sqlReport has any user parameters, such as Term and Grade in the screenshots above, they will appear as options in sqlExports too.
  • No Need to Create PowerQueries - pulling data via SQL is a lot easier to do in sqlReports vs a PowerQuery, especially if you've never created a PowerQuery.  No need for PowerQueries with sqlExports!
  • Access to Data - with sqlExports you can export any data you can use in sqlReports, which includes several tables not available in AutoSend, PowerQueries, or Data Export Manager. 

 



sqlExports Subscription FAQ

What's included in the subscription?

The subscription includes the following:

  • access to the sqlExports download for a year - the download is updated any time a new version of sqlReports or sqlExports is released.
  • support for sqlExports for a year to help with any installation or running of sqlExports (support does not cover troubleshooting any sqlReports created by the user or answering general questions related to SQL)

I'm hosted by PowerSchool - can I buy sqlExports?

No - sqlExports is not sold to hosted customers because hosted customers will not be able to access the exports since they do not have access to the local hard drive of their SIS server.

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 - there's a version of sqlExports for the free version of sqlReports and another for the site subscription versions. 

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

Yes - you must be on PowerSchool 12 or higher.

Can sqlExports do SFTP or FTPS?

No - sqlExports is based on the reporting engine feature of PowerSchool, however, PowerSchool has never added SFTP to it like it did with AutoSend and Date Export Manger.  Plus, PowerSchool itself does not do FTPS, so sqlExports can not do FTPS either.  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.  If you would like to see SFTP as part of the reporting engine and sqlExports, or see FTPS added, visit the Enhancement Requests area of PowerSource and add a vote to Enhancement Requests 4243 and 9836.

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.

Is there a version of sqlExports that I can try out first?

Yes - there are actually a couple versions.  sqlExports Lite can be used with any version of sqlReports, but is not available for download.  If you wish to try it, please contact Matt for more information.  Site subscribers using sqlReports Premium, have access to a version called sqlExports Premium.   Both free versions only save to the report queue and not a local drive, but will give you an idea of how sqlExports works. 

How much does the sqlExports Subscription cost?

Please visit the Pricing Information page for that information.

 

 

 

 

 

© 2009 - present, All Rights Reserved - Matt Freund and Freund Resources