Ad Hoc Create Table of Contents

(1)

Ad Hoc Create

The query wizard is used to create criteria based filters that can be used in a search, a report or can be exported. Filters created in the query wizard will pull results that match any fields and specific criteria entered for those fields only. The next few pages explain how to build a filter using the Query Wizard.

Query Wizard Screen 1:

1. Ad Hoc Reporting > Filter Designer. 2. Filter Type: Query Wizard

3. Data Type: Usually you will select student but here is a description of all three:

a. Student: All details of the filter are based on the student. Each record (row) contains information about the student such as their grade, class, teacher, behavior, etc.

b. Census/Staff: Details of the filter are based on person information but not student related. For example, you might want a list of the staff at

your school, what their district assignments are and so forth. c. Course/Section: Details of this type of

filter are on courses or sections but not student related. For example, maybe you want to know how many course/sections you have set up for certain topics and who teaches them. 4. Click Create.

(8)

Computer Education Support: 4/12/2012 Ad Hoc Create - Page 7 5. Query Name = Initials / Name / Date (For example: alm ECE ACTIVE STUDENTS 2/29/12)

6. Short Description – displays when you are hovering over filters to try and figure out which one you will select. 7. Long Description – Helpful for including additional details on a filter such as its purpose or the definition of fields

in the filter.

8. Filter By: Use this to locate fields.

9. Select fields from left (All Fields) window and they will appear in the right (Selected Fields). 10. Click Next.

Query Wizard Screen 2:

11. Select any appropriate Operators.

12. If an Operator is selected, usually a Value must be entered. If a Value is not necessary, then the field will not display an area to enter data. (See roster.endDate below.)

The following criteria in the MS/HS filter will pull the students 1st_{period teachers for courses they are currently}

in (haven’t dropped) that start at the beginning of the year. Note: This is just a screen-print of the last four fields in the filter. The first 7 are not shown in the picture below. If roster.endDate is not set to “IS NULL”, then the results would show the students teachers for courses that they have dropped.

(9)

Query Wizard Screen 3:

This screen is called “Format the output file/report”. We will discuss the following options:

13. Output distinct records: Check this to eliminate duplicate records on students. Note: For this to work, every field in the Ad Hoc report must be exactly the same. For example, if you have First Name, Last Name, Grade and Behavior Event and the student has had the same behavior event multiple times, checking this box will only display the student’s information once.

14. Output – Uncheck this if you don’t want the field to be exported. In the example below, we have narrowed the filter to show all students current 1st_{period classes at the beginning of term 1, so while we need the last three}

fields to set the criteria for the filter, we don’t need them to display when we export the data.

15. Seq – This is the order you want your fields to appear from left to right.

16. Sort – This is how the fields should be organized. Note – Even if you have grade as the first column to display (In your sequence), ad hoc won’t sort by grade unless you identify it in the Sort column as well. You can set your Sequence and Sort fields differently. For example, you might want last name to be the first column to appear (Seq) but you might want you list sorted by Grade.

17. Direction – If you choose a sort, select Ascend or Descend.

18. Column Header – Enter a new name for the field. (This will replace the long names that appear on the far left.) 19. Alignment – Left, Center or Right.

20. Save To: User Account – If you have a folder created, you can select it from the drop-down. If you don’t, leave it as is (will display a blank line with a /).

(10)

Data Export

Data Export is where you can send your Ad Hoc data to Excel or a PDF report for viewing/printing. Although there are 5 options on the screen to select when exporting your data, we will discuss the two most applicable functions which are Delimited Values (CSV) and PDF report.

1. Path: Ad Hoc reporting > Data Export. 2. Select Filter from Saved Filter window. 3. Pick an Export Format:

a. Delimited values (CSV) – Select this if you want to send it to excel. b. PDF report – Select this if you want a static copy that is ready to print.

4. Click Export.

5. A message will display asking if you want to Open, Save or Cancel the request.

a. If you selected CSV, always choose Open. You won’t have the opportunity to change it from a CSV to an Excel file until you get it open in Excel. At that time, you will be able to save it as an excel file. If you were to select Save on this initial message, it will not allow you to save it as an excel file since it has to be opened in Excel first.

b. If you selected PDF report, you can choose to open the file for preview/printing purposes or save it to your computer to view later.

(11)

Letter Designer

The Letter Designer is used to create custom letters in Infinite Campus. It works a lot like the Mail Merge function in Microsoft Word.

Step 1: Using the Letter Designer to create letters:

Path: Ad Hoc Reporting > Letter Designer.

1.

Under Create a New Letter Format, select one of the following:

a. Blank Form Letter: Select this option if you are planning to print the letter on a school letterhead.

b. Addressed Form Letter: Select this option for a pre-addressed letter that is suitable for placing in a window envelope.

2.

Click New Format.

3.

Name: Give the letter a name. It is a good idea to enter the date you

created/modified your letter at the end of the letter name so you will remember later.

4.

Short Description (optional) – Anything entered here will display when you

hover over your report prior to selecting it.

5.

Long Description (optional) – If you have more of a description to add

beyond what you have placed in your Short Description, you can enter it here. However, it won’t display until you open the letter.

