Basic sqlReports Troubleshooting

If you run a report and don't get any results, here are some things to try to help troubleshoot:


1 - Use Display Debug Info

Edit the report and go to the Results Page tab.  Check the box next to Display Debug Info and Submit.  Next time you run the report you'll see a link to SQL Query toward the top of the output page.  Click on it to see the full SQL being using by the report.  Any parameters, either system or user, will be replaced by their actual values.  This will help you see if the report is using the parameters correctly, or if you have a sql client, you can copy and paste the sql into it and run it with the actual data in use and see if the client gives you any error messages.

As of sqlReports 5.3, you'll also see a link to check the system log for Oracle errors.  If you're not on sqlReports 5.3, use the steps below to check the system log.


2 - Search the System Log for Oracle Errors

Searching the system log is pretty easy to do, but not everyone has access to the log.  Here's the best way to search, plus you'll want to bookmark the page so that is saves the settings and you can search even quicker next time.  If you're on sqlReports 5.3.x or higher, you can turn on Display Debug Info - see above - and it will create a link to do the following instead.

Go to the page (follow the links via system, system logs, system log, view current system log file) and do the following:

a - leave System Log File set to Current
b - leave Lines Per Page on 100
c - in the Find Text box enter ORA-
d - change the Find Mode to Only show log files containing text

Then click on the Find button and bookmark the page, which will keep the settings from above, and you can click on the bookmark in a new tab to find any errors faster the next time.   The most recent errors will be listed last.  If a report has generated an error, the first line of the error will appear which will include the date, user, and the sql until it hits a hard return.  If your sql doesn't have any hard returns, it will show the entire sql plus an ORA- error message, which is a reference to Oracle.  You can then do a search on the ORA- reference on the web. 

Some of the system log errors are due to the sql while some will be related to PowerSchool constraints, such as reserved characters (colons, semi-colons, etc.) being used in the sql.  Try to do Step 3 below if the ORA- error seems to be related to sql, and follow Step 4 below if it's related to something non-sql.


3 - Use a SQL Client

If you run your sql in a client, such as Oracle SQL Developer, any Oracle errors will be returned in the output.  You can then try fixing the errors and run the sql again in the client right away.  Once there are no errors, try the sql in sqlReports.   A client is also handy in that you can run parts of the query rather than the whole query and check to see if one part is causing an error.   If you're hosted, you'll need to check with your hosting service to see if you can use a sql client.  Some hosts will allow it but require you to set up a virtual private network connection first.


4 - Check for PowerSchool Specific Things

Sometimes the problem isn't Oracle related, but rather the problem is the fact the sql is being run in PowerSchool.  When using sql within PowerSchool, you need to be mindful of special characters, strict date formatting, and html.  The "It Works in Deverloper but not sqlReports" and "Dates and Times in sqlReports" articles at this site cover these things in more detail.  But also check to see if system parameters, such as curschoolid, curyearid, or curtermid are in use in the sql.  The curschoolid command makes the sql school specific, meaning running it at district office will not return any results, and curtermid may not be returning data in a way you can use it.  Be sure to use the Display Debug Info approach to see how parameters are being used in the actual sql.


5 - Make Sure the Report Headers Matches the SQL Output

The wrong number of columns in the Report Header will cause the report to not have any buttons and no alternating row colors.   While editing a report, copy the information you have in the Report Header box, and then click on the Build Header link to see if you get the same number of columns.   You can also have just one line of data returned when there's an issue with the commas in the main select of your sql.  sqlReports looks at the number of commas to help create the columns and a concatenation with a comma will cause a problem.  For example, first_name||', '||last_name in the main select will cause a problem.  Instead, use first_name||chr(44)||' '||last_name.

 

Back to the List of Articles