This lesson will guide you through adding a Window Column to a Custom Report. Think of it as a window into your data, without having to make a separate report.
A window column runs your report with all other columns first, then it walks through the results. It looks at the groups of records, and calculates a value for each group. This is a great option for looking at student vs. results of the whole group, like all of third grade, for example.
YES! When using Window Columns they do not require “smashing” down the data. You can get aggregation while maintaining the integrity of your row-based data set. However, it is similar to grouping in that it does allow aggregation over a complex data set.
Where to Start
- Once in a Custom Report, select the Advanced tab.
- Select Add a Window Column.
Window Column Options
The column display has a series of options and depending on what you are trying to achieve will determine what you want to choose.
Enter a name for the column of data relevant to what you want to see. For example, Fall Class Average or Percent of Total Students. This can be edited later if you need to revise
There are a variety of options to Display your data. Depending on the Display selected, other selections will or will not be allowed/necessary to build the column. The display is the end of result of what you are hoping to see with existing data already in the report:
What You Are Looking For: You want a total count of students within a program.
Choosing the display value of Count is creating a column of data that counts or creates a total number based on that.
- Select the display value of Count.
- The Of value will be defaulted is All Values.
- Select the For Each value which is "of which data to display, for each student or grade level or site, etc."
All other options are not required and by default, set up for success when selecting Count display value.
Scenario: I want to assign a row number to each value in my report, similar to how MSExcel or Spreadsheets provide a row number for each record.
What You Are Looking For: You want an assigned row number to each record, by group.
Choosing the display value of Row Number is creating a column of data that counts or creates a total number based on that.
- Select the display value of Row Number.
- The Of value will be defaulted is All Values.
- Select the For Each value which is "of which data to display, for each student or grade level or site, etc."
- Ordering By will be the unique data set on how row numbers are applied.
All other options are not required and by default, set up for success when selecting Row Number display value.
Scenario: I want to see a list of students with program start dates in a ranking order by start date.
What You Are Looking For: You want a total count of students within a program by start date.
Choosing the display value of Rank is creating a column of data that shows a relationship between a set of items for any two items, the first is either 'ranked higher than', 'ranked lower than' or 'ranked equal to' the second.
- Select the display value of Rank.
- The Of value will be defaulted is All Values.
- Select the For Each value which is "of which data to display, for each student or grade level or site, etc."
- Select the Ordering By value which is "of which data to display, for each student, ordered by Program Start Date"
All other options are not required and by default, set up for success when selecting Rank display.
Scenario: A student has participated in multiple programs throughout the course of a year. I need to know how many programs the student has been in and in which program did he or she first participate.
What You Are Looking For: The first program a student is in of many possible programs.
Choosing the Display value of First Value is creating a column of data that is originating from the first value of multiple records.
- Select the display value of First Value.
- Select the Of value which is "of which data to display from"
- Select the For Each value which is "of which data to display, for each student or grade level or site, etc."
- Select the Ordering By value which is "of which data to display, for each student, ordered by Program Start Date
All other options are not required and by default, set up for success when selecting First Value display.
Scenario: A student has participated in multiple programs throughout the course of a year. I need to know how many programs the student has been in and in which program did he or she last participated in.
What You Are Looking For: The most recently ended program a student was in of many possible programs.
Choosing the Display Value of Last Value is creating a column of data that is originating from the last value of multiple records.
- Select the display value of Last Value.
- Select the Of value which is "of which data to display from"
- Select the For Each value which is "of which data to display, for each student or grade level or site, etc."
- Select the Ordering By value which is "of which data to display, for each student, ordered by Program Start Date
All other options are not required and by default, set up for success when selecting First Value display.
Scenario: We need a list of student scores in comparison to overall grade level assessment scores by a single or multiple assessments.
What You Are Looking For: To see a student's individual score alongside the grade level average on a benchmark assessment(s).
Choosing the Display Value of Average is creating a column of data that is averaging the for each data in comparison to the individual student's data.
- Select the display value of Average.
- Select the Of value which is "of which data to display from"
- Select the For Each value which is "of which data to display, for each student or grade level or site, etc."
All other options are not required and by default, set up for success when selecting First Value display.
When selecting Average or Percentage in a Window Function, there is a new option to set the rounding. Currently, the default is to round to 2 decimal places with ability to change it to the same options as shown in the current Manage Columns page.
Scenario: I want to see a point differential or how close a student is to the minimum passing score.
What You Are Looking For: I want to see a list of students in comparison to the minimum value of passing, or whatever I choose as a minimum value.
Choosing the display value of Minimum Value is creating a column of data that will display the minimum value of a data set compared to that of a unique value or in this case a student.
- Select the display value of Minimum Value.
- Select the Of value which is "of which data to display from"
- Select the For Each value which is "of which data to display, for each student or grade level or site, etc."
All other options are not required and by default, set up for success when selecting Minimum Value. If grayed out, that means no other selections are needed to complete this Window Column.
Scenario: I want to see a point differential or how close a student is to the maximum passing score.
What You Are Looking For: I want to see a list of students in comparison to the maximum value or score, or whatever I choose as a maximum value.
Choosing the display value of Maximum Value is creating a column of data that will display the minimum value of a data set compared to that of a unique value or in this case a student.
- Select the display value of Maximum Value.
- Select the Of value which is "of which data to display from"
- Select the For Each value which is "of which data to display, for each student or grade level or site, etc."
All other options are not required and by default, set up for success when selecting Maximum Value. If grayed out, that means no other selections are needed to complete this Window Column.
What You Are Looking For: You want a calculated sum of data for each group or additional data set in the report.
Choosing the display value of Sum is creating a column of data that will display a calculated sum of values for each data value selected. This will add data together. In this case, we asked that the sum of all performance levels were added together, which we can see that the total for 5th grade was 833 of a possible 1380 performance level points across 345 students who took the test.
- Select the display value of Sum.
- Select the Of value which is "of which data to display from"
- Select the For Each value which is "of which data to display, for each student or grade level or site, etc."
All other options are not required and by default, set up for success when selecting Sum. If grayed out, that means no other selections are needed to complete this Window Column.
Scenario: List of all students' gender and race, and provide a percentage breakdown - in one report!
What You Are Looking For: The percentage breakdown of students in comparison to their gender and race.
Choosing the display value of Percentage of Total is creating a column of data that will calculate the percentage of students for each gender, and each reported race value.
- Select the display value of Percentage of Total.
- Select the Of value which is "of which data to display from"
All other options are not required and by default, set up for success when selecting Percentage of Total. If grayed out, that means no other selections are needed to complete this Window Column.
When selecting Average or Percentage in a Window Function, there is a new option to set the rounding. Currently, the default is to round to 2 decimal places with ability to change it to the same options as shown in the current Manage Columns page.
While each display selected has a series of defaulted and recommended options, you can most of the time apply additional features with your Window Column:
- Ordering By to display a particular order in the value or column you've created
- Reverse Sort is defaulted to No but you can change to Yes
- Then Ordering By creates a secondary option on how the column is ordered
- Reverse Sort is defaulted to No on the secondary order but you can change to Yes
- Format available for display values that require Rounding can be automatically set
You can also apply Rounding to numerical values or columns in the Manage Columns page. But now, you don't have to!
Always save your created column. It will instantly add to your report. To see your report select View Report.
Select View to see the new Window Column on the report. It is automatically added as the last column of the report.
- Go to Manage Columns.
- Select the blue Window Function link to access the interface shown when creating the column. This allows users to edit the column. The Name of the column can be edited from Manage Columns.
- Use the Checkbox under the Delete column to remove the Window Column entirely.
Next Steps
Now that you've made some Window Columns, start to add some color and visualize your numeric values with Using Performance Bands in Custom Reports