pop-up windows - fcps.net · web view6/29/2017ah-40icadhoc1718.docx. ... pop-up windows from...

Ad Hoc Reporting

Infinite Campus

2017 - 2018

Office of Education Technology

Mailing Address: 1126 Russell Cave Rd, Lexington, KY 40505

Physical Address: 701 E Main St., Lexington, KY 40502

Fax: 381-4140

Download the latest version of this document online:

Version 1718.01

Index > F CPS IC Support

https://my.fcps.net/ic-support

Table of Contents

GENERAL INFORMATION.......................................................................................................................5

Pop-Up Windows..................................................................................................................................... 6

Firefox.................................................................................................................................................. 6

Internet Explorer..................................................................................................................................6

MANAGE EXISTING FILTERS...................................................................................................................8

Saved Filters.............................................................................................................................................8

Testing / Copying Saved Filters................................................................................................................8

Delete Filters............................................................................................................................................8

Organizing Filters into Folders................................................................................................................. 9

Create Filter Folders.............................................................................................................................9

Save Filters to Folders..........................................................................................................................9

Move Filters between Folders..............................................................................................................9

BASIC AD HOC REPORTING..................................................................................................................10

Data Viewer........................................................................................................................................... 10

Creating a New Data Viewer Report..................................................................................................10

Editing an Existing Data Viewer Report..............................................................................................11

Copying a Data Viewer Report...........................................................................................................11

Applying an Ad Hoc Filter to an Existing Data Viewer Report............................................................11

Filter Designer........................................................................................................................................12

Creating New Filters Using Query Wizard..........................................................................................12

Define Data Filter Output Formatting................................................................................................14

Define Data Filter Grouping, Calculations and Subtotals...................................................................15

Selection Editor – Create a New Student Filter..................................................................................16

Selection Editor – Create a New Census/Staff Filter...........................................................................17

Letter Designer.......................................................................................................................................18

Creating a New Letter Format............................................................................................................18

Letter Builder......................................................................................................................................... 19

Data Export............................................................................................................................................ 20

6/29/2017 AH-5 document.docxOffice of Education Technology Fayette County Public Schools Lexington, KY

Table of ContentsADVANCED AD HOC REPORTING.........................................................................................................21

Adding Functions to Filters.................................................................................................................... 21

Logical Expressions.................................................................................................................................22

Pass-Through SQL Query........................................................................................................................23

Data Analysis..........................................................................................................................................24

Creating a New Pivot..........................................................................................................................24

Pivot Type.......................................................................................................................................... 25

Measures........................................................................................................................................... 26

Dimensions........................................................................................................................................ 27

APPENDIX...........................................................................................................................................28

Ad Hoc Filter Operators......................................................................................................................... 28

Ad Hoc Function Descriptions................................................................................................................32

Commonly Used Codes and Descriptions...............................................................................................34

Student > Demographics > raceEthnicity...........................................................................................34

Student > Demographics > birthCountry............................................................................................34

Student > Demographics > homePrimaryLanguage...........................................................................34

Sample Pass-Through SQL Queries........................................................................................................ 35

Student Queries................................................................................................................................. 35

Course/Section Queries..................................................................................................................... 39

Census/Staff Queries..........................................................................................................................40


GENERAL INFORMATION

Ad Hoc Reporting Tools provide users the ability to filter, display and report specific database information based on defined criteria. Ad Hoc Filters can also be used in conjunction with many reports throughout Infinite Campus to further filter results to a specific set of data or students.


Information gathered and exported from Infinite Campus is

confidential and should not be distributed to unauthorized

personnel or unauthorized outside entities. Distribution to

unauthorized personnel or unauthorized outside entities is a

violation of the Federal law: Family Educational Right and Privacy

Act (FERPA).

http://www.google.com/url?sa=i&rct=j&q=&esrc=s&frm=1&source=images&cd=&cad=rja&uact=8&ved=0CAcQjRw&url=http://mrscbradley.com/2014/06/28/caution/&ei=4CYxVcrwOYbPsAWc3ICICw&bvm=bv.91071109,d.cWc&psig=AFQjCNGXK4D76O0KyESCgcOGsKg6kjiayQ&ust=1429370975136736

GENERAL INFORMATION

Pop-Up WindowsPop-up windows from Infinite Campus must be enabled for various features of Ad Hoc Reporting.

Firefox1. In the upper right hand corner, click the Tools menu2. Click: Options3. Click: Content4. Choose one of the following options:

a. Option 1i. Uncheck Block pop-up windows

b. Option 2i. Check Block pop-up windowsii. Click: Exceptions

iii. Enter the following website in the Address of website field: https://fayetteky.infinitecampus.org

5. Click: Save Changes

Internet Explorer1. In the upper right hand corner, click the Tools button2. Click Internet Options3. Click the Privacy tab4. Choose one of the following options:

a. Option 1i. Uncheck Turn on Pop-up Blocker

b. Option 2i. Check Turn on Pop-up Blockerii. Click: Settings

iii. Enter the following website in the Address of website to allow field: https://fayetteky.infinitecampus.org

iv. Click: Addv. Click: Close

5. Click: OK


https://fayetteky.infinitecampus.org/

https://fayetteky.infinitecampus.org/

GENERAL INFORMATION

