Adding Drill Down to a Chart

Before You Start

  • This is an advanced tutorial and assumes you already know how to create basic BI Tool reports.

  • Adding drill down functional to charts requires the Professional Edition of JasperSoft Studio.

Step 1: Create Your Top-Level Report

  • Be sure to use the most recent version of the Illuminate Blank Templates/Report Starters to build your report. The templates/report starters contain several pre-built parameters and variables that you will need to add drill down to your report.

    Please click the links below and push the "Download" button.

  • If you are adding drill down to a chart, you must use the HTML5 Charts that are only available in the Professional Edition of JasperSoft Studio.

Example

As an example, we’ll be using a simple DRA Summary Report with a bar chart showing the count of students at each DRA Level.

We are going to add drill down to this report so that users can click on the bars in the chart to reveal a list of students for each DRA level.

*This is an advanced tutorial and assumes you already know how to create basic reports like the one above.

Step 2: Create Your Drill Down Report

More often than not, the drill down report is a very simple report that simply generates a list of students.

Example

Your drill down report might look something like this:

At first, the drill down report will simply show every student in the dataset. We will add dynamic filtering in the next step.

*This is an advanced tutorial and assumes you already know how to create basic reports like the one above.

Step 3: Add the Necessary Parameters to Your Drill Down Report

Think of the parameters as the filters that are necessary to narrow down your list of students to just the students that are relevant to the specific part of the chart the user is drilling into.

These parameters will be dynamic. In other words, the value of these parameters will change depending upon what the user is drilling into.

We are going to pass information from the top-level report to these parameters in the drill down report.

Example

In our example, the user is going to drill into a specific bar in the summary chart, revealing a list of students who scored “Level X” on the assessment.

Thus, when the drill down report is generated, the list of students should include only students who scored “Level X”, depending on which bar the user is drilling into.

So, we need to create a Parameter that tells the report which bar (i.e. which level) the user is drilling into.

Step 3.a. Create a Parameter

  • Right-Click on the Parameter heading in your outline

  • Choose: Create a Parameter

Step 3.b. Name Your Parameter; Set Class

  • Click on the new Parameter1 that you just created.

  • Go to the Properties tab

  • Name your parameter.

    • Do not put ANY spaces in the parameter name. Feel free to use an underscore, but the parameter name can NOT have any spaces.

  • Set the appropriate class for your parameter.

Repeat Steps 3.a. and 3.b. as necessary. Some reports may require only one input control to filter the drill down report to the appropriate set of students; others may require several input controls. It depends entirely on your report.

Example

In our example, I would:

  • Name the parameter DRA1Level

  • Leave the class java.lang.String because the DRA Level is a text (aka “String”) field.

Step 3.c. Use the Parameter to Filter Report

Now, we need to tell the report to include only students that match the value of our parameter.

  • Click the Dataset Icon

  • Select the Filter Expression tab at the bottom

  • Click on the Pencil Icon to bring up the expression editor.

  • Enter your filter expression.

  • Click Finish

  • Click OK

Example

In our example, the filter expression will tell the report to show only students whose DRA1 Level matches the DRA1 Level Parameter. Remember: The Parameter is what tells the report which bar in the chart the user is drilling into.

Step 3.d. Test Your Filter Expression

To make sure your filter expression is working:

  • Preview your report

  • You will be prompted to enter a value for your parameter

  • Enter a value for your parameter

  • Click the Green Play Button

Example

Step 3.e. Create ILLUMINATE Parameter

If you started your drill down report using our Blank Template/ Report Starter, you can skip this step. The ILLUMINATE parameter will already be there, and the query language will already be set to “illuminate_custom”.

If you did not use the Blank Template/Report Starter to create your report, then you must create the ILLUMINATE parameter.

  • Right-click on Parameters in your outline.

  • Select: Create a Parameter

  • Rename the parameter to ILLUMINATE (must be all caps)

  • UN-check “Is for Prompting”

If you did not use the Blank Template/Report Starter to create your report, then you must set the query language to “illuminate_custom”.

  • Click the Dataset Icon

  • In the Language field, type in: illuminate_custom (all lowercase)

Step 4: Install Drill Down Report; Get Report URI

If your drill down report is working, with the Parameter Filter, you are now ready to install your drill down report.

Install the report just like you would any other BI Tool report.

