Classic View: Ad Hoc Reporting > Filter Designer > Pass-through SQL Query
Search Term: Filter Designer
Users can return custom sets of data by using the pass-through SQL query option, available as part of the Ad hoc Filter Designer tool. A pass-through query uses SQL language to gather the desired information. A basic understanding of SQL and a working knowledge of the Campus data schema is helpful. Pass-through queries allow users to search for data in a more customized way, to search on tables and views not used in the Query Wizard and to use SQL operators.
Unless All School is selected in the Campus Toolbar, only students and course information from the selected calendar will be displayed in the query. Census/Staff information is not dependent on the selection of a calendar.
Pass-Through SQL Query
Use of the Pass-Through Query tool beyond the examples provided requires SQL knowledge. A copy of the Campus schema may be obtained through a Campus Client Executive.
Calendar Selection
Select a saved Student Data Type and saved Course/Section pass-thorough query and select from which calendars to report data. Select calendars by the active year, by the school name or by year. If a calendar is selected in the Campus Toolbar, that calendar is already selected.
Calendars cannot be selected if the query is for Census/Staff Data Types.
Only calendars to which the user is assigned calendar rights are available for selection.
Pass-Through Query Calendar Selection
Create a Pass-Through Query
Pass-through queries allow users to search for data in a customized manner, to join to tables and/or views not used in the Query Wizard, and to use SQL functions to manipulate results. Users should have the desired year, school and calendar displayed in the Campus toolbar when creating a pass-through query.
Once a pass-through query is saved, it can be used to generate reports created with the Ad hoc Letter Designer tool. The saved query appears for selection on the Saved Filters list of the Filter Designer main page. It also appears in the Saved Reports list of the Letter Builder tool.
The following information is returned, depending on the type of query selected:
- Student will return grade, last name, first name and student number.
- Census/Staff will return last name and first name.
- Course/Section will return course number and course name.
Sample queries are provided on the following articles:
Creating a Pass-Through Query
Filter and Data Type
- Select the Create a New Filter using the Pass-Through Query option.
- Select the Filter Data Type.
- Click the Create button. The screen will display the beginnings of the SQL SELECT statement.
Pass-through Query Detail
- Enter a name for the filter in the Filter Name field.
- Enter a Short and/or Long Description about the filter (if applicable). For more information, see the View Filter Descriptions section of the Filter Designer page.
- In the text fields on the left-handed side of the screen, enter more querying definitions. The beginning part of the query is already written (Select DISTINCT...). Users can join two database tables and views in the first text field.
- In the second text field, enter more criteria for the SQL WHERE clause. For example, a desired query may list all students with the first name of Ashley. The text in this field would be 'and student.firstName = 'Ashley'. The second text field can also include GROUP BY and HAVING clauses, which must be used together.
- Click the Test Query button to verify that the data returned is the data needed. Results will appear in the Test Query Results field on the right-hand side of the screen.
Select which group to Save To. Selecting the User Account radio button will provide the option of saving the filter to the current user or saving the filter to a specific folder. See the Save Filters to Folders section for more information. Selecting the User Groups radio button allows the filter to be saved to a user group or multiple user groups.
If a filter is saved to more than one User Group, a separate copy is stored for each group. Each group can independently edit the filter without affecting another group's copy.
- Click the Save button when finished. The new filter will be listed in the Saved Filters list on the main page of the Filter Designer.
Pass-Through Query Details
Save Filters to Folders
Ad hoc filters can be saved to specific folders created in the Filter Designer tool. For more information about saving and organizing filters into folders, see the Filter Designer page.
Saving a Filter to a Folder
SELECT Statements and Pass-Through Queries
The SELECT phrase changes depending on the filter data type chosen. The SELECT statement is predetermined; therefore, only certain fields are returned by a pass-through query.
Nested SELECT statements and ORDER BY clauses are not supported by the pass-through query. Data may return when testing the query, but using the saved query that has an ORDER BY clause may return an error when used in search results.
Sample Pass-Through SQL Queries
These sample queries are for example purposes only. They are a starting point to give an idea of how this tool can be used. A request for Campus Support to design, modify, or troubleshoot a Pass-Through Query is a request for Custom Development, which is subject to a fee.
The following provides examples of commonly used pass-through queries, organized by the Filter Data Type selected for the query on the Filter Designer main page.
- "Box A" refers to the upper text box that continues the SQL statement.
- "Box B" refers to the lower text box that specifies conditions of returned results.
When generating Student based queries, data returns based on the school year, calendar/school name and schedule selected in the Campus toolbar.
Box A, Box B
In SQL, all quotes MUST be straight single quotes (') or the query will return errors. Pass-through queries will not work if curly quotes are used (quotes copied from word processing programs). Copying and pasting from word processing programs is NOT recommended.