Campus CommunityPATH: App Switcher > Campus Community

The Campus Community provides IC users access to the Infinite Campus Knowledge Base, Forums and a Video Library. Staff members wishing to use this resource must create a CampusID in order to access this information.

To access Campus Community, click the App Switcher in the Campus Toolbar, then click: Campus Community OR go to the following URL if our IC application is unavailable: https://community.infinitecampus.com.

Creating a CampusID1. Click the App Switcher in the Campus Toolbar; click: Campus Community2. First time users, click: Continue3. Read the Terms of Use, and check the “I have read and agree to the Terms of Use” checkbox4. Click: Continue5. Enter your account information. Remember to note this information.6. When all fields have been completed and verified, click: Continue7. Enter your profile information8. Click: Finish9. Activate the account by following instructions in the email you receive from Infinite Campus


https://community.infinitecampus.com/

MANAGE EXISTING FILTERS

Saved FiltersPATH: Index > Ad Hoc Reporting > Filter Designer

The Saved Filters section displays the name of Ad Hocs created by the user or a user within a specific User Group. Ad Hocs listed first are filters created by the user and cannot be accessed by other users.

Always make a copy of a filter contained in a group and save it to your personal User Account. See below for steps on copying filters.

Testing / Copying Saved FiltersPATH: Index > Ad Hoc Reporting > Filter Designer

Select a filter and click Test to quickly display the filter results.

Select a filter and click Copy to make a copy of any saved filter. A pop-up message will appear indicating the filter has been copied. Copied filters are named Copy of [Original Filter Name].

Delete FiltersA saved filter can also be deleted. All users have the ability to delete filters saved to their User Account. Depending on user rights, a user may not be able to delete filters saved to a User Group.

1. Go to: Index > Ad Hoc Reporting > Filter Designer2. Select the filter from the Saved Filters window and click: Delete


These filters were created by the user and saved to the User Account

These filters were created by a user with rights to the User

Group. Filters saved here can be accessed by another user with

rights to the group.

MANAGE EXISTING FILTERS

Organizing Filters into FoldersThe Filter Designer tool allows users to create folders for organizing and storing Ad Hoc filters. Folders can be organized in a hierarchy format, where sub-folders exist within parent folders. By creating folders, users can better manage large volumes of existing Ad Hoc filters and group them in a logical order.

Create Filter Folders1. Go to: Index > Ad Hoc Reporting > Filter Designer2. Click: Create a new Folder3. Select the following:

a. Parent Folder – If the folder will serve as a main folder, leave this field set to (No Parent). If the folder should be assigned to a parent folder, select the parent folder.

b. Folder Name4. Click: Save

Save Filters to FoldersOnce folders have been created, Ad Hoc filters can now be assigned to those folders.

To assign an Ad Hoc Filter to a folder, click the User Account radio button and select the appropriate folder from the Folder field.

In the screen shot on the right, the Ad Hoc Filter is being saved to the Ad Hoc Training folder.

Move Filters between FoldersAd Hoc Filters can easily be moved and organized between folders.

1. Go to: Index > Ad Hoc Reporting > Filter Designer2. Drag and drop any filter saved to the User Account into the desired folder3. A pop-up message will appear, asking the user to confirm. Click: OK.


BASIC AD HOC REPORTING

Data Viewer

Creating a New Data Viewer Report1. Go to: Index > Ad Hoc Reporting > Data Viewer2. Click: New3. Select the following:

a. Create New – Reportb. Report Focus – Student, Census/Staff or Course/Section

4. Click: OK5. Select the following:

a. Enter the name of the report in the Name fieldb. Select the desired fields

i. Fields displayed below the Name field are based on the selection made in the Report Focus dropdown list in step 3. The fields may be filtered by using the search field.

ii. Select the field(s) to be included on the report from the Field Selection area and drag them to the Report Preview pane. Double-clicking a field will automatically place the field at the end (far right) of the report. A preset group of fields will be pre-selected for you based upon the selected Report Focus. These fields may be edited as needed.

c. The Report Preview will refresh each time a field is added to show a real-time view of how the report will look as well as the first ten records of reported data.

d. Columns may be rearranged by clicking and dragging column headings to the desired location. Columns may be renamed by double clicking a column’s header.

e. Each column may be sorted or grouped by clicking the arrow on the right side of each column heading. Selecting Group By will organize each record by the data reported in the selected column.

f. Each column may be filtered by clicking Add New Filter and selecting options for Operator and Value. To remove a filter, click the X in front of the appropriate filter.

6. Click: Save7. To generate the report, click: Print



Data ViewerEditing an Existing Data Viewer Report

1. Go to: Index > Ad Hoc Reporting > Data Viewer2. Select the desired report to edit from My Reports3. Click: Edit4. Make changes as needed5. Click: Save

Copying a Data Viewer Report1. Go to: Index > Ad Hoc Reporting > Data Viewer2. Select the desired report to copy from My Reports3. Click: Copy4. To edit the copy, follow steps above for 5.6. BASIC AD HOC REPORTING

Applying an Ad Hoc Filter to an Existing Data Viewer Report1. Go to: Index > Ad Hoc Reporting > Data Viewer2. Select the desired report from My Reports3. Select the following:

