This example demonstrates how to render a Pareto chart, based upon a declaratively created Combination chart, with a Bar series and a Line series, where individual values are represented in descending order by bars, and the cumulative total is represented by the line. The Oracle PL/SQL RATIO_TO_REPORT function is an analytic function which returns the proportion of a value over the total set of values, and is used in the calculation of the Cumulative line series of the Pareto chart.

Region Source - set the chart query at region-level, using a query similar to the following:

select product_name, 
       quantity, 
       customer, 
       product_description, 
       ratio, 
       sum (ratio) over (order by ratio desc) as running_ratio,
       'Product:    ' || product_name ||  chr(10)|| 'Quantity: ' || quantity as quantity_tooltip,
       'Product:    ' || product_name ||  chr(10)|| 'Cumulative: ' || round(sum (ratio) over (order by ratio desc)*100) ||'%' as pct_running_ratio
from 
(select a.product_name, 
        b.quantity, 
        b.customer, 
        a.product_description, 
        RATIO_TO_REPORT(b.quantity) OVER () RATIO
   from eba_demo_chart_products a, eba_demo_chart_orders b
  where a.product_id = b.product_id
    and customer = 'Deli'
  order by b.quantity)

Note the following column aliases in the above query:

• ratio - Use of the RATIO_TO_REPORT function, to calculate the proportion of the given QUANTITY value over the total set of QUANTITY values.
• running_ratio - the cumulative total
• quantity_tooltip - custom tooltip for the bar series, to display the actual value in the tooltip
• pct_running_ratio - custom tooltip for the line series, to display the cumulative value in the tooltip

Chart Type - set to Combination. Use the declarative options to define a Line chart, as you normally would.

Product Quantity Series settings - Set the following series-level attributes:

• Type - Bar
• Location - Region Source
• Label - PRODUCT_NAME
• Value - QUANTITY
• Custom Tooltip - QUANTITY_TOOLTIP
• Legend Tooltip - Customer
• Label Tooltip - Product Description

Cumulative % Series settings - Set the following series-level attributes:

• Type - Line
• Location - Region Source
• Label - PRODUCT_NAME
• Value - RUNNING_RATIO
• Custom Tooltip - PCT_RUNNING_RATIO
• Assigned To Y2 Axis - On
• Line Style - Solid
• Line Type - Automatic

Y-Axis Settings - Set the following axis-level attributes:

• Title - Product Quantity
• Format - Decimal
• Decimal Places - 0

Y2-Axis Settings - Set the following axis-level attributes:

• Title - Cumulative %
• Minimum - 0
• Maximum - 1
• Format - Percent

For more information on the Pareto chart settings, refer to the Oracle JET Cookbook Pareto example.