BI Tool Cheat Sheet

Helpful Links:

• [Link to download JasperSoft Studio] (http://community.jaspersoft.com/project/jaspersoft-studio/releases)

• [JasperSoft Studio User Guide] (http://community-static.jaspersoft.com/sites/default/files/docs/jaspersoft-studio-user-guide.pdf)

  Before You Start

• Write a clear and detailed description of the report you are going to create. It is much more difficult and time consuming to go backwards and make changes/additions after the fact.

• Sketch the report you envision before you begin any work in Illuminate. Do not try to design your report in JasperSoft Studio. Know what you want before you start working in JSS.

  Getting Started

• Step 1: Create a custom report in Illuminate.

  • Do as much work as possible in the Illuminate custom report.

    • Grouping, Calculations, Case Statements, Filters, Advanced Filters, Window Columns, Translations

  • Name the columns in your custom report clearly and strategically.

• Step 2: Sort your custom report!

  • The sorting in your custom report must match your grouping in JasperSoft Studio.

  • If the custom report is not sorted correctly, your Jasper report will produce unexpected results.

• Step 3: Download your custom report.

  • Always download as a tab-delimited txt file.

  • After download, name the txt file appropriately. (Default name is always data.txt.)

• Step 4: Create your Data Adapter in JasperSoft.

  • Select “CSV File” as your Data Adapter type.

  • Don’t forget to change the “Separator” from “Comma” to “Tab.”

  • Check the “Skip the first line” box.

    • Helpful Hint: Occasionally, this box will magically un-check itself. If you are getting unexpected errors when trying to preview your report, verify that this box is checked.

• Step 5: Create a Jasper Report

  • File > New > Jasper Report

  • Click “Next” (not “Finish”) and go through the process of setting up your report.

  • Select Blank Letter or Blank Letter Landscape

  • Select the Data Adapter you created in Step 4

• Step 6: Set the appropriate “Class” for all non-text fields

  • Expand the “Fields” section in the “Outline.” Select the field. Find the “Properties” tab, and change the “Class” field.

  • Numeric fields should all be changed from java.lang.String to java.lang.Integer or java.lang.Double (This IS case sensitive.)

  • Date fields should be changed to java.util.Date

  • All text fields should remain java.lang.String

• Step 7: Create your “Groups” (if necessary)

  • “Groups” allow you to aggregate data (by site, grade level, subgroup, etc.)

  • Right-click (PC) or control-click (Mac) on the report name at the very top of the “Outline” and select “Create Group”

  • Key Concept: Jaspersoft will “print” or generate all the elements in a specific band whenever the field by which you are grouping changes. For example, if you are grouping by site, the site band will print every time the site changes. This is why sorting your custom report correctly is critical.

  • If your data is sorted correctly, a “Site” band will print once for every site. A “Grade Level” band will print once for each grade level. etc.

  • “Detail” band prints once for every single record in your custom report.

    Helpful Tips

• Setting the appropriate “Evaluation Time” (found under “Properties”) for any chart and/or calculated field in your report is crucial to getting the appropriate results.

• Setting the appropriate “Reset Type” for any variable is crucial to getting the appropriate results.

• Use the built in spacing and alignment features in JasperSoft to save yourself time.

  JasperSoft Expressions - The Basics

• Plain text should always be in “ “.

  Equations

• Equals

  • Use == for numeric fields

    • Example: $F{Performance Level} == 4

  • Use .equals(“ “) for String/Text fields

    • Example: ${Performance Level Text}.equals(“Proficient”)

• Does Not Equal

  • Use != for number fields

    • Example: $F{Performance Level} != 4

  • Use !$F{Field Name}.equals(“ “) for String/Text fields

    • Example: !F{Performance Level Text.equals(“Proficient”)

• Greater Than or Equal To: Use >= (Less Than or Equal To: <=)

  Conditional Expressions (i.e. “If, Then” Statements, Case Statements):

• Syntax: [Condition] ? [Output if True] : [Output if False]

  • Examples:

    • $F{Percent Correct}>=90 ? “Mastered” : “Not Mastered”

    • $F{1st Grade ELA Nov. Quarterly-DRA}.equals("Proficient") ? 1: 0 (This expression can be used in a variable to get a count of students who are proficient.)

• Nested Conditional Expressions (You can nest conditional expressions to create more complex logic statements.)

  • Example:

    • $F{Percent Correct}>=90 ? “Exceeding Standard” :

      $F{Percent Correct}>=80 ? “Meeting Standard” :

      $F{Percent Correct}>=70 ? “Approaching Standard” :

      “Below Standard”

    • Note: The conditional expression will stop as soon as a condition is met, only moving on to the next condition if the preceding condition is false.

      “And” / “Or” Logic

• Use && for “And” logic

  • Example: $F{Grade Level}.equals(“K”) && $F{Scale Score}>=220)

  • Use || for “Or” logic

    • Example: $F{Performance Level Text}.equals(“Meeting Standard”) || $F{Performance Level Text}.equals(“Exceeding Standard”)

    • The “pipe” character (“ | “) key is located above the return/enter key (hold shift)

      Installing Your Report

• Create “ILLUMINATE” Parameter

  • In the “Outline”, right/control-click on Parameters to create a new parameter.

  • Name the parameter ILLUMINATE (all caps)

  • Uncheck “Is for Prompting?”

• Enter “illuminate_custom” as the query language.

  • Click on the “dataset and query dialogue” icon, located above the top-right corner of your report canvas (icon looks like a stack of hard drives)

  • In the “language” field, enter illuminate_custom

• In Illuminate, click: Reports > Create Jasper Report (must have necessary permissions)