Tool Search: 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.
Ad hoc Filter Designer Editor
Read -See note below.
Write- See note below.
Add- See note below.
Delete - Remove Ad hoc filters.
Users need at least Read rights to the Filter Designer tool and at least Add rights for Pass-through SQL Query in order to properly use this tool. For more information about Tool Rights and how they function, see the Tool Rights article.
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 Relationship Manager
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 Editor
Create a Pass-Through Query
Pass-through queries allow users to search for data in a customized manner, join tables and/or views not used in the Query Wizard, and manipulate results using SQL functions. When creating a pass-through query, users should have the desired year, school, and calendar displayed in the Campus toolbar.
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 and in the Saved Reports list of the Letter Builder tool.
The following information is returned, depending on the type of query selected:
- Student: Grade, last name, first name, and student number.
- Census/Staff: Last name and first name.
- Course/Section: Course number and course name.
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
FROM and WHERE sections accept and save subqueries.
Semicolons (;) are not allowed.
- 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 needed data. The results will appear in the Test Query Results field on the right side of the screen.
- Select which group to Save To. Selecting the User Account radio button will allow you to save the filter to the current user or to a specific folder. See the Save Filters to Folders section for more information. Selecting the User Groups radio button allows you to save the filter 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 or Technical Services, 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.
