How-To Document

Build a Chart with a Drill Down to a Report

Date: 06-Nov-2003
Based on Oracle Application Express (formerly called HTML DB) version 1.5

After completing this How-To, you should be able to understand:

• How to Create an SVG chart
• How to enable an SVG chart to drill down to a report

Table of Contents

• Introduction
• Software Requirements
• Create a SVG Chart
• Create a report
• Change chart drill down to a report
• Additional Resources

Introduction

HTML DB supports charts using Scalable Vector Graphics (SVG). SVG is a new XML-based language for vector based graphics from the World Wide Web Consortium (W3C). (For more information about SVG, please visit the W3C SVG Web Site). HTML DB generates SVG charts dynamically based on a SQL Query. In this How To, we will describe how you can use a table supplied with the sample application to generate an SVG chart and enable that chart to drill down to a report.

Software Requirements

• Oracle HTML DB
• Internet Explorer 5.5+ *
• Adobe SVG Viewer

* SVG Charts are currently only supported by the Adobe SVG plugin available for Internet Explorer.

Create a SVG Chart

We will extend the Sample Application that is included when a new workspace is provisioned. To create an SVG chart, we will use the SVG Chart wizard.

• In 'Sample Application'
• Navigate to the Application Builder
• Click on the Wizards tab
• Select the SVG Chart wizard
• Enter 'Ordered Product Categories' as page and region name
• Select 'Do not use tabs'
• Select Pie as chart type
• Enter following chart query:

  select  null link,
          p.category label,
          count(p.category) value
  from    demo_product_info p,
          demo_order_items i
  where   p.product_id = i.product_id
  group by null, p.category
  order by p.category
  

* HTML DB uses the following SQL query syntax to generate an SVG chart:

SELECT LINK, LABEL, VALUE
FROM   ...

Where LINK is a URL, LABEL is the text that displays in the legend, and VALUE is the numeric column that defines the chart element.

• Click Finish to create the chart
• When you run the page, you should see a pie chart that looks like the one in Figure 1.

Figure 1 - Ordered Product Categories SVG Chart

Create a report

To create a report, please go through the steps outlined in the "How To" Dynamic Query Report. We will use this page to link from SVG chart.

Change chart drill down to a report

Now that the report is created, we will change the chart query to make the SVG chart drill down to the report. You should have created a dynamic query report on page 600 using the Dynamic Query Report How To. It generates the report dynamically based on the value of an item called P600_SHOW, allowing the user to constrain the report to show rows for individual product categories. Rather than having the user select a category using a select list, we will modify the pie chart to link to the report and set the category to display. To change the chart query to incorporate the assignment of a category value to the P600_SHOW item, follow these steps:

• Click on the Chart link for the pie chart you created

  Figure2 - Edit SVG Chart

  

• Change the query as follows to include a link to page 600:

• select  'f?p='|| :APP_ID ||':600:'|| :APP_SESSION ||'::::P600_SHOW:'||p.category link,
          p.category,
          count(p.category) 
  from demo_product_info p,
       demo_order_items i
  where p.product_id = i.product_id
  group by null, p.category
  order by p.category
                 
  

• Click Apply Changes button to save

After adding a link to the chart query, you should be able to drill to the detail report by clicking on a slice in the pie chart, or on the corresponding category in the chart legend.

Additional Resources

SVG Sites:

• Adobe SVG Web Site
• W3C SVG Web Site

Discuss this how-to in the OTN HTML DB Forum.