a. Ad Hoc Filter – Select the appropriate Ad Hoc Filter to limit report resultsb. Report Output Format

4. Click: Generate Report



Filter Designer

Creating New Filters Using Query WizardQuery Wizard functionality allows users to easily create Ad Hoc Filters by organizing elements in a straightforward manner. Query Wizard Filters are dynamic and will always pull current information from the database based on the fields and filter options selected.

1. Select Filter and Data Typea. Go to: Index > Ad Hoc Reporting > Filter Designerb. Select the following:

i. Filter Type - Query Wizardii. Data Type - This determines which type of

fields are available for selection: Student, Census/Staff or Course/Section. Select the appropriate type.

c. Click: Create2. Selecting Categories and Fields

a. Enter a Query Name for the filter

b. Enter a Short Description and/or Long Description about the filter (if applicable). These fields provide additional information and context about the filter. This information is displayed when a user selects the filter in the Saved Filters window as well as when the filter is being edited.

c. Select the data elements from the All Fields list by clicking on them. The fields will move to the Selected Fields list. To remove a field from the Selected Fields list, click on it to highlight and click the left pointing arrow button.

i. To search for a particular field, enter part of the name of the field in the Filter By section and click the Search button. Select the appropriate options for the query. All fields that contain that name will appear in the All Fields list. To clear the selection, click the Clear button and all available fields will appear again.

d. Click: Next



Filter DesignerCreating New Filters Using Query Wizard

3. Filter the dataa. Enter a value for each

applicable Operator. See page 30, Ad Hoc Filter Operators for a list of available Operators and their function.

b. The Logical Expression field may be used, if necessary. See page 34, Ad Hoc Function Descriptions for more information on this feature. If this feature is not used, data will only be exported if it meets all Operator and Value items entered.

c. Select whether to save the filter to a User Account or to a User Group. Selecting User Groups will allow the filter to be saved to user-selected User Groups.

4. Click: Save or Save & Test5. If desired, click: Next for Output Formatting options. See page 16, Define Data Filter Output

Formatting for further instructions.6. If desired, click: Next again for Grouping, Calculations and Subtotals options. See page 17, Define

Data Filter Grouping, Calculations and Subtotals for further instructions.


Helpful Hints…

Adding the student.activeToday field for all student Ad Hoc Filters is strongly recommended. Set the student.activeToday field to TRUE to include only actively

enrolled students (see the example above).

If you are unsure of what Operator and Value to enter, leave both blank. Run the filter to view data first, then make adjustments to your filter as needed.

If the first attempt of the filter isn’t displaying the desired information, try changing the Field or Operator and/or Value.


Filter DesignerDefine Data Filter Output FormattingPATH: Field Selection > Filter Parameters > Output Formatting

The Output Formatting editor allows users to control how each field is reported and displayed when exported via the Data Export tool. All of the features listed below are optional.

1. Output distinct records – Option will remove duplicated records, meaning if the student had multiple rows returned in the query that had exactly the same results checking the checkbox would return only one row.

2. Output – Uncheck this option if the field should NOT appear in filter output but still be used in the filter

3. Seq – The order the fields should appear. If left blank, the fields will appear in the order displayed on the screen.

4. Sort – Enter a number in the Sort field to determine the order in which fields will be sorted5. Direction – If a number was entered in the Sort field, determines how the field should be sorted

by selecting a Direction. Fields can be sorted by ascending or descending direction. If the Sort field is left blank, fields will be sorted alphabetically in the order the fields appear on the screen.

6. Column Header – Enter a value to name the column something other than the system default. If left blank, the field name will be used as the header for the column (i.e., student.lastName will be displayed instead of the user entered column header Student Last Name)

7. Alignment – Determines if the field will be aligned to the Left, Center or Right of the column.8. Formatting – Specify how the data should be reported when export in the Formatting dropdown

list. This feature is not commonly used.9. Length – Enter the maximum number of characters the field should report before truncation in

the Length field. This is required if data will be exported using the Fixed Width format. This feature is not commonly used.



Filter DesignerDefine Data Filter Grouping, Calculations and SubtotalsPATH: Field Selection > Filter Parameters > Output Formatting > Grouping and Aggregation

The Grouping and Aggregation editor allows users to group fields into sections and report specific aggregates/sub-totals for each section.

1. Select each field to include in each tier in the Group By dropdown list. This will determine which fields are grouped into sections and allow the field to have separate aggregate/sub-totals reported.

2. Determine how aggregate/sub-total data should be reported by selecting ascending or descending in the Group Order dropdown list.

3. Specific calculations may be applied to groups by selecting the applicable field in the Aggregate/Sub Total by dropdown list and the calculation type in the Aggregate Type dropdown list.

4. Select whether to save the filter to a User Account or to a User Group. Selecting User Account will allow the user to save the filter to a specific folder. Selecting User Groups will allow a copy of the filter to be saved to user-selected User Groups. Each group will receive their own copy.

5. Click: Save & Test to test the filter. Pop-ups from Infinite Campus must be enabled on the user’s browser in order to view test results. See page 8, Pop-Up Windows for instructions on enabling pop-up windows.

6. Click: Save



Filter DesignerSelection Editor – Create a New Student FilterThe 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.


i. Filter Type – Selection Editorii. Data Type – Student

c. Click: Create2. Student Selection

