Tag: Drill Down Chart Reports

Guide for Jasper Reports Drill Down facility inside a web application

Recently one of our delivery team wanted us to demonstrate how drill down feature in Jasper Report can be used for their project.  I was going through Jasper web site and various blogs to provide the team with a easy steps to create drill down reports, but I could not find all the information in one place. I have to use contents from various blog to make it work. So I thought it would be worth to put this experience of creating drill down report on this blog.

These 11 easy steps will enable you to create drill down jasper reports using iReport tool:

  1. Open IReport, go to File –> New, in the displayed dialog box please select “Blank A4” and click on “Open this Template” button.

  2. Now in subsequent dialog box, provide the report name and directory location where the file need to be created.
  3. In the displayed Designer window, drag the “Chart” from Palette window and place it in “Title” section.

  4. Now select the Chart in the Designer and go to the Properties window to make the following changes:
    • Set “Background Alpha” to “0.5”
    • Set “Foreground Alpha” to “0.5”
    • Set “Depth Factor” to “0.2”
  5. Now click on the “Report Query” in the toolbar. In the displayed dialog box, specify the following query
    6. “select shipcountry, count(*) as order_count from orders group by shipcountry”.

    (Note: Here we are using HSQL sample db supplied with jasperreports-3.6.2 installation as our database for this   demo).

  6. Now right click on Chart in the Designer and click on Chart Data. It will open Chart Details dialog box. In the Details tab, specify the values as given in the below figure.

  7. In the Section Hyperlink sub-tab, specify following details:
    • Hyperlink target = “Self”
    • Hyperlink type = “Reference”
    • Reference tab, Hyperlink Reference Expression, specify ““DetailedReport.jsp?shipcountry=” + $F{SHIPCOUNTRY}”
    • Tooltip tab, specify “$F{SHIPCOUNTRY}”

    8. These settings will provide the drill down hyperlink facility to each slice inside the Pie chart and passes the selected country name as filter to the drill down report.

    Note: Instead of using Hyperlink type “Reference”, it is ideal to use “ReportExecution”, but in my case it is not working (I think it is due to some bug in iReport 3.6.2 community edition release).

  8. Now click on the Preview to test the report.
  9. For drill down feature to work we need to create another report which takes country name as an input filter and displays orders of the given country. This is straight forward report and guidelines for the same can be found in the jasper quick start guide.

    10. In our case we have provided following input as Report Query for detailed report.

    “select * as order_count from orders where shipcountry=$P{shipcountry}”

    Below is the sample detailed report that is created for our demo.

  10. To view this inside a web application we need to insert following code in our JSP/Servlet files.
    • <<DrillDownMainChart.jsp>>: Below given code generates the HTML contents with drill down hyperlinks for each slice in the chart. Upon clicking the hyperlink, it will take us to detailed page.
      • File reportFile = new File(application.getRealPath(“/reports/DrillDownMainChart.jasper”));
      if (!reportFile.exists()) throw new JRRuntimeException(“File DrillDownMainChart.jasper not found. The report design must be compiled first.”);

      JasperReport jasperReport = (JasperReport)JRLoader.loadObject(reportFile.getPath());

      Map parameters = new HashMap();
      JasperPrint jasperPrint =
      JasperFillManager.fillReport(jasperReport, parameters, WebappDataSource.getConnection());

      JRHtmlExporter exporter = new JRHtmlExporter();
      session.setAttribute(ImageServlet.DEFAULT_JASPER_PRINT_SESSION_ATTRIBUTE, jasperPrint);
      exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
      exporter.setParameter(JRExporterParameter.OUTPUT_WRITER, out);
      exporter.setParameter(JRHtmlExporterParameter.IMAGES_URI, “../servlets/image?image=”);
      exporter.exportReport();

    • <<DetailedReport.jsp>>: Below given code generates detailed report based on the criteria supplied criteria (in our case it “shipping country”) from the previous page.

      • File reportFile = new File(application.getRealPath(“/reports/DrillDownDetailed.jasper”));
      if (!reportFile.exists()) throw new JRRuntimeException(“File DrillDownDetailed.jasper not found. The report design must be compiled first.”);

      JasperReport jasperReport = (JasperReport)JRLoader.loadObject(reportFile.getPath());

      Map parameters = new HashMap();
      parameters.put(“shipcountry”, request.getParameter(“shipcountry”));

      parameters.put(JRParameter.IS_IGNORE_PAGINATION, Boolean.TRUE);

      JasperPrint jasperPrint =
      JasperFillManager.fillReport(jasperReport, parameters, WebappDataSource.getConnection());

      JRHtmlExporter exporter = new JRHtmlExporter();

      session.setAttribute(ImageServlet.DEFAULT_JASPER_PRINT_SESSION_ATTRIBUTE, jasperPrint);
      exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
      exporter.setParameter(JRExporterParameter.OUTPUT_WRITER, out);
      exporter.setParameter(JRHtmlExporterParameter.IMAGES_URI, “../servlets/image?image=”);

      exporter.exportReport();

  11. Sample output screens (from our demo application)

    12. <<First Screen with drill down links to each slice (in this case lets click on Germany)>>

    <<Second Screen with detailed report based on the filter (shipping country) selected in previous screen (in this case it was Germany)>>