6.

Type the contents of your letter and then insert the fields when you want to directly reference the student or parent information.

7.

Each time you insert a field, it will display with blue dashes around it. Be sure to add a space between it and the next word so that they are not “smooshed” together. (AKA, place a space between “sch.name” and “for” above.)

8.

Add/change the formatting of the letter as necessary using the toolbar on this screen. It is not as fancy as word

but does offer some options.

9.

Click Save Format.

(12)

Letter Builder

The Letter Builder is the final step to create a Merge in Infinite Campus. In the Letter Builder, you select the letter you want to send and who you want to send it to. The “who” part is based on one or more Ad Hoc filters. This can be a selection editor filter with specific students selected or a query which chooses to send letters based on specified criteria.

Step 2: Using the Letter Builder to complete the merge.

Path: Ad Hoc Reporting > Letter Builder.

1. Under the Saved Filter window: Select the filter which specifies who should receive the letter. (If you want to send the letter to all students actively enrolled in your school, expand the All Users group and choose the ALL STUDENTS – Active.)

2. Under Saved Letters, select the letter you want to send.

3. Leave the Set Operation at Union unless you are selecting two different filters and only want to records in common from the filters, then you would select Intersection. Please ask your JCPS Infinite Campus trainer if you have more questions on using the Intersection option.

4. Select your Sort Options. You can sort your letters by Alpha (default option), Grade/Alpha, Zip or Teacher. 5. Click Build Letters.

Note: If you find that your letter needs some changes, you will need to return to the Letter Designer to make those

changes and then return to the Report Builder to regenerate the letters. For this reason it is a good idea to create a selection editor filter with just a few students to test the letters and be sure they appear as you want them to.

(13)

Appendix

Ad Hoc Criteria Operators and Wildcard Characters

The next few pages explain how to use the Operators and Wildcard characters to set criteria in an Ad Hoc

Query.

TEXT/NUMERICAL OPERATORS

Operator Description Example

= Equals Student.grade = 12

Returns all seniors

< > Not equal to Student.grade < > 12

Returns all 9, 10, and 11th_graders.

> Greater than Student.age > 5

Returns all students over the age of five.

Student.lastname > Richards

Returns all students in the school whose last name begins with R through Z but comes after Richard alphabetically. > = Greater than or equal to Student.age >= 15

Returns all students in the school that are 15 or older.

< Less than Student.lastname < Matthews

Returns all students in the school whose last names begin with A through M but comes before Matthews.

Student.age < 16

Returns all students in grade 15 or younger.

< = Less than or equal to Student.grade <= 04

Returns all students in grade 00,01,02,03 and 04

IN Includes (Must put a comma between

each value.) courseSection.courseNumber IN 388286, 38828712, 38828812

Displays records for course numbers = PE (the above course numbers are PE courses.)

NOT IN Does NOT include (Must put a comma

between each value.) courseSection.courseNumber NOT IN 35229611, 35229711, 35229811

Displays all courses but Care Circle classes. (The above course numbers are Care Circle classes.)

BETWEEN In between Student.lastname BETWEEN A through G

Returns all students whose last names start with A through those that starts with G.

LIKE Used with wildcard characters (See

chart on page 13) See page 13

NOT LIKE Used with wildcard characters (See

chart on page 13) See page 13

SOUNDS LIKE Soundex (Names with a similar Soundex pattern.)

AKA – keep the consonants fill in blanks with vowels.

Student.lastname

SOUNDS LIKE “Mueller” will return Miller, Muller, Moeller, etc.

SOUNDS LIKE “Ball” will return Bell, Boll, Bill, Bull etc. CONTAINS Contains the numbers or letters listed courseSection.CourseName CONTAINS comp

Returns all records where the course name contains the letters comp. “Could be “Comp Applications”, or ADV Computer APPS, etc…”

(14)

Operator Description Example

STARTS WITH Starts with the numbers or letters

listed Student.firstName STARTS WITH chris Displays Chris, Christy, Christopher, etc. ENDS WITH Ends with the numbers or letters

listed Student.lastName ENDS WITH son Displays Richardson, Anderson, Johnson, Olson etc. IS NULL Is Empty (field contains no data) active.Enrollment.endDate ISNULL

Displays students who have NOT withdrawn from your school.

IS NOT NULL Is NOT Empty (field contains no data) Student.SSN IS NOT NULL

Will return all students who have a Social Security Number entered in campus

DATE OPERATORS

Operator Description Example IN THE MONTH

OF Returns all records with a date in the specified month Student.birthdate IN THE MONTH OF JULY Finds all students with July Birthdays.

IS TODAY Returns all records where the date is

equal to TODAY’s date. learningPlan.planEndDate IS TODAY Finds all students whose IEP End Date is today.

IS YESTERDAY Returns all records where the date is

equal to YESTERDAY’s date. learningPlan.planEndDate IS YESTERDAY Finds all students whose IEP End Date is yesterday.

IS TOMORROW Returns all records where the date is

equal to TOMORROW’s date. learningPlan.planEndDate IS YESTERDAY Finds all students whose IEP End Date is tomorrow.