a. Selection Name – Enter the name of your filterb. Short Description and Long Description – Optional, but recommended. These fields

provide additional information and context about the filter. This information is displayed when a user selects the filter in the Saved Filters window as well as when the filter is being edited.

c. Filter students within the All Students list by using the Quick Search options. Enter one or all of the following:

i. Active Todayii. Grade

iii. Nameiv. Sortv. Using the filters in the Quick Search area will allow you to narrow your results

d. 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 in the Selected Students window and click the left-pointing arrow.

3. Select whether to save the filter to a User Account or to a User Group. Selecting User Groups will allow the filter to be saved to user-selected User Groups.

4. Click: Save5.



Filter DesignerSelection Editor – Create a New Census/Staff FilterThe 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.


i. Filter Type – Selection Editorii. Data Type – Census/Staff

c. Click: Create 2. Census/Staff Selection

a. Selection Name – Enter the name of your filterb. Short Description and Long Description – Optional, but recommended. These fields

provide additional information and context about the filter. This information is displayed when a user selects the filter in the Saved Filters window as well as when the filter is being edited.

c. People Search – enter the start of a Name for those to include in the filter. At least one letter needs to be entered to return results. Or, select a filter from the Filter dropdown list. Both fields can be populated, resulting in the intersection of the two entries. For example, if a user selects a filter that only contains eighth graders and enters “Anderson” in the Name field, the search will only return eighth graders named Anderson.

i. To remove a chosen filter, click the X near the Filter dropdown arrowd. Include – Determine which type of people to include in the search: Students, Staff or

Other People. All three checkboxes can be selected; at least one option needs to be chosen.

e. Click: Search. Results display with the name of the person, some identifying information and the type of person.

f. Click: Add to add the desired individual to the filter. The names display in the Selected People box. Use the Add All button to add all people in the results to the selection.

g. Click the Back to Search link to enter a new search criteriah. If they should not be included, click the Remove link next to their name. Use the Remove

All button to remove all selected people from the selection.3. Click: Save4.



Letter DesignerThe Letter Designer allows creation of letters using a WYSIWYG (What You See Is What You Get) editor. Letter formats created within the Letter Designer can be used by many users and generated as many times as desired.

Creating a New Letter Format1. Go to: Index > Ad Hoc Reporting > Letter Designer2. Select the following:

a. Create a New Letter Formati. Blank Form Letter – No fields are pre-populatedii. Addressed Form Letter – The student’s address information will be included at the

top of the page. This format is useful for letters printed and mailed to parents/guardians.

b. Click: New Format3. Enter the following:

a. Nameb. Short Description and Long Description – Optional, but recommended. Information

written in these fields will display for users when modifying or editing the letter. Text entered in the Long Description field will display on screen when the letter is selected within the Letter Building and Letter Designer tools.

c. Begin writing the letter within the text field. To include dynamic Campus Field options and sub-reports, select the two buttons on the far-right side of the text format bar.

d. Use the formatting options available as needed

4. Select the appropriate Organized To option5. Click: Save Format



Letter BuilderThe Letter Builder tool allows users to combine Ad Hoc Filters with Letter Designer to create specific letters based on filtered student data.

1. Go to: Index > Ad Hoc Reporting > Letter Builder2. Select the following:

a. Saved Filter(s) – Select which Saved Filter(s) to include within the letter. To select more than one filter, use the CTRL key on your keyboard.

b. Saved Letter – Select which Saved Letter to include in the letter. You may only select one Saved Letter at a time.

c. Set Operation – If more than one Saved Filter is selected, determine how the letter will filter data by selecting the Set Operation. For more information about this field, see the Filter Operations definitions below.

d. Sort Option – Select as desired3. Click: Build Letters

Union – This Set Operation combines two or more filters by appending one to the other. An example of this would be all Baseball Team members and all 10th grade male students. In the illustration below, if the Union Operation is selected, a letter would generate for a student if they fell in either Ad Hoc 1 or Ad Hoc 2.

Intersection – This Set Operation is used to find data that one or more filters have in common. An example of this would be all Baseball Team members who are also 10th grade male students. In the illustration below, if the Intersection Operation is selected, a letter would generate for a student only if they existed in both Ad Hoc Filters, or in other words where the groups “intersect”.


Ad Hoc 1 Ad Hoc 2

Baseball Team

10th Grade Male

Students

10th Grade Male Students

on the Baseball Team


Data ExportThe Data Export tool allows users to view the results of Ad Hoc Filters in multiple report formats and files. All Ad Hoc Filters created via the Filter Designer are able to be exported via the Data Export tool.

1. Go to: Index > Ad Hoc Reporting > Data Export2. Select the following:

a. Saved Filters - Select the filter to be exportedb. Pick an Export Format – Select the desired format of the exported Ad Hoc Filter by clicking

the appropriate radio button. See the Data Export Fields table below for more information.

3. Click: Export

Field DescriptionHTML list report Exports the results of the Ad hoc filter in HTML format.XML Exports the results of the Ad hoc filter in XML format.

Delimited values (CSV)

Exports the results of the Ad hoc filter in a number of Delimited values (CSV). Options include:

