Birthday Week 2017 - Free Reports and Tips & Tricks

The 8 free reports and tips and tricks have come to an end.  Next week I'll have a beta copy of sqlReports 5.1 available for people to download and try out on test servers.

Below are some links to reports that will be free that day, plus a couple tips and tricks for the day.  Check back daily - this post will be updated through Thursday:

Thursday - March 16

Today is the last day of free reports and tips and tricks.  For the free reports, I thought I'd share a couple that are my version of the gradebook vs stored grades report, which is a popular custom page in the custom reports bundle from

Gradebook vs Stored Grades - allows admins to choose a term and see discrepancies.  The column filtering makes it a very powerful report.  The report is also a good example of how to link to students from reports (see Tip #1 below).

Current vs Historical Grades (PST) - this is a version for the teacher portal.  It allows teachers to see the discrepancies for themselves.

Tip #1 - if you have a student report, you can create links from the student name to the student's screens by using coding like this:

select '<a href=/admin/students/home.html?frn=001'||to_char(dcid)||' target=_blank>'||lastfirst||'</a>', home_phone

The key is to include the student dcid field in your sql as it must follow the 001 to form the student frn.  The Gradebook vs Stored Grades report from above has links in case you want to see it in action.

Tip #2 - you can include student photos in reports.  Here's an example of the coding:

select '<img src=/admin/stpthumb/'||id||'ph_thumb.jpeg>', lastfirst

Unlike with tip #1, this one looks for the id field information rather than dcid.  For a teacher report, change the /admin part to /teachers.

Wednesday - March 15

Probably the reports I get asked most about changing are the free attendance reports for the admin portal.  People ask how to change them to look for specific codes instead of all attendance codes.  Recently I added enhanced versions of both that allow one to type in a code or codes to search on, and today those reports are free:  Daily Attendance - Enhanced and Meeting Attendance - Enhanced.

Tip #1 - Before adding any system variables or params to your sqlReport, make sure you're getting results with real values.  For example, instead of schoolid = ~(curschoolid), use an actual id value, such as schoolid = 100.  Once you get results, export them, then add your variable or param, and then run the report again and compare the results.

Tip #2 - To make sure your variables and params are working correctly, use the Display Debug Info option on the Edit, Results Page tab.  The next time you run the report, a link that reads SQL Query will appear at the top of the report, which you can click on and see the query that the report is trying to run.  Any variables and params will show the values being used rather than the variables and params coding.

Tuesday - March 14

Current Classes by Period - Students - this is a new report that is a enhanced version of the popular free report called Current Classes.  The Current Classes report shows where students are enrolled as of the current time, but this report allows an admin to enter a period number for the day and see where the students are enrolled during the day.

Current Classes (PST) - this report is similar to the report above, but is for teachers to run in the teacher version of sqlReports.

Tip #1 - Use an if statement to make a report show active students for the current school only or all students when at the district office, for example:

where enroll_status=0 ~[] and schoolid=~(curschoolid) [/if]

The if command will only execute when the user is at a school, which means if the user is in district office, it's ignored.  So the and schoolid=~(curschoolid) will only execute when one is at a school.

Tip #2 - you can link to a sqlReport from another page, but you must replace the lrn=###### part with lrn=~(userfrn).  For example, /admin/sqlReports5/params.html?frn=0068129&lrn=005315 would be /admin/sqlReports5/params.html?frn=0068129&lrn=~(userfrn) instead.

Monday - March 13

Current Courses Enrollment with Grades - this report is handy for those who need a list of all the students who are enrolled in a certain course or courses and need the current grades for those enrollments.

Expanded Enrollment Counts - one of the most popular reports on the site is a free enrollment counts report for grades 9 - 12.  This report builds on it by breaking things down by section and listed the teacher for the section.

Tip #1 - If you enable column filtering for a report and have some columns that you don't want to have filtered, add class="NOCF" to the TH command in the column header.

Tip #2 - If you want to make a copy of a report to try some new things with it, instead of saving the report you can export the current version, but instead of to a file, open it in a new browser tab.  Then copy and paste what's in the new tab into the import box in sqlReports.  That will save you time because you don't have to save it to a file and then open up the file to copy and paste.