USING THE “LIKE” FUNCTION AND WILDCARD CHARACTORS

Like: Searches for the text string in the field based on Wildcards

Not Like: Searches for all records that do NOT match the criteria entered. Replace LIKE with NOT LIKE in the

examples below and you will get opposite results.

Wildcard Description Purpose

_Example

% Percent Symbol (usually

above the number 5) Matches a string of zero or more characters Student.lastName LIKE Mc% Finds all names beginning with Mc

Student.lastName LIKE %en%

Finds all names that include the letters en (Bennett, Green, Mendell)

_ Underscore

(Usually next to the Zero key.) Use Shift key to select.

Matched one character Student.lastName LIKE Anders_n

Finds Anderson and Andersen

courseSection.CourseNumber LIKE 422100_4 Finds all High School SVY SOCSCI courses taught 1st_Trimester

(15)

Wildcard Description Purpose

Example

[ ] Square brackets

(Usually located 2 keys to the right of the P key)

Matches each single character enclosed in the brackets

student.lastName LIKE [ABCD]%

Finds every last name beginning with A,B, C, or D

student.lastName LIKE [ckl] ars[eo]n Will find “Carsen”, “Karsen”, “Carson”, “Larsen”, etc.

^ Caret

(Usually located above the number 6)

When used with square

brackets, it means NOT Student.lastname LIKE M[^c]% Finds all students with a last name beginning with M that does NOT have c as the second letter.

Note: In many cases, you can use ‘starts with’, ‘ends with’ or ‘contains’ instead of using Like along with wildcard

characters in your criteria. For example, Last Name contains ‘wil’ is the same as Last Name LIKE %wil% and will both pull names such as Wilson, Williams, and Willis etc?)

Ad Hoc Create Table of Contents

Ad Hoc Create

Table of Contents

Ad Hoc Filters in an Advanced Search

Sometimes Ad Hoc filters are used to view/update student records. For example, there is an Ad Hoc filter that

ECE Clerks and counselors run to locate students with Unlocked IEP’s. Once running this filter, the student

names display in the search results and then one by one can be fixed. The following directions explain how to

use Ad Hoc filters in the Advanced Search screen.

1. Click on Search > Advanced Search.

When the Campus Search screen displays look the right side of the screen and you will see a “Saved

Filter” window.

2. Select the filter and click Search.

3. Your Search results display on the search screen located to the left.

4. If you wish to add additional criteria, add it under Student Search and click Search.

Using Ad Hoc Filters in Reports

Sometimes you want to run a report but not for the whole school, or for an entire grade. Sometimes you want

to run the report just for certain students specifically or students that meet certain criteria. For example

perhaps you want to run the grades report for students in athletics. Or, maybe you want to run mailing labels

to only go to parent/guardian of Active ECE students.

1. Select the report you will need to run.

2. Select the Radio button next to Ad Hoc Filter.

3. Click on the drop-down list and select the Ad Hoc filter.

Creating Folders

Just as you have folders in your office or at your desk to organize your files, folders can be used in Infinite

Campus to organize your Ad Hoc filters. You can also place folders within folders. (The first folder would be the

“Parent Folder”. For example maybe you have a Behavior folder and within it have different behavior data

that you pull for different grades. Or, staff at schools such as Moore and Brown with multiple grade levels

might have a Middle School Ad hoc folder with applicable folders/filters and a High School Ad hoc folder with

applicable folders/filters within it.

Use the following directions to create folders:

1. Click Create a new folder.

2. Enter a Parent Folder if you want to place this folder in another one you have created. Otherwise leave

it set to “No Parent”.

3. Click Save.

Creating a Copy of a Filter

Before “re-inventing the wheel” and creating a filter from scratch, in many cases there is already a filter you or

someone else has created that has many/all fields that you would like in the filter. It is often simplest to just

make a copy of that filter and use the copy as your own. Then, you can add/remove fields from the filter, as

necessary.

1. Select the filter you would like to Copy.

2. Click Copy.

3. Click OK, when the message displays indicating that you have made a copy of the filter. The copy will

be placed in your user account. (Usually at the top of your Ad Hoc Filter Designer screen.)

4. Now you will be able to edit the filter.

(Select the filter and click Edit.)

Selection Editor

Query Wizard

Query Wizard Screen 1:

Query Wizard Screen 2:

Query Wizard Screen 3:

Data Export

Letter Designer

Step 1: Using the Letter Designer to create letters:

1.

2.

3.

4.

5.

6.

7.

8.

9.

Letter Builder

Step 2: Using the Letter Builder to complete the merge.

Appendix

Ad Hoc Criteria Operators and Wildcard Characters

The next few pages explain how to use the Operators and Wildcard characters to set criteria in an Ad Hoc

Query.

TEXT/NUMERICAL OPERATORS

DATE OPERATORS

USING THE “LIKE” FUNCTION AND WILDCARD CHARACTORS

Like: Searches for the text string in the field based on Wildcards

Not Like: Searches for all records that do NOT match the criteria entered. Replace LIKE with NOT LIKE in the

examples below and you will get opposite results.

Example

Example

_Example