APEX

I'm looking for a way of generating charts against reports that use control breaks - i.e. for each report control break, I'd like to be able to generate a matching chart.

The intention is to replace an existing reporting tool that does achieve this.

The source data for a report has already been generated and is , generally, a calculated value, per hour of the working day, per calculation method, per geographic ‘region’, so in general 24 rows per method per region.

The data looks something like:

| REGION | METHOD | HOURLY_SLOT | CALCULATED_VALUE | HOUR_SORT_ORDER |
| -------- | ------ | ----------- | ---------------- | --------------- |
| Region 1 | M 1 | 05:00 | 1 | 0 |
| Region 1 | M 2 | 05:00 | 2.78 | 0 |
| Region 1 | M 3 | 05:00 | 3.59 | 0 |
| Region 1 | M 4 | 05:00 | 1.9 | 0 |
| Region 1 | M 1 | 06:00 | 1 | 1 |
| Region 1 | M 2 | 06:00 | 2.78 | 1 |
| Region 1 | M 3 | 06:00 | 3.59 | 1 |
| Region 1 | M 4 | 06:00 | 1.9 | 1 |
| Region 1 | M 1 | 07:00 | 1 | 2 |
| Region 1 | M 2 | 07:00 | 2.78 | 2 |

Dummy data can be created:

WITH regions AS
 (SELECT 'Region ' || LEVEL AS region
  FROM   dual
  CONNECT BY LEVEL <= 4),
methods AS
 (SELECT 'M ' || LEVEL AS method
        ,round(dbms_random.value(1
                                ,4)
              ,2) AS calculated_value
  FROM   dual
  CONNECT BY LEVEL <= 4),
daily_data AS
 (SELECT TO_CHAR(LEVEL
                ,'00') || ':00' AS hourly_slot
        ,CASE
           WHEN (LEVEL - 5) < 0 THEN
            24 + (LEVEL - 5)
           ELSE
            LEVEL - 5
         END AS hour_sort_order
  FROM   dual
  CONNECT BY LEVEL <= 24)
SELECT region
      ,method
      ,hourly_slot      ,calculated_value
      ,hour_sort_order
FROM   daily_data
      ,regions
      ,methods
ORDER  BY region       
         ,method
	     ,hour_sort_order

The number of regions and methods is not fixed (it may depend on the customer's installation) and there may be some missing calculated values (errors or omissions from the source system prevented calculation, etc). The working day starts at 05:00 'today' and ends at 04:00 ‘tomorrow’ - the hour_sort_order is used to order the hourly slots for the user.

The current reporting tool groups the data by region and method and produces a standard report of calculated value against hourly_slot sorted. It also produces a chart of calculated value against hourly slot for each ‘group’ data. Each control break appears as its own report and chart.

Replicating this in APEX is easy enough for the report e.g. use control break for the region and method, but doesn't seem so clear cut for the charting requirement. Can I produce a report per control break, or a chart that contains all the data with series per control break - when the control breaks are unknown at build time?

Any thoughts?