Path: Reporting > Ad Hoc Reporting > Filter Designer
Use a Pass-through SQL Query to create custom filters that return data not available through standard Query Wizard options. Pass-through queries allow you to search additional tables and views, create custom joins, and use SQL functions to return targeted data sets. This guide explains how to create, test, and save a pass-through SQL query filter.
Create a new ad hoc filter and select the method and data source for retrieving report data.
Before you start
- Pass-through queries require a basic understanding of SQL and familiarity with the Campus data schema.
- Authorized Technical Support Contacts can request a copy of the Campus schema from their Infinite Campus Client Relationship Manager.
- When creating student- or course-based queries, select the appropriate year, school, and calendar in the Campus toolbar before creating the query.
- Unless All School is selected, student and course data is limited to the selected calendar. Census/Staff data is not dependent on calendar selection.
- Set appropriate tool rights. Refer to the Filter Designer Tool Rights article for more information.
Create a Pass-through SQL Query
- Select the Pass-through SQL Query Filter Type.
- Select a Data Type. The selected data type determines which fields are available when building the filter.
- Student
- Census/Staff
- Course/Section
- Click Create.
Create, test, and save a pass-through SQL query for use in ad hoc filtering and reporting. - Enter a Filter Name.
- Enter a Short Description and/or Long Description to provide additional information and context about the filter. Select the (+) icon to access the Long Description text box.
- In the first query field, add SQL statements that extend the predefined SELECT DISTINCT statement. Use this area to join tables or views as needed.
- In the second query field, enter additional query criteria for the WHERE clause. You can also include GROUP BY and HAVING clauses when used together.
- NOTE:
- Copying and pasting SQL from word-processing software is not recommended.
- Semicolons (;) are not allowed in pass-through queries.
- The FROM and WHERE sections accept and save subqueries
- Nested SELECT statements and ORDER BY clauses are not supported. Queries containing ORDER BY may test successfully but fail when used as saved filters.
- All quotation marks in SQL statements must be straight single quotes ('). Curly quotes copied from word-processing applications can cause query errors.
- Click Test Query to validate the query and review the results.
- Select where to save the filter:
- User Account to save to your account or a folder.
- User Groups to save one or more copies for user groups. Each group receives its own editable copy.
- Click Save.
- Click Return to List to navigate back to the Filter Designer home page.
Impact
The saved query appears in the Saved Filter list in Filter Designer and can be used in reporting tools such as Letter Builder/Letter Designer.
Select calendars for saved queries
For saved Student and Course/Section pass-through queries, you can choose which calendars to include when running the query.
- Open a saved Student or Course/Section pass-through query.
- Select one or more calendars by:
- Active year
- School name
- Year
- Run the query.
Calendars are only available when you have rights to those calendars. Refer to the User Group Calendar Rights article for more information.
Impact
The query returns results only from the selected calendars.
What's next
- Use the saved query in reporting and communication tools.
- Review pass-through queries for additional SQL examples:
