# 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:
1. Overall & Claim Scores from your State Assessment
2. Overall Score from multiple years of your State Assessment
3. Overall, Standard, and/or Question Group Scores from an Interim Assessment
4. Overall score from multiple interim assessments
5. Scores from a rubric with multiple categories.
* TWO data points for aggregation
* Ideas/Suggestions:
1. Current Site & Grade Level
2. Current Site & Teacher
3. Current Site & Reported Race
4. 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.](https://dl.dropbox.com/s/5uz7n0w4fu42kb8/Blank%20Template%20-%20Landscape.jrxml?dl=0)
* 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.
You should now have a report that might look something like this:
NOTE: In another tutorial, I will show you how to **customize your chart** so you can:
* Adjust the colors and fonts
* Add a title & subtitle
* Add data labels
* Etc.
**Did you know Illuminate can build your BI Tool for you?**\
[Click here to learn about Illuminate's **Report Building Service**.](https://www.illuminateed.com/resources/report-building-service/)
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://colah.gitbook.io/illuminate-bi-tool-guide/tutorials/tutorials-for-free-version-community-edition/tutorial_6_using_jasper_report_bands.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.