Infinite Campus Ad Hoc Reporting Basics

CC 5/17 1

Innite Campus

Ad Hoc Reporng Basics

CC 5/17 2

Ad Hoc Reporting Basics

Overview

The Ad Hoc Reporng module allows a user to create reports and run queries for various types of data in Campus. Ad Hoc

queries may be used to nd data relang to students, sta, all people and courses. These queries may be exported from

Campus or used to lter canned reports throughout Innite Campus. Addionally, a tool is available in the Ad Hoc module

to create form leers and complete a mail merge using elds from the Campus database.

Basic Ad Hoc Vocabulary

 Field – A chunk of informaon like a name, an address, a date, a student’s grade or status. We assemble a set of elds

that we want as output or that we want to nd or lter the results of our query.

 Columns – Our results are made up of columns dened by the elds we included in the query and selected for output.

 Table – The answer to the query is a table of data.

 Filter – Filter is used by Innite Campus to describe the set of specicaons we’re using to answer our queson. It is

also used to describe the criteria we set to specically answer our queson or dene the results we want. We lter

some or all of the elds we include to narrow down the data to just the results we want. For example; students that are

currently acve, dates aer August 10

, only the parent’s primary mailing address.

 Query – The queson we want answered or the specicaons for the data we want to extract from Innite Campus.

We also use query to refer to the set of specicaons and instrucons we’re building and saving in the Ad Hoc tool to

get our queson answered. Query is at mes used to describe a Filter in Campus.

Ad Hoc Vocabulary Example

The nurse stopped by the oce and said she needs a list of all 11th grade girls who had a quesonable scoliosis screening

or were referred aer their rst screening. First, start with the elds needed: rst name, last name, grade, gender, and sco-

liosis screening. Next, add the criteria to lter the data: 11th grade, female, scoliosis screening status. The results will be

organized as columns of data in a table to answer the original query.

CC 5/17 3

Saved Filters

 From the Index, expand Ad Hoc Reporng and select Filter Designer.

 Exisng Ad Hoc lters are listed in the Saved Filters window. The rst lters on the list are your personal Ad Hoc Fil-

ters.

 The boom of this window lists folders with the icon. The lters inside folders shared among all WCSD sta who

are members of that Campus group.

 Because these folders are shared among WCSD sta, changes you make to the lter will aect everyone. We request

that you make a copy of the lter and edit that copy instead of making changes to the shared lters.

Copying Filters

Copying working lters out of the shared folders can make a great starng point for a new lter.

To copy a lter, click the ⊞ next to the folder icon to expand the folder.

Select the lter in the list you wish to copy and click the Copy buon. Click OK when the message appears that the lter

has been copied. A copy of the lter will be added to your Saved Filter list with “Copy of” added to the lter name.

Organizing your Filters Into Folders

1.From the main Filter Designer screen, select Create a new Folder.

2.Enter a folder name and click Save. Parent folder can be le at (No Parent).

3.If you want to create a folder within a folder, you would create the “main” (parent folder) rst and the create a new fold-

er, choosing the exisng Parent Folder you wish to put it in. This allows you to create a nested hierarchy of folders for

even more organizaon.

CC 5/17 4

Deleting Filters

1.Click to select the lter you wish to delete.

2.Click the Delete buon. Conrm you wanted to delete the lter by clicking OK.

Exercise: Copying and Organizing Filters

Exercise 1: Copy an exisng lter from one of the shared folders available to you into your

personal lters.

1.Pick one of the shared folders available to you.

2.Click the ⊞ next to the folder icon to expand the folder.

3.Pick any of the Ad Hoc lters in that folder.

4.Press the Copy buon.

5.Note that the lter appears at the top of the list with “Copy of” added to the tle.

Exercise 2: Create a folder

1.Click the Create a new Folder buon.

2.Click into the Folder Name eld and type ‘Ad Hoc Class’. Leave the Parent Folder at (No Par-

ent).

3.Click Save.

Exercise 3: Move a lter into the folder you just created.

1.Click to select the lter you created in Exercise 1.

2.Click and drag the lter over the folder created in Exercise 2 and drop it onto that lter.

3.Check the conrmaon message to conrm the lter was copied into the correct folder and

press OK.

Exercise 4: Delete a lter

1.Click to select the lter you moved into a folder in Exercise 3.

2.Click the Delete buon. Conrm you wanted to delete the lter by clicking OK.

