User Guide › Reports › Report Types › Free-Form Reports › HeatMap

HeatMap

A Heatmap is a graphical display window into underlying real-time business data. Heatmaps provide a visual, matrix representation of the status of multiple metrics via color coding.

Heatmaps include:

• Two-level X-axis; two-level Y-axis
• Export (PDF, HTML, CSV,…)
• Schedule
• Ability to be included in Dashboard
• Copy and paste to Excel

A Heatmap report is a Free-Form report, with predefined visualization. Parameters are supported, using Free-Form parameters.

Turning on the HeatMap Feature

You will need to use the following update:

update t_system_configurations
set sys_config_value = 'Y'
where sys_config_name = 'display_visualization'
and sys_config_parent = 162

The Heat map SQL query uses the following format:

• Exactly 6 columns are returned
• These 6 columns assume the following, in the following fixed order:
  1. Color
  2. Value
  3. Y axis (Vertical) Parent
  4. Y axis (Vertical) Child
  5. X axis (Horizontal) Parent
  6. X axis (Horizontal) Child

Columns headings in the result set are irrelevant.

Note: Each cell in the heat map represents a unique combination of: {Y Parent, Y Child, X Parent, X Child}

Note: Each cell shows the information: {color, value}.

• In the result set, there should be only one row for each {Y Parent, Y Child, X Parent, X Child} combination, since the corresponding {color, value} data must fit into exactly one cell. If there is more than one row for a particular {Y Parent, Y Child, X Parent, X Child} combination, then only one of those rows would have their {color, value} data shown in the corresponding cell.
• Conversely, not every possible {Y Parent, Y Child, X Parent, X Child} combination needs to be returned in the result set. If there are combinations missing, those cells would simply be shown empty in the report.
• ORDER BY in the query is allowed, but not necessary.
• The Color column must return a valid HTML Color, which can be in Name format (e.g., "MidnightBlue"), or Hex format (e.g., "#191970").

The following is an example of a Heat map query:

SELECT 
                CASE 
                                WHEN (t.VAL > 800) THEN 'red' 
                                WHEN (t.VAL > 600) THEN 'orange' 
                                WHEN (t.VAL > 400) THEN 'yellow' 
                                ELSE 'green' 
                END AS COLOR, 
                t.val, 
                SD.SERVICE_DOMAIN_NAME "Service Domain", 
                DC.DOMAIN_CATEGORY_NAME "Domain Category", 
                GROUPS.APPLICATION_GROUP_NAME "Service group", 
                AG.APPLICATION_GROUP_NAME "Service"
FROM T_SERVICE_DOMAINS    SD, 
                T_DOMAIN_CATEGORIES  DC, 
                T_APPLICATION_GROUPS AG, 
                T_APPLICATION_GROUP_MEMBERS AGM, 
                (SELECT round(dbms_random.value(1,1000)) val FROM dual) t, 
                (
                                SELECT APPLICATION_GROUP_NAME, APPLICATION_GROUP_ID 
                                FROM t_application_groups 
                                WHERE application_default_group = 'N') GROUPS 
WHERE AG.APPLICATION_GROUP_ID = AGM.APPLICATION_ID 
AND AGM.APPLICATION_GROUP_ID = GROUPS.application_group_id  
AND dc.service_domain_id = sd.service_domain_id 
ORDER BY 3, 4, 5, 6