Comma - Exports the Ad hoc filter in Comma delimited format. Tab - Exports the Ad hoc filter in Tab delimited format Pipe - Exports the Ad hoc filter in Pipe delimited format Caret - Exports the Ad hoc filter in Caret delimited format The Include column display header checkbox allows users to control whether or not a header is displayed on the exported Ad hoc filter

The Double quote data dropdown list allow users to determine when double quotations are added to exported Ad hoc filter data

The Include Subtotals checkbox allows users to properly display aggregate totals that may be included in the filter.

Fixed width Exports the results of the Ad hoc filter in Fixed Width format

PDF report

Exports the results of the Ad hoc filter in PDF format. Selecting the Field Name Only radio button means field names in the header will only display the field name as it exists in the database (i.e., grade is reported for the student.grade database field). Selecting the Table Name and Field Name radio button means both the table and field name will display in the header on the report (i.e., student.grade is reported in the header for the student.grade database field ). Because Ad hoc filter fields can be grouped and report aggregate data, two variations of the PDF format exist. Grouped filter fields display as specified. Ungrouped Ad hoc filters display in a standard list format. For more information about grouping Ad hoc filter fields, see page 17, Define Data Filter Grouping, Calculations and Subtotals.

ADVANCED AD HOC REPORTING


Adding Functions to FiltersFunctions can be added to filters which allow logic to be applied to field columns when the filter is generated via the Data Export tool.

1. On the Field Selection page, add a function to a filter by selecting the Add Function button. The Function Editor appears in a new window.

2. Enter the following:a. Nameb. Function – Select the desired Function. See page

34, Ad Hoc Function Descriptionsc. The Constant

Value field is only used with the Constant Function.

d. Use the Filter by Search field to search for desired fields. Select which fields to include within the function by clicking on each field within the All Fields window.

3. Click: Save



Logical ExpressionsThe Logical Expression field allows users to incorporate conditions between fields within a filter. This field provides an effective way to use the OR, AND and NOT conditions between fields and groups of fields. Only fields assigned an Operator are allowed to be included within logical expressions.

Logical expressions can be grouped using ( ) symbols and the ID number to define the order in which the tool should include or exclude a record. In the screen shot below, the “Logical Expression” is looking for potential overage students based on their grade level. Four grade levels are being reviewed (IDs: 4, 6, 8 and 10), further defined by their age (IDs: 5, 7, 9 and 11). The query below will return all 9th grade students over 15 years old, all 10th grade students over 16, all 11th grade students over 17 as well as all 12th grade students over 18.



Pass-Through SQL QueryUsers 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.

1. Go to: Index > Ad Hoc Reporting > Filter Designer2. Select the following:

a. Filter Type – Pass-through SQL Queryb. Data Type – Choose as desired

3. Click: Create4. Select the following:

a. Filter Name – Enter a name for the filterb. Short Descriptionc. Long Descriptiond. In the text fields on the left hand side of the screen, enter more querying definitions. The

beginning part of the query is already written. Users can join two or more tables and fields here.

e. In the second text field, enter more criteria for the SQL WHERE clause.f. Click: Test Query to verify that the data returned is the data needed. Results will appear

in the Test Query Results field on the right hand side of the screen.g. Select which group to Save To

5. Click: Save

See page 37 for Pass-Through SQL Query examples.


Helpful Hints…

Pass-Through SQL Queries do not return data in a specific order. To sort the results by student name, add the following to the end of the text in Box B:

ORDER BY student.lastName, student.firstName


Data AnalysisData Analysis requires Adobe Flash Player. Infinite Campus recommends Data Analysis be used with a Firefox browser.

The Data Analysis tool analyzes Campus data and allows users to measure student progress as well as understand and visually present school performance data. This tool allows users to cross reference student information by dimension and produce visual results of this data in an easy to understand format.

Data Analysis uses pivot table functionality. A pivot table is a data summation tool often found in spreadsheets and other business intelligence software. Pivot table tools can sort, count and total the data stored in a table or spreadsheet, then display the data in a new table.

Creating a New Pivot1. Go to: Index > Ad Hoc Reporting > Data Analysis2. Select the desired Measure under “Create a New Pivot”. See page Error: Reference source not

found, Pivot Error: Reference source not found for a description of each Measure.3. Click: New Pivot4. Select the following:

a. Pivot Informationi. Pivot Name – Required. Enter the desired name.ii. Created Date – Optional. Enter the date the pivot was created.

b. Student Filtersi. Ad Hoc Filter – Optional. If desired, select an Ad Hoc Filter that contains a specific

student set on which to base the pivot table.ii. Effective Date – Optional. Only students actively enrolled on the entered date will

be included.iii. Active only – Optional. If checked, the pivot will include only currently enrolled

students.c. Measures – Optional. These values allow further analysis of the data. See page 28,

Measures for a description of each measure.d. Dimensions – Users must select at least two dimensions to use in the pivot. These fields

are available within the user interface throughout Campus. Dimensions allow users to designate specific data elements that are pulled into the pivot table. For a description of each Dimension please reference the Data Analysis Discussion article on Infinite Campus’s Campus Community.

e. Organized to5. Click: Display Pivot



Data AnalysisPivot TypeType DescriptionStudent Counts Bases information on total number of students enrolled.

No show students are not included. Students marked as State Exclude are included. Count of Students is the default Measure on the field list.