Is your list of lters starng to look like

mine? Pages of lters named ‘Copy of

Copy’? Consider adding some folders to

organize your lters.

CC 5/17 5

Create a Filter with the Query Wizard

Start by naming your lter and adding the elds that contain the informaon you need and will help you reduce the results

to just the ones you need.

1. Select the rst radio buon Query Wizard under Create New from the main Filter Designer screen.

2. Select a Filter Data Type - Student, Census/Sta or Course/Secon.

 Student Data queries return data that would be found on tabs in the Student Informaon > General secons

of Campus. Other data associated with students can also be included.

 Census/Sta queries return data found on tabs in the Census > People secon of Campus.

 Course/Secon queries return data from a school’s courses and secons.

3. Click the Create buon. Campus will load the Field Selecon Screen.

4. A Query Name is required. The Query Name should be task descripve. A Short Descripon can be entered, this will

appear as a toolp when browsing the Saved Filter list. A Long Descripon can also be entered with much more room

to describe the query.

5. The All Fields list displays secons of available data you may include in your results and use to lter the results. These

roughly correspond to secons or tabs within Innite Campus. Expanding the ⊞ next to a secon displays available

elds and possibly some addional secons of elds.

6. Clicking a eld name will put that eld into the list of Selected Fields for inclusion in your Ad Hoc lter results.

7. Field names can be searched by entering a eld name in the Filter By eld and clicking Search. Pressing Clear will re-

store the full list of eld names.

8. Remove unwanted elds from your Ad Hoc lterby clicking the <— buon between the All Fields and the Selected

Fields lists.

9. Save will save your lter to the folder set in the Save To: seng. By default this is the base level of your user folder,

you can choose a dierent folder to save the lter into. You can also select User Groups and save your lter into the

shared folders, sharing it with others. Note that choosing this opon will move the lter out of your personal folder.

Make a copy and save that to the User Groups if you wish to save a personal copy of the lter.

10. Save & Test will save your lter and display the results of your Ad Hoc lter.

CC 5/17 6

Note: The results appear in a pop-up window and may be blocked by your browser by default. If a second window with

your lter results fails to appear, check your pop up sengs and allow pop-ups from hps://campus.washoeschools.net/.

Pop-up sengs for popular browsers can be found at:

 Internet Explorer: Tools > Internet Opons > Privacy > Pop-up Blocker > Sengs

 Chrome: Sengs > Advanced Sengs > Privacy > Content Sengs > Pop-ups > Manage Excepons…

 Firefox: Opons > Content > Pop-ups > Excepons…

 Safari (macOS): Preferences > Security > Block pop-up windows

Filter Parameters

Choose the Filter Parameters link at the top of the Ad Hoc Query Wizard screen or the Next buon at the boom of the

screen to set lter parameters that reduce the results to just the students or statuses you’re looking for.

Set Operator and Value to only return rows of elds that match the sengs you’ve made.

Available operators will change depending on the type of data in that eld. Value should match what Campus displays else-

where. If you’re unsure about how Campus is using a value, leave the Operator blank and examine the results, then add

lter criteria for the values you’re interested in including or excluding from your results.

 = (Equals) - An exact match of the user specied value. Student.Grade = 12 will return all seniors.

 <> (Does Not Equal) - Will return all results that do not equal the user-specied value. Student.Gender <> M will return

all females or student’s whose gender eld is null.

 IN - Includes mulple values in a comma separated list Student.Grade IN 10,11,12 will return all 10

, 11

, and 12

grade students.

 NOT IN - Not including the list of values. Student.Grade NOT IN 11,12 will return 9

and 10

graders.

 LIKE - Searches for the text in the eld. courseSecon.courseName LIKE history will return courses that have history

in their name such as WORLD HISTORY 1, WORLD HISTORY 2, US HISTORY 1, and US HISTORY 2.

 NOT LIKE - Excludes results that have the text anywhere in the eld. courseSecon.courseName NOT LIKE history

would return all courses that do not have history in their name.

 CONTAINS— Equivalent to the LIKE operator in many cases, CONTAINS opmizes some text searching and matching.

 SOUNDS LIKE - Phonec search that searches for text that have similar sound paerns. Last name SOUNDS LIKE “Ball”

