Tutorial 1: Categorize and Aggregate Your Data

In this tutorial, we will:

Collect a numeric data point (any numeric data point will do)
- any assessment score (SBAC, PARCC, CELDT, SRI, SMI, STAR Renaissance, any local assessment, any data in summary assessment, etc.)
- number of absences (by Session / Grading Period)
- etc.
Categorize / Organize the data into groups / levels /performance bands
Disaggregate the data (by Site / Ethnicity/ Grade Level /Program, etc.).

Objective

Using what you learn in this tutorial, you will be able to create reports like this one:

Step 1 - Create a Custom Report

Note: You should already be familiar with how to create custom reports in Illuminate. Experience with Illuminate custom reporting is a prerequisite for using the BI Tool.

The process will be slightly different depending on which data point you’re using.

Tip: If you’re unsure how to create your custom report, use SBAC/PARCC data. That will be the simplest data to work with.

Custom report should include at least 3 columns:
1. Student ID
2. Numeric Data Point of Choice (add multiple data points if you’re feeling brave!)
3. Data for Disaggregation
  If you want to disaggregate your data by site, you need to include “current site” in your custom report. If you want to disaggregate your data by grade level, you need to include “current grade level” in your custom report. etc.

You may have hidden columns for filtering.

In most cases, your custom report should have one row per student.
You may need to Group by Student to get your data.

Examples

SBAC Data Attendance Data (Number of Absences)

Step 2 - Download Data & Create Data Adapter

Before you download the data, you must sort the data in your custom report. The data must be sorted by the field by which you want to aggregate your data (i.e. Site / Grade Level / Ethnicity / Program, etc.)
Download the custom report as a tab delimited txt file.

Open JasperSoft Studio. In the top left hand of the screen, click the icon to create a new data adapter.

Choose CSV File. Click Next.
Name your data adapter. Click: File and select the data.txt file that was created when you downloaded the custom report from Illuminate. Click: Open

Next, click: Separators and select tab.
Click: Columns > Get Column names from the first row of the file.

Step 3 - Create New Jasper Report

Click: File > New > Jasper Report
Select Blank Letter or Blank Letter Landscape. Click: Next >
Name your report. Click: Next >
Select the data adapter you created in Step 2. Click: Next>
Click: ”>>” to select all your fields from the custom report. Click: Next >

On the “Group By” screen, select the field by which you want to aggregate your data. Click “>”

Click Finish.

Step 4 - Setup Your Report Canvas

In the Outline, delete the unnecessary “bands”: Page Header, Column Header, Detail, Column Footer, Page Footer (For this report, we will be using only the Title and Summary bands.)
- Right click (on PC) / Control-Click (on Mac) on the band you want to remove, and select Delete

Click and drag the bottom blue line to expand your “Summary” Band.

Your canvas should now look like this:

Step 5 - Set “Class” for Numeric Fields

By default, all fields are read as text fields. You’ll need to tell JasperSoft Studio which fields are numeric by adjusting the “Class” setting.

In your Outline, expand the “Fields” section; select your first numeric field.

In the Properties tab (lower right-hand corner by default), adjust the “Class” of your numeric fields.
- Any Decimal numbers should be changed to java.lang.Double (Class is case sensitive.)

Step 6 - Create Your Variables

In order to calculate the percentage of students in each data category/performance level, we will need to create multiple variables that give us a count of students in each data category/performance level.

In your Outline,

Right/control click on “Variables” and choose Create a New Variable.

In the Properties tab (lower right-hand corner by default),

Click on the Edit icon (Pencil Icon) to bring up the Expression Editor.

Write an “If Then” expression to count only students who meet your criteria.

“If Then” / Conditional Expressions in JasperSoft Studio (Java)

Learning how to write an “If Then” expression in JasperSoft studio is an essential building block for creating all sorts of reports. It is very similar to writing “IF()” formulas in Excel, but the syntax is different.

In Excel, an IF() formula uses the following syntax

=IF(Condition, Output if True, Output if False)