After the report has been installed:

  • Search for your drill down report on the "List Reports" page (Tip: BI Tool Reports are always "Type: Prebuilt" and "Author: District")

  • Open the drill down report

  • Click the "Edit" button

  • Copy the Report URI

  • In JasperSoft Studio, go to your top level report (not the drill down report)

  • In the Outline, under Parameters, locate the Drill Down Report URI parameter.

  • Paste the Report URI into the "Default Value Expression"

    • Make sure the URI is surrounded by quotation marks. Example: "/organizations/demo/Reports/DistrictCreated/DraDrillDownReport"

Note: If you do not see the "Drill Down Report URI" Parameter in your report, you do not have the latest version of the blank templates/report starters.

You can download them by following the links below. Please click the link and push the "Download" button.

Step 5: Add drill down to your top-level report

Now, it’s time to go back to your top-level report and add the drill-down functionality.

Step 5.a. Edit the “Drill Down Parameters” Variable

  • Go to the Variables section of your Outline

  • Find the Drill Down Parameters variable

  • Go to the Properties tab

  • Click the Pencil Icon next to the Expression field

By default, the Drill Down Parameters variable will have this expression:

"title: 'Report Drill Down',"+
"parameter_name:'"+"INSERT FIELD HERE"+"',"

You’ll need to adjust the default expression to make the drilldown work:

  • The "title" can be whatever you want. You can change Report Drill Down to anything you would like.

    • The title will appear at the very top of the drill down window when a user drills into the chart.

  • The second (and subsequent lines) in the expression will determine what data is passed from the main report to the drill down report.

    • Change parameter_name to the exact name of the parameter in your drill down report

      • In our example, that would be: DRA1Level

      • This must match the exact name of the parameter in your drill down report or the drill down will not work.

    • Select "INSERT FIELD NAME" including the quotation marks and double-click on the field or variable that holds the corresponding data.

      • In our example, that would be: $V{DRA 1 Performance Level}

In our example, the resulting expression would like this:

"title: 'Report Drill Down',"+
"DRA1Level:'"+$V{DRA 1 Performance Level}+"',"

This expression determines what data is passed from the main report to the drill down report so that the student list can be filtered appropriately.

Think back to Step 3 of this tutorial. In Step 3, you created one or more parameters for your drill down report.

Now, you need to add one line for each parameter to the expression in the Drill Down Parameters variable. (The ILLUMINATE parameter is already taken care of for you.)

IF you created multiple parameters in your drill down report,

  • Add one line for each input control you created for your drill down report.

    • Copy the second line of the expression

    • Add a + to the end of the second line

    • Paste to create the third line of the expression

    • Change the parameter name and the Field/Variable

For example, if my drill down report had two parameters (DRA1Level and gradelevel for example) the expression would look like this:

"title: 'Report Drill Down',"+
"DRA1Level:'"+$V{DRA 1 Performance Level}+"',"+
"gradelevel:'"+$F{Current Grade Level}+"',"

Step 5.b. Create Bucket Property Value in HTML5 Chart

IMPORTANT NOTE: This step will be slightly different depending on the type of chart you are using. The directions below are for a simple bar chart. If, for example, you are adding drill down to a stacked bar chart, the process would be slightly different.

It is not possible for to document all the possible scenarios here. If you are stuck on this step, please reach out for help by emailing help@illuminateed.com.

  • Double-click on your chart.

  • Click: Chart Data > Configuration

  • Double-click on the Category Level in your chart.

  • Click: Bucket Properties

  • Click: Add

  • Name the property: drilldown

  • Click the Pencil Icon to open the expression editor

  • Select Variables

  • Double-click on Drill Down Javascript Variable

  • Click Finish

  • Click OK

  • Click OK

  • Double-click on your chart’s Measure

  • Click Edit Hyperlink

  • Check Use Hyperlink

  • Set Hyperlink Target to Self

  • Set Hyperlink Type to Reference

  • Select Use Bucket Value

  • Choose the drilldown Bucket Value

  • Click OK

*IMPORTANT: The drill down will not work locally. You must install and run the report in Illuminate to see if your drill down is working.*

Step 6: Install your top-level report

Now, please go ahead and install the report just like you would any other BI Tool report.

Run the report in Illuminate to see if the drill down is working properly.

*NOTE: Should you want to add drill down to more than one chart in a report, you will have to create duplicates of all the relevant pre-built Parameters and Variables.*

Support

Please do not hesitate to reach out for help if you get stuck. There are a lot of details and a lot of moving parts when it comes to adding drill down functionality.

Simply send an email to help@illuminateed.com requesting help from a BI Tool Specialist.

PreviousKeeping Colors Consistent in HTML5 ChartsNextPrevent Tooltip from Being Cut Off

Last updated