will return “Bell” and “Boll”.

 > and >= (Greater than, greater than or equal to) - Student.Age >= 16 will include students 16 and older.

 < and <= (Less than, less than or equal to)

 IS NULL - Searches for empty elds and is used oen in conjuncon with start and end dated statuses to conrm a stu-

CC 5/17 7

dent is currently part of a program. acveEnrollment.spedExitDate IS NULL along with the IS NOT NULL example below

indicate a student is part of a SPED program.

 IS NOT NULL - Returns all records that have data in the eld. acveEnrollment.disability1 IS NOT NULL will return stu-

dents with an acve disability set in the SPED elements and is used in conjuncon with the IS NULL example above.

 BETWEEN - Includes the values between (and including) the two values specied. Student.Grade BETWEEN 02 and 04

will includes students in grades 2, 3, and 4. The BETWEEN operator has addional opons for elds containing dates:

 DATE - Returns data based on the dates specied.

 TODAY - Filters data based on dates that occur from a specic date through today or vice versa.

 TOMORROW - Filters data based on dates that occur from a specic date through tomorrow or vice versa.

 YESTERDAY - Filters data based on dates that occur from a specic date through yesterday or vice versa.

 DAYS BEFORE - Filters data based on the number of days prior through the second date set.

 MONTHS BEFORE - Filters data based on the number of months prior through the second date set.

 DAYS AFTER - Filters data based on the rst date set through the number of days aer the rst date set.

 MONTHS AFTER - Filters data based on rst date set through the number of months aer the rst date set.

 =TRUE - Is available to include Campus elds set to True. Student.acveToday =TRUE is used in many student lters to

only return students with a current enrollment.

 =FALSE - Is available to include Campus elds set to False.

 IS TODAY - Includes dates set to today.

 IS YESTERDAY - Includes dates set to yesterday.

 IS TOMORROW - Includes dates set to tomorrow.

 IN THE MONTH - Includes dates in the month specied.

Exercise: Creating Student Reports and Using Operators

Exercise 5: Create a simple student report:

1. Choose your main calendar if you have mulple or all calendars available.

2. Use the Filter Designer to create a Filter Type Query Wizard with the Data Type Student.

3. Set the Query Name to ‘Ad Hoc Class’

4. Add the following elds from the Student > Demographics list. Use the Filter By Search if you have trouble nding these

elds.

 student.studentNumber

 student.lastName

 student.rstName

 student.gender

 student.birthdate

 student.grade

 student.acveToday

5. Click Save & Test. Another window should pop up displaying a list of all students at the school selected in step 1. Con-

rm that the number of students listed as Total Records makes sense.

CC 5/17 8

Exercise 6: Add an operator to retrieve a single grade level:

1. Open the Ad Hoc lter created in exercise 5 if you’re not currently eding it.

2. Switch to the Filter Parameters secon of the Ad Hoc Query Wizard.

3. Set student.acveToday = TRUE. Innite Campus keeps every student enrolled at a school in the database, even if

their enrollment has ended. These students who are no longer at the school will be included in your reports unless you

include student.acveToday = TRUE in your Ad Hoc lters.

4. Select the = operator next to student.grade.

5. Set the Value next to student.grade to ‘3’ if you’re at an elementary school, ‘7’ if you’re at a middle school, and ‘9’ if

you’re at a high school.

6. Click the Save & Test buon. Note that Ad Hoc returns 0 records. Why is that? We represent grades as double digit

numbers and character codes in Innite Campus.

7. Change the Value next to student.grade to ‘03’ if you’re at an elementary school, ‘07’ if you’re at a middle school, and

‘09’ if you’re at a high school. Click Save & Test.

8. Note that Ad Hoc lters and returns data based on the way it stores values, which may be dierent from the way we

commonly think of or reference those values. Check the way Innite Campus is storing data if your Ad Hoc lters aren’t

returning the expected results.

Logical Expressions

The majority of your Ad Hoc lters will not use Logical Expressions, leaving the box blank. However, occasionally you need

to include one value or another value and the exisng operators don’t give you enough exibility. Or you’ll nd a need to

assemble a complex set of condions across mulple operators. The Logical Expression box allows you to set Boolean oper-

ators on each of the elds that have an operator set and combine those Boolean operators in exible ways. Some guide-

lines for using Logical Expressions:

CC 5/17 9

 Use the ID to the le of the eld name to reference that eld.

 Every eld with an operator set must be referenced in the Logical Expression

 Use the Add buon to set more operators on a eld that has already had an operator set on it.

