How to Use the Current Selection in a sqlReports Report

When you create a current selection of students, PowerSchool creates a temporary table of the DCID's of those students.  You can incorporate the current selection in your sqlReports report by joining the temp table created by current selection to the students table.  You join the tables by the thing they have in common - the student dcid values.  Below are a couple examples of creating the join.  The following will only work by using a from and joins and table aliases with all your fields - if you have all your tables listed in the from clause this will not work and you will need to redo your sql to have just one table there and join the others.

SELECT s.lastfirst, s.grade_level
FROM students s
INNER JOIN ~[temp.table.current.selection:students] stusel ON stusel.dcid = s.dcid

SELECT s.lastfirst, cc.course_number
FROM CC cc
INNER JOIN students s on s.id = cc.studentid
INNER JOIN ~[temp.table.current.selection:students] stusel ON stusel.dcid = s.dcid


Giving Users the Option to Use the Current Selection

By using the Yes or No drop-down menu and an if statement, you can make it possible for the user to run the report for all students or just those in the current selection.  For example, if you use the Yes or No drop-down with Parameter 1, you could add the following to your SQL:

FROM students s
~[if#cursel.%param1%=Yes]
INNER JOIN ~[temp.table.current.selection:students] stusel ON stusel.dcid = s.dcid
[/if#cursel]

FROM CC cc
INNER JOIN students s on s.id = cc.studentid
~[if#cursel.%param1%=Yes]
INNER JOIN ~[temp.table.current.selection:students] stusel ON stusel.dcid = s.dcid
[/if#cursel]

The if statement will check the value of Parameter 1, and if Yes, will do the inner join, otherwise, it’ll ignore the inner join and all students will be used. See the Current Grades - No Minimum report for a working example.

FYI - the #cursel that's part of the if statement is optional, and when used, is a way to "name" different if statements so they don't conflict with one another.  If statements are used throughout sqlReports, so it's a good idea to "name" any that you add to your sql.  What follows the # can be anything you want it to be as long as it's unique and one word with no spaces. 

 

  Back to the List of Articles