Tutorial 2: Using Jasper Report Bands

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.
Use report “bands” to aggregate the data by Site > Grade Level > Teacher > Ethnicity
Use Conditional Formatting to highlight important data in the report.
Use CrossTabs to provide district-wide summaries of the data

Objective

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

KEY CONCEPTS

Any element (charts, fields, etc.) in a band will “print” everytime the field you are grouping by changes.
Sorting dramatically affects the behavior of the report bands.
If your data is sorted correctly, the elements in the “site band” will print once for each site; elements in the grade level band will print once for each grade level; etc.

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 5 columns:
- Student ID
- Several numeric data points of your choice.
  - Ideas/Suggestions:
    Overall & Claim Scores from your State Assessment
    Overall Score from multiple years of your State Assessment
    Overall, Standard, and/or Question Group Scores from an Interim Assessment
    Overall score from multiple interim assessments
    Scores from a rubric with multiple categories.
- TWO data points for aggregation
  - Ideas/Suggestions:
    Current Site & Grade Level
    Current Site & Teacher
    Current Site & Reported Race
    Teacher & Reported Race

Example

Step 2 - SORT & Download Data. Create Data Adapter.

Before you download the data, you should sort the data in your custom report. This report will need multiple levels of sorting. Click the Sorting tab and sort your data.

KEY CONCEPT: Sorting is tied to aggregation. Sort the data the same way you want to aggregate your data. For example, if your report is going to aggregate your data first by site, then by grade level within each site, your data should be sorted first by site, then by grade level.

Download the custom report as a tab delimited txt file. (Downloading as an Excel file will result in errors.)

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 - Link Your Data Adapter to Report Starter Template

Open the Report Starter Template in Jaspersoft Studio.
- Right-click on this link and choose "Save Target As" or "Save Link As" to download the Report Starter Template.
Click File > Save As to create a copy of the template. Name your report.
Click the "Dataset" icon, select your data adapter, click "Read Fields". Click OK.

Step 4 - Create Your Groups/Bands

Scroll to the very top of your outline.
Right-click on the very top line of your outline.
Select Create Group
Name Your Group (i.e. "Site")
Select the first field/data point by which you will be aggregating your data (i.e. Current Site Name)
Click finish.

Repeat this process to create another group for the second field/data point by which you will be aggregating your data (i.e. Current Grade Level).

In my example, I am aggregating my data first by site, then by grade level. So I created two groups: one for current site name, and one for current grade level.

Delete Unnecessary Bands

Next, delete the bands we won't be using.

Select the band in your outline. Right-click on the band and choose "Delete".

Delete the following bands:
- Page Header
- Column Header
- Detail
- Column Footer
Delete the footer bands for both groups you created, leaving the header bands for your groups.
- Be sure to select "Delete" -- not "Delete Group"
Your canvas should now look something 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.
- All numeric fields should be changed to java.lang.Double (Class is case sensitive.)

Step 6 - Create Your Variables

In order to calculate the average of your data point by site > grade level > teacher > reported race, we will need to create multiple variables.

In your Outline, right/control click on “Variables” and choose Create a New Variable.
In the Properties tab (lower right-hand corner by default),
- Rename the variable to “[Your Data Point] Average by Site”
- Change the class of the variable from java.lang.Integer to java.lang.Double (the average will be a decimal number).
- Select Average as your Calculation
- Click on the Edit icon (Pencil Icon) to bring up the Expression Editor.
- Double Click on the field you want to average. Click Finish.

Duplicate variable for each data point.

Copy & paste the variable for each data point.
- Change the name of the variable
- Change the expression to the next data point

Duplicate the entire set of variables for your second band.

You will create two nearly identical sets of variables, the only difference being: 1. The name of the variable 1. The “Reset Type” of the variable.
Save yourself a lot of time by copying and pasting the entire set of variables from your outline. Then, all you have to do is change the name and the reset type, and you should be able to get this done quickly.

Step 7 - Generate a Bar Chart for each Report Band

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 Bar Chart and click Next >.

Create Your Series

Click on the “…” icon next to the “SERIES 1” field. You will need to have one Series for each data point you are averaging. If you have only one data point, you will have only one series.

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. In this report, the “Category” is simply the Site Name.

Click on the “…” next to the “Category” field. Double click on the field Current Site Name.

Repeat for each series that you created. (Switch between your series using the drop-down menu.)

Set the Value for each Series

The “value” is the data you want the bar to represent.

Click on the “…” next to the “Value” field.
Select the Average by Site variable that you created.
If you have multiple data points, do this for each series in your chart.

Set the “Reset On” Field & Evaluation Time

Next, you need to set the “Reset On” field to “Site Name”. Click FINISH.
- In most cases, the “Reset On” field should match the band in which you have placed your chart. (i.e. If the chart is in the “Site Name” band, the “Rest On” field should be set to “Site Name”.)

Finally, you need to set the “Evaluation Time” for your chart.
- Click on the chart on your report canvas.
- Go to the Properties Tab
- Select the Chart Tab within the Properties Tab
- Set the Evaluation Time to Site Name.

NOTE: Evaluation Time can be a confusing concept at first, but just remember this simple guideline:

In most cases, the “Evaluation Time” should match the band in which you have placed your chart. (i.e. If the chart is in the “Site Name” band, the “Evaluation Time” field should be set to “Site Name” — just like the “Reset On” field.)

As you get familiar with JasperSoft and start playing around with the settings, you will see how these “Reset On” and “Evaluation Time” fields can be very useful.

Step 8 - Resize your chart and Preview Your Report

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.

You should now see a separate chart for each site in your report, displaying the average of each data point for that site.

REPEAT the Same Process for the Grade Level, Teacher, and Reported Race Bands

Copy, paste, and edit to save yourself loads of time! You can copy and paste a chart from one band to the other, and then make the necessary adjustments.
You will now repeat the exact same process for the Grade Level, Teacher, and Reported Race Bands.
The value in your chart should match the band.
- Instead of “Average by Site”, you will select the Average by Grade Level/Teacher/Reported Race.
The Rest On in your chart should match the band.
- Instead of “Reset On Current Site Name”, you will select Reset On Grade Level/Teacher/Reported Race.
The Evaluation Time of your chart should match the band.
- Instead of “Evaluation Time: Current Site Name”, you will select Evaluation Time: Grade Level/Teacher/Reported Race.

Step 9 - Design!

Make your report look pretty by adding labels, customizing your chart, etc. to the report.
Use conditional formatting to highlight important data.
- Note: I will add more detail about using conditional formatting later.