Attendance Mark Counts

Bases information on students’ attendance records. Students must be on a roster in order to report. Students must have at least one attendance event. Each attendance event displays as one count. No show students are not included. Students marked as State Exclude are included. Sum of Period Absences is the default Measure on the field list.

Behavior Event Counts

Bases information on students’ behavior records. Each behavior event displays as one count. No Show students are included. Students marked as State Exclude are included. Count of Behavior Events is the default Measure on the field list.

Grade Mark Counts

Bases information on the Total Number of Students Enrolled + GPA + Credits Earned. GPA is based on the student's Grades tab. Credits Earned is based on the student's Transcript tab. Each credit earned displays as one count. No Show students are included. Students marked as State Exclude are included. Count of Grades, Sum of Credits Earned and Weighted Term GPA are the

default Measures on the field list.Transcript Mark Counts

Bases information on students' transcripts. Only students who have transcript credits on the Transcript tab are included. Each posted transcript credit on a student's Transcript tab displays as one

count. No Show students are included. Students marked as State Exclude are included. Count of Transcript Records, Sum of Credits Earned and Transcript GPA are

the default Measures on the field list.Special Education Analysis

Bases information on students who have a special education record. Students must have a locked IEP in order to report. No Show students are included. Students marked as State Exclude are included. Count of Special Education students is the default Measure on the field list.



Data AnalysisMeasuresAvailable Measure Description Pivot Type

Display Average Cumulative GPA Measure

The pivot displays the Average Cumulative GPA for all reported students.

Student Counts Attendance Mark Counts Behavior Event Counts Grade Mark Analysis Transcript Mark Analysis Special Education Analysis

Display Average Test Scores Measure

The pivot will display students' average test scores.

Student Counts Attendance Mark Counts Behavior Counts Grade Mark Analysis Transcript Mark Analysis Special Education Analysis

Use only MAX Test Score The pivot will display students' highest test score.

Student Counts Attendance Mark Counts Behavior Event Counts Grade Mark Analysis Transcript Mark Analysis Special Education Analysis

Display ADM/ADA Summaries

The pivot will display students' Average Daily Membership (ADM) and Average Daily Attendance (ADA).

Student Counts

Use percent enrolled in ADM/ADA calculations

This field determines which views are used for calculating ADM/ADA and whether or not Percent Enrolled is taken into account.

Student Counts



Data AnalysisDimensionsDimensions allow users to designate specific data elements that are pulled into the pivot. Users must select at least two dimensions to use in the pivot. These fields are available within the user interface throughout Campus. Select dimensions by marking the checkbox next to the desired field.

For details on each Dimension, reference the “Data Analysis” documentation posted on Infinite Campus’s Campus Community.


APPENDIX

Ad Hoc Filter OperatorsOperator Results Example= (Equals) Returns exact match of value student.grade=3

Only students in grade 3 are returned.< > (Does not equal)

Returns results not equal to the value.

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.

> (Greater than) Returns results that are greater than the entered numeric value.

student.age > 16 All students older than 16 years of age are returned.

>= (Greater than or equal to)

Returns results that are greater than or equal to the entered numeric value.

student.age >= 16 All students 16 years of age and older are returned.

< (Less than) Returns results that are less than the entered value.

student.age < 16 All students under the age of 16 are returned.

< (Less than or equal to)

Returns results that are less than or equal to the entered numeric value.

student.age <= 16 All students 16 years of age and younger are returned.

IN Includes value. student.grade IN 9,10 All students in 9th and 10 grade are returned.

When using this format, do not put spaces after the commaNOT 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


APPENDIX

Ad Hoc Filter OperatorsOperator Results ExampleBETWEEN Filters data between two

