Miscellaneous Tips and Tricks for sqlReports

You can use PowerSchool system variables in your SQL Query in sqlReports to make your reports more flexible.  System variables are substituted with actual values when the report is run, with the actual values based on such factors as the school or term the user is in at the time.  Below are some common variables that you can use, along with examples:

~(curschoolid) - current school id, based on the school the admin is in, example: schoolid = ~(curschoolid)
~(curtermid) - current term id, based on the term the admin is in, example: termid = ~(curtermid)
~(curyearid) - current year id, based on the term the admin is in, examples: yearid = ~(curyearid) or termid like ‘~(curyearid)%%’

Add the Current Date to the Report Title

If you want today's date to appear in the report title, you can add ~(f.currentdate) to your report title.  It'll be blank when you view the sqlReports main page, but when you run the report, you'll see the current date as part of the report title on the output.  Just make sure the entire code is in the report title and doesn't get truncated.  The report title has a limit to the number of characters it can have, and if you have the code at the end of the title and something like only ~(f.curre gets saved with the title, the report will error out when it runs because it's expecting more coding.  So be sure to view the report title box in the Edit area and make sure the entire code appears.

Current School vs District Search

You can build a report that will only run for the current school or for the district office, depending on which school the user is in.  Use the following in your where:

~[if.is.a.school]and s.schoolid=~(curschoolid)[/if]

Such as in

where s.enroll_status=0 ~[if.is.a.school]and s.schoolid=~(curschoolid)[/if]

PS will check the school the user is in and if it's a school and not the district office, the part between the if and /if will be executed.  If the person is in district office, then the part is ignored.  It's important to include the "and" in the if part, otherwise when the if is false, the and will cause the sql to error out because nothing follows it.  For example, "where s.enroll_status=0 and" would be used and would error out if the and is not included in the if and the criteria was false.

Working Around Double Hyphens

In the PGFinalGrades and StoredGrades table you may find cases where the value of the grade field has double hyphens in it.  If you try to search for them in sql such as using the following - where grade = '--' - the report won't work.  The following is a workaround - where grade = regexp_replace('..','\.',chr(45)).

Resize the Text Area Boxes

An example of a text area box is the SQL Query input box.  Basically it’s any input area with more than one line of entry possible.  You can resize them by dragging the left hand corner down the page.  So if you have a really long query, you can resize the box to see more of it.

Copying a Report

The easiest way to copy a report is to export the report first and then import the information back in as a new report.  You can export the report and save the information as a text file, which gives you a copy of the original and you can then copy and paste it into the import box.  However, if all you want is information to import back in, rather than doing a right click on the ‘export this report’ and doing a save as, right click on the link and choose to open the link in new tab.  That will open a new browser tab with the information needed for an import, and you can highlight it and copy and paste the information into the import box.


  Back to the List of Articles