How to create a Simple Drill-Down Report

Jaspersoft Studio Version: 6.0.1 / Jaspersoft Server Version: 6.0.1

In this Example I will lead you through the steps needed to demonstrate the Drill-Down report functionality. It will use the Foodmart Database Sample which ships with the Jaspersoft Server.

Child Report

Create the Child Report first. Create a Jaspersoft Studio Report using the following SQL:

SELECT store.store_city,

          store.store_state,

          store.store_country,

          sales_fact_1998.store_sales,

          sales_fact_1998.store_cost,

          sales_fact_1998.unit_sales,

          store.store_manager

FROM sales_fact_1998

          INNER JOIN store ON

           store.store_id = sales_fact_1998.store_id

           where  store.store_country = $P{Country_P}

Pull the fields over to the blank report and tidy up the unwanted bands such that the Child Report looks like this

Parent Report

This example shows drilling down from a Bar Chart taking the Country name as the Drill-Down parameter into the above Child Report.

Create the Parent Report. Create a Jaspersoft Studio Report using the following SQL:

SELECT sales_fact_1998.store_sales,

          sales_fact_1998.store_cost,

          sales_fact_1998.unit_sales,

          store.store_country,

          store.store_state

FROM sales_fact_1998

          INNER JOIN store ON

           store.store_id = sales_fact_1998.store_id

Delete all the bands except the Summary band in this report. In the Summary band add a HTML5 Bar Chart.

In the HMTL5 properties add the Country to the Categories and Sales to the Measures.

In the Categories Level for Country, configure as below:

In the Bucket Properties (of Categories) , configure as below:

NOTE: The Bucket Property here must be the same as the Parameter in the Child Report

In the Measures Level for Sales, configure as below:

In the Advanced Properties, select the Hyperlink button and the fields will auto populate as below:

Remove the hyperlinkReference property.

Modify the hyperlinkType property as follows:

Nothing to do on the hyperlinkTarget, keep as is

Add a _report property and configure as follows:

Add a Country_P property and configure as follows:

Publish both reports to the Jasper Server

Edit the Child Report, select the Control & Resources tab and change the Display Mode to “In Page” and uncheck the “Always Prompt” option

You should now be able to goto the Parent Report and select a Bar in the Chart and drill through to the Country Detail (Child) Report.

The Parent and Child reports are attached here, but remember you will still have to do the step in blue above to get it working as expected.

When you create the Child Parameter, make sure the "Is for Prompting" dialog is checked otherwise the parameter will not passed to the server

childreport.jrxml

parentreport.jrxml