specified values. Works with numbers, dates and strings. If a date field is selected, the following options are available:

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. 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 (sub-option 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-option2. 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.

For BETWEEN: student.stateID BETWEEN 00001 THROUGH 100000.

All students with a State ID between 00001 - 100000 are returned.

For DATE: student.birthDate BETWEEN DATE 10151995 THROUGH DATE 10152010. 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. 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. All student who began an enrollment in the school on 10/15/2010 through 3/15/2011 (5 months after) are returned.

IS CURRENT USER Returns the current user’s ID. For learningPlan.planManagerPersonID IS CURRENT USER The current user's ID is reported along with data only applicable to him/her.


APPENDIX

Ad Hoc Filter OperatorsOperator Results ExampleLIKE Searches for test string in field. course LIKE hist

All courses like History 101 are returned.NOT LIKE Searches for test string and filters

data that is not like the user-defined value.

course NOT LIKE hist All courses not like Hist are returned.This Operator allows NULL values.

SOUNDS LIKE Returns names with similar Soundex patterns.

student.laseName SOUNDS LIKE Ball Names such as "Ball," "Bell" and "Boll" are returned.

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.

student.birthCountry CONTAINS Cana All students with a Birth Country that contains "Cana" are returned.

STARTS WITH 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.

student.birthCountry STARTS WITH Mexi All students with a Birth Country that begins with "Mexi" are returned.

ENDS WITH 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.

student.birthCountry ENDS WITH many All students with a Birth Country that ends with "many" are returned.

IS NULL Returns fields that are completely NULL (0 is considered a value).

student.stateID IS NULL All students who do not have a state ID are returned.

IS NOT NULL Returns all fields that are not NULL (0 is considered a value).

student.ssn IS NOT NULL All students who do not have a stateID are returned.

IS TODAY Returns result dates as the current date.

start.date IS TODAY Entries where the start.date is the current date are returned.

IS YESTERDAY Returns result dates as of yesterday's date.

start.date IS YESTERDAY

IS TOMORROW Returns result dates as of tomorrow's date.

end.date IS TOMORROW Results for one day after the current date are returned.


APPENDIX

Ad Hoc Filter OperatorsOperator Results ExampleIN 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 enter at least the first three characters (i.e., Oct for October).

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.


APPENDIX

Ad Hoc Function DescriptionsFunction

Name Description Sample Function Sample Result

Constant

Output the Constant Value entered on each record returned when the filter is exported.

Coalesce

Allows users to define multiple fields where logic pulls the first field and if NULL, the second field is pulled and so on until a value is found. Logic pulls field values in the order fields are selected in the Function Editor.

ConcatenateAllows field values to be appended when the filter is exported.

AddAllows field values to be added together to output a single result.

Subtract

Allows field values to be subtracted from each other to output a single result.

MultiplyAllows field values to be multiplied together to output a single result.

DivideAllows two or more fields to be divided and output a single result.

APPENDIX6/29/2017 AH-34 document.docxOffice of Education Technology Fayette County Public Schools Lexington, KY

Ad Hoc Function DescriptionsFunction

Name Description Sample Function Sample Result

Record Count

Allows users to report a record count for the field selected.

Distinct Count

Allows users to report a distinct count for the field selected.

MINAllows users to report the minimum value for a field.

MAXAllows users to report the maximum value for a field.

SumAdds the value or field selected over all other aggregated fields.

AvgAllows users to report the average value for a field.


APPENDIX

Commonly Used Codes and Descriptions

Student > Demographics > raceEthnicity

Student > Demographics > birthCountryFind a list of these codes and country names online: Index > IC Support Site > Instructional Manuals > Birth Country Codes and Descriptions

Student > Demographics > homePrimaryLanguageFind a list of these codes and languages online:Index > IC Support Site > Instructional Manuals > Primary Language Codes and Descriptions


Code1234

APPENDIX

Sample Pass-Through SQL QueriesThe following pages provide 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

Student Queries

Student Birth Date from RangeThis query returns students whose birth dates exist within a range of time between two dates. It will include student birth dates occurring on the specified start or end date.

Box ANo text necessary

Box BAND student.birthdate BETWEEN 'MM/DD/YYYY' AND 'MM/DD/YYYY'

Students without GuardiansThis query returns students who do not have a guardian relationship assigned.

Box ALEFT OUTER JOIN RelatedPair rp ON rp.personID1 = student.personID AND rp.guardian = 1

Box BAND rp.personID2 IS NULL

Students with Multiple GuardiansThis query returns students who have two or more guardian relationships assigned.

Box AINNER JOIN RelatedPair rp ON rp.personID1 = student.personIDAND rp.guardian = 1

Box BGROUP BY student.personID, student.lastName, student.firstName, student.grade, student.studentNumberHAVING COUNT(rp.personID1) > 2


APPENDIX

Sample Pass-Through SQL QueriesStudent Queries

Students who did not make a course request for a certain type of course by Course GroupThis query returns a list of students who did not make a course request for a type of course, like Language Arts or Math. Modify the name of the course group (in the query as Language Arts) to meet the needs of your district. Ensure the query is returning the credit name, not the department name.

Box Aleft join (Request rjoin Course crs ON crs.courseID = r.courseIDjoin GradingTaskCredit gtc ON gtc.courseID = crs.courseIDjoin CurriculumStandard cs ON cs.standardID = gtc.standardID And cs.name = 'Language Arts') ON r.personID = student.personID And student.calendarID = crs.calendarID

Box BAnd crs.number IS NULL

Students who did not make a course request for a certain type of course by DepartmentThis query returns a list of students who did not make a course request for a type of course, like Language Arts or Math. Modify the name of the Department (in the query as Language Arts) to meet the needs of your district.

Box Aleft join (Request rjoin Course crs ON crs.courseID = r.courseIDjoin Department d ON d.departmentID = crs.departmentID And d.name = 'Language Arts') ON r.personID = student.personIDAnd student.calendarID = crs.calendarID

Box BAnd crs.number IS NULL


APPENDIX


Students who did not get a Requested CourseThis query returns students who did not get scheduled into a course they requested. The course number must be specified.

Box AINNER JOIN Request r ON r.personID = student.personIDAND r.calendarID = student.calendarIDINNER JOIN Course c ON c.courseID = r.courseID AND c.number = '0355'INNER JOIN Section s ON s.courseID = c.courseIDINNER JOIN Trial tr ON tr.trialID = s.trialIDAND tr.active = 1LEFT OUTER JOIN Roster ro ON ro.personID = r.personIDAND ro.sectionID = s.sectionID

Box BAND ro.personID IS NULL

Students with Fewer Credits than Desired for GradeThis query returns students enrolled in a certain grade who have less than a specified amount of credits. This example will return all twelfth-graders who have fewer than 20 credits.

Box AINNER JOIN v_TranscriptDetail td ON td.personID = student.personID

Box BAND student.grade = '12'GROUP BY student.personID, student.lastname, student.firstname, student.grade, student.studentnumberHAVING(SUM(td.creditsearned))< 20


APPENDIX


Students on the A Honor RollThis query returns students who are considered "A" honor-roll students. This query may require user modifications to fit specific needs.

Box AINNER JOIN TermSchedule ts on ts.structureID = student.structureIDINNER JOIN Term t on t.termScheduleID = ts.termScheduleID and t.seq = 1INNER JOIN GradingTask k on k.name = 'Quarter Grade'LEFT OUTER JOIN Term tx on tx.termScheduleID = t.termScheduleID and tx.seq = 1INNER JOIN GradingScore g on g.calendarID = student.calendarID and g.termID = t.termID and g.taskID = k.taskIDand (g.score like 'A%')AND g.personID = student.personIDLEFT OUTER JOIN GradingScore gx on gx.calendarID = g.calendarID and gx.termID = g.termID and gx.taskID = g.taskIDand gx.personID = g.personIDAND NOT (gx.score like 'A%')

Box BAND gx.scoreID IS NULL

Students on the A/B Honor RollThis query returns students who are considered "A" or "B" honor-roll students. This query may require user modifications to fit specific needs.

Box AINNER JOIN TermSchedule ts on ts.structureID = student.structureIDINNER JOIN Term t on t.termScheduleID = ts.termScheduleID and t.seq = 1INNER JOIN GradingTask k on k.name = 'Nine Week' LEFT OUTER JOIN Term tx on tx.termScheduleID = t.termScheduleID and tx.seq = 1INNER JOIN GradingScore g on g.calendarID = student.calendarID and g.termID = t.termID and g.taskID = k.taskID and(g.score like 'A%' OR g.score like 'B%')AND g.personID = student.personIDLEFT OUTER JOIN GradingScore gx on gx.calendarID = g.calendarID and gx.termID = g.termID and gx.taskID = g.taskID andgx.personID = g.personIDAND NOT (gx.score like 'A%' OR gx.score like 'B%')

Box BAND gx.scoreID IS NULL


APPENDIX


Students on the A/B Honor Roll Excluding Students with only A'sThis query returns students who are considered "A" or "B" honor-roll students, but excludes those who received only A's. This query may require user modifications to fit specific needs.

Box AINNER JOIN TermSchedule ts on ts.structureID = student.structureID INNER JOIN Term t on t.termScheduleID = ts.termScheduleID and t.seq = 1INNER JOIN GradingTask k on k.name = 'Quarter'LEFT OUTER JOIN Term tx on tx.termScheduleID = t.termScheduleID and tx.seq = 1 INNER JOIN GradingScore gon g.calendarID = student.calendarIDand g.termID = t.termID and g.taskID = k.taskID and (g.score like 'A%' OR g.score like 'B%')AND g.personID = student.personID LEFT OUTER JOIN GradingScore gx on gx.calendarID = g.calendarID and gx.termID = g.termIDand gx.taskID = g.taskID and gx.personID = g.personID AND NOT (gx.score like 'A%' OR gx.score like 'B%')INNER JOIN GradingScore g3 ON g3.calendarID = g.calendarIDAND g3.termID = g.termIDAND g3.taskID = g.taskID AND g3.personID = g.personIDAND(g3.score LIKE 'B%')

Box BAND gx.personID IS NULL

Course/Section Queries

Courses without Assigned Grading TasksThis query returns courses to which a grading task has not yet been assigned.

Box ALEFT OUTER JOIN gradingtaskcredit gtc ON gtc.courseID = course.courseIDAND gtc.calendarID = course.calendarID

Box BAND gtc.taskCreditID IS NULL


APPENDIX

Sample Pass-Through SQL QueriesCourse/Section Queries

Courses without SectionsThis query returns courses to which a section has not yet been assigned.

Box ALEFT OUTER JOIN [Section] s ON s.courseID = course.courseIDAND course.active = 1LEFT OUTER JOIN Trial tr on tr.trialID = s.trialID AND tr.active = 1

Box BAND s.sectionID IS NULL

Census/Staff Queries

Teachers with no Lesson Plans for the Current School YearThis query returns information on teachers who have not set up the Lesson Planner in the current school year.

Box AINNER JOIN Section se ON se.teacherPersonID = individual.personIDINNER JOIN Course c ON c.courseID = se.courseIDINNER JOIN Calendar cal ON cal.calendarID = c.calendarIDINNER JOIN SchoolYear sy on sy.endyear = cal.endyear and sy.active = 1LEFT OUTER JOIN LessonPlanGroup lpg ON lpg.sectionID = se.sectionIDLEFT OUTER JOIN LessonPlanGroupActivity lpga ON lpga.groupID = lpg.groupIDLEFT OUTER JOIN LessonPlanActivity act ON act.activityID = lpga.activityIDINNER JOIN activeTrial at ON at.trialID = se.trialID

Box BGROUP BY individual.personID, individual.lastName, individual.firstName HAVING SUM(COALESCE(act.activityID, 0)) = 0


pop-up windows - fcps.net · web view6/29/2017ah-40icadhoc1718.docx. ... pop-up windows from...

Documents