Example: We need an export of unexcused tardies today to place a Connect call. Unexcused tardies show up with an

aendanceDetail.status set to ’T’ AND aendanceDetail.excuse set to ‘U’ OR with aendanceDetail.excuse being NULL.

We’ll need to lter on the status and either excuse elds. The following lter illustrates how this is set up:

We set student.acveToday = 1 and aExactDailyDetail.date = today to only retrieve today’s tardy students. We also set

aendanceDetail.excuse set to ‘U’ and press the Add buon and set eld #10 to aendanceDetail.excuse with the operator

set to IS NULL. Without a Logical Expression this lter won’t return any results, a student’s aendanceDetail.excuse will

never be NULL and set to U at the same me. To x this, we set a logical expression based on the following logic:

Parenthesis around the eld IDs group the logical expressions so we can reduce each set down to evaluate one or the other.

This results in the logical expression (4 AND 5 AND 7 AND 9) OR (4 AND 5 AND 7 AND 10).

Output Formatting

The Output Formang secon of the Ad Hoc Query Wizard allows you to selecvely include columns, set the order that

they appear, set sorng, set headers, and other output formang.

(

aendanceDetail.status 4

)

(

aendanceDetail.status 4

)

AND AND

student.acveToday 5 student.acveToday 5

AND OR AND

aExactDailyDetail.date 7 aExactDailyDetail.date 7

AND AND

aendanceDetail.excuse 9 aendanceDetail.excuse 10

}

(4 AND 5 AND 7 AND 9) OR (4 AND 5 AND 7 AND 10)

CC 5/17 10

When set, the Output disnct records checkbox will remove duplicate records from the results when all elds in the re-

port are idencal. It will not remove duplicates if only some of the elds contain duplicate data. Consider if you can turn

o the non-duplicate elds if you wish to remove duplicate values or consider exporng data to Excel and using the Re-

move Duplicate tool in Excel to selecvely remove duplicates while preserving the informaon you’re extracng.

The Output checkbox allows you to turn o selected columns while sll including their lter parameters you’ve set. For

example, you may wish to turn o the student.acveToday eld to keep it from cluering your reports while sll only

including the currently acve students.

The Seq eld allows you to change the order of elds in the output. You may have added and removed elds while ne-

tuning your report and wish to re-order the working report to provide more clarity. Set 1 for the rst eld output, 2 for

the second, etc.

The Sort eld allows you to dene the elds you wish to sort from the columns included in the lter. Mulple elds can be

set for sorng by using sort sequence numbers. For example, if the output was to be Last Name, First Name set the stu-

dent.lastname sort eld to ‘1’ and the student.rstname sort eld to ‘2’. Direcon allows you to reverse the sorng or-

der.

The Column Header elds allow you to set change the computer name elds to something that makes more sense. For

example, to label the student.lastname column to Last Name and the student.rstname column to First Name, set Last

Name and First Name respecvely in the Column Header elds.

Alignment allows you to set Le, Right, and Center text alignment to the output.

The Formang dropdown allows you to select various output formang rules based on the type of data in that eld.

Dates and mes have a number of Formang opons.

The Length eld allows you to limit the length in characters of the output.

Grouping and Aggregation

The Grouping and Aggregaon page of the Ad Hoc Query Wizard allows you to group the output of your Ad Hoc lter on a

specic eld and perform basic aggregaon on elds. For example, I might want the output of my simple student query

grouped by grade, with a count of students in each grade. I’d set Tier 1 Grouping to student.grade, the rst Aggregate/

Sub Total by to student.grade, and the Aggregate Type to Record Count.

CC 5/17 11

Selection Lists

Selecon lists allow you to create a list of specic students and save that list for later use in searches and reports.

From the main Filter Designer screen, select the second radio buon opon, Selecon Editor. Select Student as the data

type and click the Create buon.

Give the Selecon a name and choose your “Quick Search” opons if needed. Your students will be listed on the le side.

Select the students from the All Students list on the le by highlighng the name and clicking the right-poinng arrow key.

To remove a student from the selected students list, click the le-poinng arrow key.

Click the Save buon when nished. The new lter will be listed in the Saved Filters list on the main page of the Filter De-

signer feature.

Using Ad Hoc Filters in Searches and Reports

Saved Selecon Lists and Ad Hoc Filters can be used as part of a search on the search tab. Student Ad Hoc lters or lists

