Download Report

Ad Hoc Manual
2013-2014
For Infinite Campus documentation, go to the FCPS Education Technology
Infinite Campus Support site: https://my.fcps.net/ic-support
0 Table of Contents Filter Designer: Saved Filters ..............................................................................................................................2 Filter Designer: Testing Saved Filters..................................................................................................................3 Filter Designer: Copying Saved Filters ................................................................................................................3 Filter Designer: Creating Folders .........................................................................................................................3 Filter Designer: Query Wizard – Student, Census/Staff or Course/Section .........................................................4 Output Formatting .................................................................................................................................................8 Filter Designer: Selection Editor ........................................................................................................................10 Letter Designer ...................................................................................................................................................11 Letter Builder ......................................................................................................................................................12 Data Export .........................................................................................................................................................13 Sample Ad Hoc Reports .....................................................................................................................................14 Students with Program Flags ..........................................................................................................................14 Behavior by Homeroom Teacher/Team ..........................................................................................................14 Period Roster ..................................................................................................................................................15 No Pass No Drive............................................................................................................................................15 Staff Email Addresses .....................................................................................................................................15 Sample Reports ..................................................................................................................................................16 Student Information Reports ...........................................................................................................................16 1 Ad Hoc Reporting
Index>Ad Hoc Reporting
The Ad Hoc Reporting feature allows a user/user group to create reports and run queries for various
types of data in Infinite Campus. Ad Hoc queries may be used to find data relating to students, staff
and courses. These queries may be used throughout Infinite Campus to filter existing reports or
export data out of Infinite Campus. These filters can also be used to create form letters and mail
merges.
Disclaimer: Information gathered and exported from Infinite Campus is confidential and
should not be distributed to unauthorized personnel.
Ad Hoc
Report Tools
Filter
Designer
Letter
Designer
Letter Builder
Data Export
Description
Allows the selection of specific data fields for use in creating filters. These filters are used in
exporting data from Infinite Campus using Query Wizard or Selection Editor
Allows the creation of custom forms and letters.
Combines filters created in the Filter Designer with reports created in the Letter Designer.
Exports ad hoc filters in various formats.
Filter Designer: Saved Filters
Index>Ad Hoc Reporting>Filter Designer
Saved filters are the personal ad hocs you have created and can only be accessed by you. These
will be listed first under saved filters.
Filters saved in the groups below your personal ad hocs can be accessed by any individual who has
tool rights to a particular group. If you want to use an existing filter from one of the groups you should
always make a copy of the filter and save it to your personal user account.
You can also save to your school account located in the group filter section.
These filters have been created by you and are saved to your user account. You can save filters you create to these groups, so any person having access to a particular group will be able to use the filters.
2 Filter Designer: Testing Saved Filters
Index>Ad Hoc Reporting>Filter Designer
To test an existing filter, select the filter from the Saved
Filter window and click the Test button. A separate window
will appear, displaying filter results.
Filter Designer: Testing Saved Filters
Index>Ad Hoc Reporting>Filter Designer
Filters can be copied for additional editing. Select a saved
filter and click the Copy button. A pop-up message will
appear indicating the filter has been copied. Copied filters
are named Copy of [Original Filter Name].
Filter Designer: Creating Folders
Index>Ad Hoc Reporting>Filter Designer
Creating folders allows the user to organize their saved filters for continued use.
1. Click on Create a new Folder.
2. Name the folder. The first folder created will not have a Parent Folder.
3. Save.
3 Filter Designer: Query Wizard – Student, Census/Staff or
Course/Section
Index>Ad Hoc Reporting>Filter Designer
Query Wizard filters will always pull current information from the database based on the fields and
filter options selected. The Student and Census/Staff data types will allow the selection of
demographic and census filters, the Course/Section will not allow access to those filters.
1. Filter Type: Query
Wizard
2. Data Type:
Student,
Census/Staff or
Course/Section
3. Create
4. Name the Query
5. Users have the
option to enter a
Short and/or Long
Description if
applicable.
The long description will appear on the screen when the filter is selected on the saved filter screen.
6. Select the fields to
be included in the
filter from the All
Fields table. The
will open available
fields and additional
subcategories.
7. Users can search
for specific fields by
typing in a keyword in
the Filter By section
and clicking search.
8. Next.
4 9. Select the Operator.
Operator
= (Equals)
Results
Returns exact match of value
< > (Does not
equal)
Returns results not equal to the
value.
>= (Greater than
or equal to)
< (Less than)
Returns results that are greater than
or equal to the entered numeric
value.
Returns results that are less than
the entered value.
< (Less than or
equal to)
Returns results that are less than or
equal to the entered numeric value.
IN
Includes value.
Example
student.grade=3
Only students in grade 3 are returned.
student.gender < > M
Students who have a gender = F on the demographics tab or who do not
have a value entered in the gender field are returned.
student.age >= 16
All students 16 years of age and older are returned.
student.age < 16
All students under the age of 16 are returned.
student.age <= 16
All students 16 years of age and younger are returned.
student.grade IN 9,10
All students in 9th and 10 grade are returned.
When using this format, do not put spaces after the comma
NOT IN
Excludes value.
student.grade NOT IN 11,12
All students not in 11th or 12th grade are returned.
This operator allows NULL values.
When using this format, do not put spaces after the comma
BETWEEN
For BETWEEN: student.stateID BETWEEN 00001 THROUGH 100000.
Filters data between two specified
values. Works with numbers, dates
and strings.
All students with a State ID between 00001 - 100000 are returned.
If a date field is selected, the
following options are available:
For DATE: student.birthDate BETWEEN DATE 10151995 THROUGH
DATE 10152010.
DATE - Returns data based on the
specified date range (where the
starting date is sub-option 1 and the
ending date is sub-option 2).
• TODAY - Filters data based on
dates that occur from a specific date
through today or vice versa.
• TOMORROW - Filters data based
on dates that occur from a specific
date through tomorrow or vice versa.
All students with a birth date between 10/15/1995 - 10/15/2010 are
returned.
For TODAY: student.startDate BETWEEN TODAY THROUGH TODAY.
All students who began an enrollment in the school today (current date)
are returned.
For YESTERDAY: student.startDate BETWEEN YESTERDAY
THROUGH DATE 10152010.
5 • YESTERDAY - Filters data based
on dates that occur from a specific
date through yesterday or vice
versa.
• DAYS BEFORE - Filters data
based on the number of days (suboption 1) prior to sub-option 2
through sub-option 2.
• MONTHS BEFORE - Filters data
based on the number of months
(sub-option 1) prior to sub-option 2
through sub-option 2.
• DAYS AFTER - Filters data
based on sub-option 1 through the
number of days (sub-option 2) after
the sub-option 1 date.
• MONTHS AFTER - Filters data
based on sub-option 1 through the
number of months (sub-option 2)
after the sub-option 1 date.
All students who began an enrollment in the school yesterday through
10/15/2010 are returned.
For DAYS BEFORE: student.startDate BETWEEN DAYS BEFORE 4
THROUGH YESTERDAY.
All students who began an enrollment in the school 4 days before
yesterday through yesterday are returned.
For MONTHS BEFORE: student.startDate BETWEEN MONTHS
BEFORE 5 THROUGH TODAY.
All students who began an enrollment in the school 5 months prior to
today through today are returned.
For DAYS AFTER: student.startDate BETWEEN DATE 10152010
THROUGH DAYS AFTER 5.
All student who began an enrollment in the school on 10/15/2010
through 10/20/2010 (5 days after) are returned.
For MONTHS AFTER: student.startDate BETWEEN DATE 10152010
THROUGH MONTHS AFTER 5.
LIKE
Searches for test string in field.
NOT LIKE
Searches for test string and filters
data that is not like the user-defined
value.
SOUNDS LIKE
Returns names with similar Soundex
patterns.
CONTAINS
Searches for strings that include the
same data entered by the user in the
field. Any string that does not
contain the user-defined value is
filtered out. Any wildcard characters
entered are treated as standard SQL
wildcards.
Searches for strings that begin with
the same data entered by the user in
the field. Any string that does not
contain the user-defined value is
filtered out. Any wildcard characters
entered are treated as standard SQL
wildcards.
Searches for strings that end with
the same data entered by the user in
the field. Any string that does not
contain the user-defined value is
filtered out. Any wildcard characters
entered are treated as standard SQL
wildcards.
Returns fields that are completely
NULL (0 is considered a value).
STARTS WITH
ENDS WITH
IS NULL
IS NOT NULL
Returns all fields that are not NULL
(0 is considered a value).
All student who began an enrollment in the school on 10/15/2010
through 3/15/2011 (5 months after) are returned.
course LIKE hist
All courses like History 101 are returned.
course NOT LIKE hist
All courses not like Hist are returned.
This operator allows NULL values.
student.laseName SOUNDS LIKE Ball
Names such as "Ball," "Bell" and "Boll" are returned.
student.birthCountry CONTAINS Cana
All students with a Birth Country that contains "Cana" are returned.
student.birthCountry STARTS WITH Mexi
All students with a Birth Country that begins with "Mexi" are returned.
student.birthCountry ENDS WITH many
All students with a Birth Country that ends with "many" are returned.
student.stateID IS NULL
All students who do not have a state ID are returned.
student.ssn IS NOT NULL
All students who do not have a stateID are returned.
6 IS TODAY
Returns result dates as the current
date.
IS YESTERDAY
Returns result dates as of
yesterday's date.
IS TOMORROW
Returns result dates as of
tomorrow's date.
IN THE MONTH
Returns all database field data for
the month entered.
This operator allows both numbered
dates and spelled out dates (i.e., 10
or October). This operator also
allows for both upper and lower case
letters. If spelling out a month, users
must entered at least the first three
characters (i.e., Oct for October).
start.date IS TODAY
Entries where the start.date is the current date are returned.
start.date IS YESTERDAY
Results for one day previous to the current date are returned.
end.date IS TOMORROW
Results for one day after the current date are returned.
employment.districtStartDate IN THE MONTH October
All employees who have a district employment Start Date within the month
of October are returned. This operator does not look at the Year or
Calendar selected in the Campus toolbar. All historical and current district
employment records with a Start Date in October are returned.
= TRUE
Returns checkbox values of "true" (checkbox is marked)
enrollment.stateExclude = TRUE All students who have the State Exclude checkbox marked on their enrollment records are returned.
= FALSE
Returns checkbox values of "false"
(checkbox is not marked)
enrollment.stateExclude = FALSE
All students who do not have the State Exclude checkbox marked on their
enrollment records are returned.
10. Enter the Value for each operator selected.
Selecting the field “student.activeToday” for all Ad Hoc Filters created will report only students currently enrolled in the school. If unsure of what will be reported for the value leave the Operator and Value blank. Run the report . This will help determine the value needed. If the first running of the report isn’t reporting the exact information needed try changing the Field or Operator and/or Value. 7 11. Determine where to save the newly created filter.
12. Click Save or Save & Test.
Output Formatting
The Output Formatting allows you to control how each filter is reported/displayed on report.
1. Click Next.
2. Select
formatting
options.
8 FIELD
OUTPUT
SEQ
SORT
DIRECTION
COLUMN
HEADER
ALIGNMENT
FORMATTING
Fields selected from the All Fields window in the previous screen.
This checkbox determines whether or not the field is included in outputted data. Deselecting this checkbox means
data will still be filtered and reported for this field and operators but not included in the output.
This field determines the sequence of outputted data.
This field determines the sort order of outputted field data.
This field determines if data is sorted ascending or descending. This field is only available if a value is entered in
the Sort field.
This field determines what header is displayed for the field on files exported via the Data Export tool. Users are
encouraged to enter a logical and easily identifiable column header for each field as leaving the field blank results
in the field name (i.e., student.stateID) being reported.
The field determines how field data is aligned on files exported via the Data Export tool. Available options
include: Left, Center and Right.
The field determines how values are reported for the field when used in reports and exported files. Formatting
options are important for filters used with reports which require specific formatting in order for the file to be
correctly submitted to an entity or system. The following formatting options are available:
•
•
•
•
FORMATTING
(cont..)
LENGTH
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Zero Pad - numbers are padded with zeros to the left (i.e., 444 zero padded becomes 000444)
Space Fill - values are filled with spaces in order to reach required field length
Upper Case - values are reported entirely in uppercase (i.e., Course is reported COURSE). This option
is only available for text, char and varchar fields.
Lower Case - values are reported entirely in lowercase (i.e., Course is reported course). This option is
only available for text, char and varchar fields.
MM/DD/YYYY
MM-DD-YYYY
MMDDYYYY
YYYY/MM/DD
YYYY-MM-DD
YYYYMMDD
YYYY
YYYY/MM
YYYY-MM
YYYYMM
MM/YYYY
MM-YYYY
MMYYYY
1, 234.5; - 1,234.5
1,234.5; (1,234.5)
$1,234.00; -$1,234.00
This field determines the length of the column in the exported data file. This is the maximum amount of characters
allowed to be reported in the column. Data which exceeds the defined length is truncated on the right side. Zero
padding is added to the left of a value. Space filling is added to the right of a value.
A length must be defined for each field when exporting the filter in Fixed Width format within the Data Export
tool.
9 Filter Designer: Selection Editor
Index>Ad Hoc Reporting>Filter Designer>Selection Editor
The Selection Editor allows creation of a static list of students which can be used with Report Builder,
Data Export and existing reports within Infinite Campus.
1. Filter Type: Selection
Editor
2. Data Type: Student
3. Create
4. Enter a Selection
Name.
5. Quick Search allows for
narrowing of search results. 6. Select students from the
All Students list by
highlighting the name and
clicking the right-pointing
arrow key. To remove a
student, highlight the
student’s name listed in the
Selected Students list and
click the left-pointing arrow.
6. Click Save. Saving options
are the same as those in
Query Wizard. (pg. 8)
10 Letter Designer
Index>Ad Hoc Reporting> Letter Designer
The Letter Designer allows creation of letters that will pull data for use with existing Ad Hoc filters.
Letter formats created within the Letter Designer can be used by many users and generated as many
times as desired.
1. Select Blank Form Letter or Addressed
From Letter
2. Click the New Format Button.
3. Name the report. The name will appear
in the Saved Reports section.
4. Choose your formatting preferences.
(Ex: font, font size and font
color)
5. Type the layout of the letter
in the white text field. Select the
Campus fields and Subreports to allow for automatic
input of data.
These fields act like Mail Merge
fields when added to the report.
6. When the letter is complete,
select which users should see
the report in the Organized To:
field.
7. Select Save Format and the report will appear in the Saved Reports section (after clicking on the
Report Designer icon).
11 Letter Builder
Index>Ad Hoc Reporting>Letter Builder
The Letter Builder merges Saved Filters and Saved Reports to form letters. 1. Select the Filter(s)
from the list of Saved
Filters in the left-hand
window. At least one
filter needs to be
selected.
2. Select the Letter from
the list of Saved Letters
in the right-hand window.
Only one Report can be
selected at a time.
3. Select the Set
Operation from the
dropdown list. Union –
combines two queries by
performing the
equivalent of appending
one query onto the other (will show all students in all filters). Intersection – generates a report to
students who make up the “overlap” of multiple ad hoc filters (will only show the students that are in
all filters selected, i.e. if a student is listed in one filter but not in the other then that student will not
show on the report).
4. Select the Sort Options order the report should generate.
5. Build Letters.
6. The report will open in a new window for you to view and print (Adobe-PDF format).
12 Data Export
Index>Ad Hoc Reporting>Data Export
The Data Export tool will export query wizard filters out of Campus in the format desired to be used
outside of Infinite Campus. The Data Export will not allow you to export “Selection Editor” filters.
1. Select the Filter to be exported from the Saved Filters list.
2. Select the Export Format in which the filter will be displayed.
•
•
•
•
•
HTML list report –opens the report in a new Web window
XML – shows HTML coding values
Delimited Values (CSV) – opens in an Excel spreadsheet
(For best results use the default settings for Delimiter, Column Display Header and
Double Quote Data.)
Fixed width -- Exports the results of the ad hoc filter in Fixed Width format
PDF report – opens the filter in PDF format
o Select the desired Print Option
Field Name Only – prints only the field name. Ex. If your filter contains
the Student’s Last Name and is found in the Student Table your report will
only print “lastName” as the row header.
Table Name and Field Name – prints the table and the field name on the
report. Ex. If your filter contains the Student’s Last Name and is found in
the Student Table your report will print “student.lastName” as the row
header.
4. Click the Export button and the report will display in the format selected.
13 Sample Ad Hoc Reports
Students with Program Flags
Identifies students with program flags and includes the description of the flag.
Type: Query Wizard
Filter Data Type: Student
Demographics>StateID
Demographics>Last Name
Demographics>First Name
Demographics>grade
Demographics>activeToday>(operator: = True)
Learner>Programs>name
Learner>Programs>startDate
Learner>Programs>endDate
Learner>Programs>studentDescription
Note: You can limit your results by using operators and values.
Behavior by Homeroom Teacher/Team
Identifies students with behavior events by homeroom or team.
Type: Query Wizard
Filter Data Type: Student
Demographics>Last Name
Demographics>First Name
Demographics>gender
Demographics>grade
Demographics>activeToday>(operator: = True)
Demographics>teamName
Demographics>homeroomTeacher
Behavior>Behavior Incident Detail>incidentDate
Behavior>Behavior Role Detail>role
Behavior>Behavior Resolution Detail>resolutionName
Note: You can limit your results by using operators and values.
14 Period Roster
List of students in a period.
Type: Query Wizard
Filter Data Type: Student
Demographics>Last Name
Demographics>First Name
Demographics>grade
Demographics>activeToday>(operator: = True)
Learner>Schedule>Course/Section>courseNumber
Learner>Schedule>Course/Section>courseName
Learner>Schedule>Course/Section>teacherFullName
Learner>Schedule>Course/Section>Section Schedule>periodStart
Note: You can limit your results by using operators and values.
No Pass No Drive
List of students who would not be eligible for getting driving permit.
Type: Query Wizard
Filter Data Type: Student
Demographics>Last Name
Demographics>First Name
Demographics>age (operator: IN value: 16,17)
Demographics>activeToday>(operator: = True)
Grading>Grading Detail>termName (operator: LIKE value: SEM 1)
Grading>Grading Detail>gradingScore (operator: LIKE value:%F)
Grading>Grading Detail>gradingTask (operator: = value: Semester 1) *will only work with posted
grades
Staff Email Addresses
To get a current list of staff email addresses in Infinite Campus.
Type: Query Wizard
Filter Data Type: Census/Staff
Demographics>Last Name
Demographics>First Name
Census>Person Contacts>email
Staff>Assignment>Assignment endDate (operator:= is null)
15 Sample Reports
You may not have tool rights to access all these reports. However,
someone in your building will have access to any specific report in IC.
Student Information Reports
Index>Student Information>Reports>Testing Reports
Available reports:
ACT Benchmark
ACT Scores
AP Exams
CATS Scores
Explore/Plan
MAP Compare
ACT/Compass Benchmark
College Ready
GAP Students
Next Enr (Geo) Scores
EOC Exams
MAP/UL by Course
Unbridled Learning
Index>Student Information>Reports>Activity Eligibility
The Activity Eligibility Report can be used to monitor student’s academic grades to determine
participation eligibility.
Index>Student Information>Reports>Geo Code Placement
This report will display students with addresses residing in the school boundary. This report
will only be available until the rollover for the next school year occurs.
Index>Student Information>Reports>Student List
This report will display students enrolled in your school for the current/new school year.
Index>Grading & Standards>Reports>Grade Distribution
This report will display a breakdown of total letter grades given by a teacher in a particular
course/section and by ethnicity within a selected grading period.
Index>Grading & Standards>Reports>Grade Distribution
This report lists roster counts by gender and Special Ed., section placement, team, room, and
teacher.
16