Classic View: Ad Hoc Reporting > Data Analysis > Pivot Designer
Search Term: Pivot Designer
The Pivot Designer tool allows users to analyze, interpret and visually present crossed-referenced data in easy to understand charts and graphs.
For example, a list of students who have a specific race/ethnicity can be selected and cross-referenced with those students' attendance records, behavior incidents and semester grades. This data can then be visually produced on a chart, facilitating comparison and analysis.
This tool 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, and then display the data in a new table or chart.
This tool is designed for Administrators and select power users. Users working within or viewing a Pivot Designer report are able to see data related to tools for which they may not have tool rights to access (with the exception of FRAM). Calendar rights are respected as pivots will not display data tied to calendars for which a user does not have rights to access.
We recommend saving pivots to specific User Groups or as an Outline Link to control user access.
Create a New Pivot
The following sections will walk you through the process of creating a new pivot:
Step 1. Select a Pivot Type
The first step is selecting the type of pivot you would like to make. Use the table below to help understand and decide which pivot type is best for you.
Pivot Type | Description |
|---|---|
Student Counts | Bases information on total number of students enrolled.
|
Attendance Mark Counts | Bases information on students' attendance records.
|
Behavior Event Counts | Bases information on students' behavior records.
|
Grade Mark Counts | Bases information on the Total Number of Students Enrolled + GPA + Credits Earned.
|
Transcript Mark Counts | Bases information on students' transcripts.
|
Special Education Analysis | Bases information on students who have a special education record.
|
Step 2. Determine Pivot Information, Dimensions, Measures, and Filters
Once a pivot type is selected, you must now determine pivot information, any filters or measures to apply, and what dimensions will be used for reporting data. Use the table below for help in understanding each section.
Pivot Information Pivot Information fields are used for identification purposes so you can more easily locate and reuse this pivot in the future. A Pivot Name is required for all pivots (unless the pivot will be used immediately and not saved). The Created Date indicates when the pivot was first created. | ||||||||||||||||||
Student Filters
Users can select an Ad hoc Filter that contains specific students who will make up the population reported in the pivot. If an Effective Date is entered, only students who are actively enrolled as of this date are included in the pivot. Marking the Active Only checkbox will force the pivot to only return students who are enrolled on the current date (today). | ||||||||||||||||||
Measures
Select the desired measures for the pivot table. These options vary based on the type of pivot selected. The following is a list of available Measures and their corresponding Pivot Types.
| ||||||||||||||||||
Dimensions
Dimensions allow users to designate specific data elements that are pulled into the pivot table. These data elements are fields found throughout Campus. You must select at least two data elements in order to generate a pivot table. Select data elements by marking the checkbox next to the desired field. For detailed information about each dimension and data element, see the Understanding Dimensions section below. | ||||||||||||||||||
Organized to
This field indicates which user groups are allowed access to the pivot from the Saved Pivots list. This tool is designed for Administrators and select power users. Users working within or viewing a Pivot Designer report are able to see data related to tools for which they may not have tool rights to access (with the exception of FRAM). Calendar rights are respected as pivots will not display data tied to calendars for which a user does not have rights to access. We recommend saving pivots to specific User Groups or as an Outline Link to control user access. Marking the Read checkbox means users in this user group can only generate and view the pivot. Marking the Write checkbox means users in this user group can edit and view the pivot. | ||||||||||||||||||
Data Source
The Data Source determines which database is used when pulling pivot information. This option is only available to customers who have Data Warehouse Settings properly configured. | ||||||||||||||||||
Use Default Layout Marking this checkbox will display the pivot in the default format, ignoring any and all saved modifications made within the Pivot Designer tool. |
Understanding Dimensions
Dimensions allow users to designate specific data elements that are pulled into the pivot table. These data elements are fields found throughout Campus. You must select at least two data elements in order to generate a pivot table.
- If All Years and All Schools are selected in the Campus toolbar, the School Name and School Year elements must be selected in order for the pivot to display correct data.
- Only dimensions available to all districts are listed. State-specific or Reporting Entity-specific fields are not included.
- Student enrollment pivots within the Pivot Designer tool will report data from historical LEP fields and not from new LEP fields.
Data Analysis uses database views to more efficiently pull data into pivots.
The following section describes all available dimensions within Pivot Designer and the mapping and definition of each data element within each dimension.
Student
View: cube_student
Enrollment and State Elements
View: cube_enrollment
Student Schedule
View: cube_roster
Attendance
View: cube_attendance
Behavior
View: cube_behavior
Grades
View: cube_grades
Transcript
View: cube_transcript
Health Visits
View: v_HealthVisitDetail
Medication
View: v_MedicationDetail
Special Education
View: cube_sped
Blended Learning
View: cube_blendedLearning
Food Service
View: cube_fram
Standardized Tests
View: This view is built dynamically based on the contents of the Test and TestScore tables.
Understand Pivots
To filter a dimension, click the gear icon 
next to the dimension header (Image 4).
From here you can filter dimension data by label or value as well as modify the dimension sort order. For example in the image below, filtering the Age dimension by Labels and setting the filter parameters to Equal 9 results in the pivot only displaying cross-referenced data for students Age 9.
Selecting the Fields icon allows you to modify the order of the fields in the pivot, add or remove fields from the pivot table, and modify which fields are used in rows or columns (Image 6).
You can also modify or insert specific calculations to be used in the pivot table by clicking the Add calculated value button (Image 7). When adding calculated values, users can use aggregation key words to produce aggregates that aren't available when selecting the sigma droplist.
For example, notice in Image 7 the how the first option is Age (Count). The aggregation function is Count. Clicking the Sigma displays Count and Distinct Count only, however, users can modify the aggregation function when creating a calculated value using these additional keywords:
- Count
- Distinct Count
- % of Grand Total (Percent)
- % of Column (Percent of Column)
- % of Row (Percent of Row)
In the example below, the personID field was added and applied to the pivot table. This additional field created a second dimension tied to Federal Race Ethnicity (Image 8).
Adding this additional dimension means Federal Race Ethnicity values can now be drilled down to see the personIDs of all students reporting for this Race Ethnicity value (Image 9)
You can further drill down on pivot table data by double-clicking on a specific cell. This will display all the cross-referenced information contained within the cell. For example in the image below (Image 10), this cell is reporting data for a 10 year old Asian student with a PersonID of 12300.
You can sort each row in ascending or descending order by selecting the arrow icon 
next to the row name (Image 11).
Image 12: Right-Clicking a Cell
Create Charts from Pivot Data
Pivot data can be visually displayed in a number of charts. To create a chart, click the Charts icon and select a chart type.
Column
The Column chart displays pivot data in vertical color-coded columns. Colors are defined in the legend at the bottom of the screen. To filter displayed data, select the gear icon next to a data element.
Image 14: Column Example
Bar
The Bar chart displays pivot data horizontally in color-coded columns. Bar charts work well for tracking changes over time.
Image 15: Bar Example
Line
The Line graph displays pivot data using color-coded dots and lines. Line graphs are useful for reviewing changes over short and long periods of time, noticing spikes in data, and noticing trends.
Image 16: Line Graph Example
Scatter
Scatter charts display pivot data in color and graphically-unique points. Scatter charts work best when comparing large numbers of data points without regard to time. For example, you might use a scatter chart to analyze the relationship between two variables such as a person's height and weight.
Image 17: Scatter Chart Example
Pie
Pie charts display pivot data in a color-coded circle, indicating how much each variable makes up a part of the whole. Pie charts are useful for understanding the size or impact one variable has compared to the others or to see how a variable has shrunk or grown over time.
Image 18: Pie Chart Example
Bar Stack
A Bar Stack displays pivot data in color-coded bars representing how much each variable makes up a part of the whole for another variable. Bar stacks are useful for representing multiple types of data within a single bar.
Image 19: Bar Stack Example
Bar Line
A Bar Line displays pivot data in vertical color-coded columns. Bar lines are useful for determining trends and the trajectory of data over time.
Image 20: Bar Line Example
Format and Layout Options
Format options allow you to modify how cells represent data within the pivot table. To access format options, click the Format button and select one of the following options:
- Format cells - This editor allows you to control how text within the cell is aligned, what value is reported in the cells, how decimals are used, the current symbol used, any default null value you want the table to display, and whether or not you want data displayed as a percentage.
- Conditional formatting - This editor allows you to set cell value thresholds as to whether or not the data is reported for each cell (less than or greater than a certain value) as well as modify the cell text size and font.
The Options menu gives you layout options for how you would like the table to be displayed (Layout), how grand totals are display in the table (Grade Totals), and how subtotals should be displayed (Subtotals) (Image 22).
Export and Share Pivots
Pivots can be exported to a number of different formats by clicking the Export icon and selecting an option (Image 23).
Pivots contain all data from the Effective Date entered on the Pivot Designer editor to the current date. Because of this, users are unable to do historical comparative analysis of data. Users are highly encouraged to export data periodically in order to facilitate comparative analysis.
You can also share your pivot with other Campus users by clicking the Share icon and selecting one of the following options:
Tiny URL - Produces a short URL which is useful for texting or emailing to other Campus users.
This option will not preserve any formatting or filtering done to the pivot table. Users will receive the data in the default pivot table format.
- Current View URL - Preserves all existing formatting or filtering done to the pivot table however, this URL will be much longer than the URL provided via the Tiny URL option.
Use caution when sharing a pivot with other users. Users working within or viewing a Pivot Designer report are able to see data related to tools for which they may not have tool rights to access (with the exception of FRAM). Calendar rights are respected as pivots will not display data tied to calendars for which a user does not have rights to access.
We recommend saving pivots to specific User Groups or as an Outline Link to control user access.
Users attempting to access a pivot table via a shared URL will need to first be logged into Campus in order to access the tool and have proper calendar rights in order to see reported data.
View, Edit and Delete Pivots
To view an existing pivot, select the pivot from the Saved Pivots window and click the View button (Image 25). You will be redirected to the Pivot Designer tool, displaying the pivot and its corresponding data.
To edit an existing pivot, select the pivot from the Saved Pivot window and click the Edit button (Image 26). You will be directed to the Pivot Designer editor where modifications can be made to existing dimensions and field data. To save modified pivot field data select the Save icon. To view modified pivot data select the Display Pivot button.
To delete an existing pivot, select the pivot from the Saved Pivots window and click the Delete button (Image 27). You will receive a warning message. Select OK to delete the pivot or select Cancel to cancel the deletion process.