may be used in the student search, Census lters or lists may be ulized when searching for a person, and course/secon

lters or lists may be used when searching for a course or secon.

1. Select the Search tab.

CC 5/17 12

2. Choose the type of data to search for (Student, Sta, Course/Secon).

3. Select Advanced Search from the Search.

4. Select your lter from the list of Saved Filters on the right and click Search.

5. Display results will appear on the le side of the Campus session.

Note that for Ad Hoc lters to work with searches all elds but the student number or person ID must be turned o with the

Output checkbox on the Output Formang secon of the Ad Hoc Query Wizard.

Ad Hoc lters and Selecon Editor lists can also be used in the canned reports Innite Campus provides.

As an example, mailing labels may be run for an Ad Hoc lter using

Census > Reports > Mailing Labels.

1. From the Index, expand the Census folder by clicking the plus (+)

sign next to the folder or by clicking on the name of the folder.

2. From the Index, expand the Reports folder by clicking the plus (+)

sign next to the folder or by clicking on the name of the folder.

3. Select the Mailing Labels opon from within that folder.

4. Select your Selecon Editor list or Ad Hoc lter.

5. Click Generate Report to see a preview of your labels.

Data Export

Data from your Ad Hoc lters can be exported to Excel or other for-

mats for further analysis or prinng. Do the following to export the

results of an Ad Hoc lter to Excel:

1. From the Index, expand the Ad Hoc Reporng folder. Select the

Data Export opon.

2. Select the Filter from the list of saved lters in the le-hand win-

dow.

3. Select Delimited values (CSV) as the Export Format in which to

display the lter. This is the most commonly used format for Ex-

cel. Other formats include

 HTML list report – opens report in new Web window

 XML - for use with systems that support XML.

CC 5/17 13

 Delimited Values (CSV) - Excel spreadsheet

 Fixed width

 PDF Report

4. Choosing Delimited Values (CSV) opens up some addional opons including the Delimiter, including the column head-

er in the output, quong sengs, and the opon to include subtotals. The defaults for the opons are almost always

good.

5. Click the Export buon. You will be prompted to save, download, or open a le tled ‘extract.csv’.

Common Fields and Where to Find Them

Where to nd some common elds when wring an Ad Hoc lter:

DEMOGRAPHICS –

studentNumber

lastName

rstName

birthdate

grade

acveToday—Used with “= TRUE” operator in most queries to be sure the query only returns students who are currently

enrolled.

CUSTOM PERSON

eld trip

internet

media

StudentEmail

OnlineEdSvc

FEDERAL RACE

Race.Ethnicity—Leer indicang race.

Race.EthnicityFed—Number indicang race. Addional TRUE / FALSE elds for each race in this secon of Ad Hoc.

SCHOOL

name

LEARNER

ACTIVE ENROLLMENT

CC 5/17 14

STATE REPORTING ELEMENTS

ResidentSchool—Zoned school number.

SPECIAL ED ELEMENTS

disability1

spedExitDate—usually used with “IS NULL” in most queries to indicate the student is currently a Special Ed student.

SCHEDULE

enddate (roster) - usually used with “IS NULL” in queries to only return informaon on acve classes and not on classes

the student has dropped.

COURSE/SECTION

courseName

teacherDisplay

SECTION SCHEDULE

termStart

periodStart

CENSUS

MAILING ADDRESSES

relatedBy

addressLine1

addressLine2

city

state

zip

Secondary—used to dierenate between a student’s primary mailing address and a secondary address – a ’1’ in the eld

indicates the address is designated as a secondary address

phone

ATTENDANCE*

*Aendance is slow and dicult to query in Ad Hoc. The Tardy/Absence Totals report in the Index menu allows you to

answer the most common aendance queries like Perfect Aendance.

ASSESSMENT

ALL TESTS—*Assessment score elds depend on the test taken. Review the assessment entry on a student’s tab to

determine which scores are entered for the assessment you’re interested in.

name

dateTaken

rawScore

scaleScore

percent

result

CUSTOM TAB: CREDIT GPA

SchoolYear—SchoolYear should be set to the current school year to return current GPA and credit informaon.

CUSTOM TAB: MTSS

MTSS was custom developed for WCSD and the elds aren’t available in Ad Hoc. Look for the RTI-MTSS report in the Index

menu for an overview of MTSS data that can be exported and further ltered in Excel.