SqlReports

From California PowerSchool User Group
Jump to: navigation, search

sqlReports is a free (Classic version) or paid (Premium version) customization that allows you to create sql-based reports and/or charts in the admin portal. As a reporting tool you can create reports that do not exist in PS and make them interactive by asking admins for input. As an export tool you can build simple or complex reports and users can save the information in comma or tab format. No need to use DDE, quick export, or report templates - admins can run reports you create and export the results on the screen or filter the results before exporting. You can also build student based reports that have a 'Make Current Selection' button to easily use the results of the report as the current selection.

sqlExports

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 first 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 just 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.

SQL Topics

  • Avuhsd sql.zip - Resources from the SQL presentation at the January 20, 2012 Meeting at Antelope Valley UHSD in Palmdale.
  • Using Oracle SQL Developer.pdf - Using Oracle SQL Developer - presentation by Matt Freund
  • Basic SQL.pdf - Basic SQL Presentation by Dean Dahlvang at PSUG Las Vegas

sqlReports Examples

These can be "imported" by unzipping the download, opening the text file and pasting the contents into the "import" feature in sqlReports. These reports may need modification to work on your server and are shared "as is".

  • AP Class Sizes - Written to report on class sizes for 2nd semester. It helps the AP test coordinator with ordering the tests. The "WHERE" clause should be easily modifiable to suit your environment. Assumes:
    • That all AP course names start with "AP...."
    • That the semester 2 termid ends in "02"
  • Category Points - Lists all the PTP categories and how many points in each one. User can optionally specify a date range.
  • Class Rank Audit - Lists all schools in district and their Class Rank recalculation frequency setting and the latest date for which Class Rank was recalculated.
  • Course Roster - Provides a roster of students, course number and term, based on user supplied Course Number. It should work "as is".
  • Daily Presence and Absence Detail - Meeting-based. For a user-supplied date, reports all students with any period in their schedule meeting on that day and their attendance summary, including daily percent present.
  • Health Immunizations-All Active Students - All immunization records for all active students
  • Health Immunizations-by Grade Level - Immunization records for active students based on grade-level selected.
  • Health Immunizations-Measles Records for All Active Students - Shows Measles record for All Active Students
  • Health Immunizations-TDAP Records All Active Students by Grade-Level - TDAP records for all active students by grade level selected.
  • Health Immunizations-TDAP Missing - Returns a list of students for the current school whose Health screen does not have a TDAP date recorded.
  • Schedule Gaps and Double Bookings - Reports students who are double booked for a period or who have a non-scheduled period. You must choose a date when class is in session. (i.e. - picking a Saturday won't work). Designed for a 1 day 8 period schedule.
  • No SSO Accounts - Lists and makes current selection students who do not have an SSO account. This means parents have not signed up. The report is limited to students who have "AllowWebAccess" checked for a parent.
  • Parent Email Finder for SSO - Enter all or part of a guardian email address to find where that email address exists in Parent SSO accounts. It could be the main account address or additional notifications the guardian has setup. The report searches both areas.
  • D and F At Risk Report from Valley - Contains some elements unique to Valley, such as tagging students as "New" or "International (IN)"
  • Unverified Absences from Valley - Reports "A" absences for the current year through a user specified date for Periods 1-9.
  • Recent Quick Imports by Date and User - Lists the name of anyone using quick import to upload data.
  • District SARB Extract - Lists all students that have been extracted using ATN process for District.
  • School SARB Extract - Lists all students at current school that have been extracted using ATN process.
  • Check for Duplicate Course Requests - Lists students with duplicate course requests. Core code credit -- Richard James. Posted by Mantas.
  • Duplicate Course Requests - Reports students who have more than one request for a course number. Uses the current school and the working scheduling year. User can choose to ignore a duplicate request, where the 2nd request is an alternate and the first request is primary. Posted by Roger
  • Check for Incomplete Schedules - Lists students with incomplete schedules in PowerScheduler. Posted by Mantas.
  • Current Grades - D/F's with No Comments - Searches students who have a D or F with no teacher comment. Posted by Trish
  • Current Grades - N/U Citizenship with No Comments - Searches Students who have a citizenship of N or U with no teacher comment. Posted by Trish
  • Room Utilization from Valley - Matrix view with rooms on left side and a column view for each period with totals per period. Unused rooms show a blank.
    • WARNING! This report uses pivot, in SQL pivots need to have the columns "hard-coded". This report is code for 1(A), 2(A), 3(A), 4(A), 5(A), 6(A) and 7(A) as expressions. You will need to modify for your own use.
  • Attendance Reconciliation from PSUG SE 2016 - Putting all Together - Lists students and their daily attendance with time in/time out compared to meeting attendance for a user-selected date.
  • Student Email Missing - Lists students from the grade levels specified by the user who do not have an email entered in the stock student email field.
  • Student Request Tally - A near clone of the built-in "Student Request Tally" found in PowerScheduler, but with filters and the ability to make the filtered results the current student selection. For example, filter to students with 0 requests and make them the current selection.
  • All Course Requests with Email - Shared by Larry Steinke. Lists all course requests for the specified yearid along with the student email. Helpful to answer the question "How can I send an email to all the kids who are going to take ___ next year?"
  • Master Schedule with Grade Counts - Reports master schedule data for the current term(s), with class size, but also broken down by grade level. Useful for finding teachers who have just seniors, or juniors, etc. If you set a grade level to "No", it will NOT include any sections that have any students in that grade level.
    • For example, to find sections with ONLY seniors, set 9th, 10th and 11th to "No" and Seniors to "Yes".
  • Demerit Count - Reports active students in the current school and their demerit count based on the adoption of discipline_incidenttype field in log table as demerit field. You can modify query to suit your implementation of demerits.
  • Year to Year Enrollment Comparison - Lets the user select two years to compare. Lists all enrollments from year 1 and then if they are enrolled or not in year 2.
  • sqlReport to find missing or mismatched Global IDs - Related to SSO via OIDC through Azure or Google. Unzip this file then open the txt contents and copy/paste in sqlReports setup to import this report that can help you keep your Global IDs up-to-date.
Retrieved from "https://www.psugcal.org/index.php?title=SqlReports&oldid=1189"