Example: =IF(B2>A2,"Growth","Decline”)

In JasperSoft Studio, uses the following syntax:

Condition ? Output if True : Output if False

Example: $F{2015 CELDT Level}>$F{2014 CELDT Level} ? “Growth” : “Decline”

Instead of “Cells”, you will be using Fields & Variables in JasperSoft.

Fields are denoted like this: $F{Field Name}
Variables are denoted like this: $V{Variable Name}

In the Expression Editor, simply double-click on the name of a field or variable, and it will automatically create the syntax for you.

Calculate a Count of Students Who Meet Your Criteria

Write an expression like this:

$F{2014-2015 SBA Summative (Final) ELA Performance Level}==1 $F{Student ID} : null`

This expression will output the student's ID # if and only if the criteria is met; a "null" if it is not met.

Note: A null value means nothing. It is not the same as a zero. A null vaue will not be counted in any "Count" or "Distinct Count" expression.

Tip: You must use a a double equals sign (==) to create equations. Refer to the BI Tool Cheat Sheet for more tips on writing JasperSoft expressions.

Click Finish
Rename your variable to an appropriate name for the variable you have just created.
Change the class from java.lang.String to java.lang.Double because our variable is numeric value, not a text value.
Select Distinct Count as your Calculation.
- The "Distinct Count" calculation is very useful if you have a report in which some or all students have multiple records. If your report has only one record per student, count and distinct count should be the same.
Set the Reset Type to the group you created when you setup your report in Step 3. This setting is critical! Without it, your data will not aggregate correctly.

Repeat the same process for each data category you’d like to create.

Examples:

Or:

Use “AND” / “OR” Logic in JasperSoft Expressions

Use “AND” / “OR” logic to identify students in a specific range, or to categorize data using multiple measures.

Use “&&” to signify AND.
Use “||” to signify OR. (Hint: Hold Shift and press the key above the Return key to create the “pipe” character.)

Example: ($F{Count of Absences for Session}>=3 && $F{Count of Absences for Session}<=5) ? 1 : 0

Use the expression above in a variable to calculate a count of students who have between 3 and 5 absences.

Example: ($F{Count of Daily Attendance in Category for Session}==1 || $F{Count of Daily Attendance in Category for Session}==2) ? 1 : 0

Use the expression above in a variable to calculate a count of students who have 1 or 2 absences.

Step 7 - Generate a Stacked Bar Chart

In your Palette (top right by default), locate the Chart element. Drag and drop the chart element into your Summary band. The “Chart Wizard” should appear.
Select Stacked Bar and click Next >.

Create Your Series

Click on the “…” icon next to the “SERIES 1” field. You will need to have a Series for each variable you created in the previous step. (These are the stacks in your bar chart.)

There will be one already there by default called “SERIES 1”. Rename the default series by clicking on the “…”. Add the rest. (All you’re doing at this point is labeling your series.)

Tip: Plain text must always be surrounded by ” “ in expressions.

Set the Category for each Series

The “category” is your x-axis. The chart will aggregate your data by the field you select as your category.

Click on the “…” next to the “Category” field. In the Expression Editor, double-click on the field by which you want to aggregate your data. Click: Finish.

Repeat for each series that you created. (Switch between your series using the drop-down menu.) The category must be the same for all your series.

Set the Value for each Series

The “value” is the data you want each series, or each stack in your bar chart, to display.

Click on the “…” next to the “Value” field.
Write an expression that will calculate the percentage of the total students in each series:

To calculate the percentage, take the:

Count of Students in Series divided by Total Count of Students at each Site. (Then multiply by 100.)

The variables you created earlier will give you a count of students for each series.
The pre-built variable Site_COUNT will give you the Total Count of Students by Site.

Your expression should look something like this:

($V{Count of SBA ELA Level 1 Students}/$V{Site_COUNT})*100

Repeat for each series in your bar chart.

Step 8 - Resize your chart and Preview Your Report

Once you set the value for each series, click Finish.
Resize the stacked bar chart icon that is now on your canvas. (It’s just an icon. It does not in any way reflect your actual data.)
Finally, hit the Preview button to see your report.