Plug In: Heat Map Report


It's a Heat Map (or heatmap) Report Plug-In. It's a representation of data where values are depicted by color. It makes the visualize of complex data easy and understand it at a glance.

This has been inspired by apex.oracle.com. Special thanks to Oracle APEX Product Development team, who brought this solution.

Plug-In Details:


DB versions: 12.1.0.1, 12.2.0.1, 18.4.0.0, 19.0.0.0.0, 19.2.0.0.19, 21.0.0.0.0, 21.1.0.0.0, 21.1.0.0.1

APEX versions: 20.1.0.00.13, 20.2.0.00.20,  21.1.0


Installation:

Go to GitHub and export plugin file "region_type_plugin_orclking_heatmap_report.sql" from Source directory and import it into your application.

Steps to Achieve:

Step 1: Export a script "Script to Populate Sample Data.sql" from directory and compile it in your schema.

Step 2: Create a new blank page.

Step 3: Export plugin file "region_type_plugin_orclking_heatmap_report.sql" from GitHub source directory and import it into your application.

Navigation: Shared Components ==> Plug-ins ==> Import


Plugin will be listed under plug-ins bucket after successful installation.


Step 4: Create a region to the page. Change region type to Heat Map Report [Plug-In].


Step 5: Construct Oracle SQL query and copy and paste it in region SQL Query section.


Query Template:

SELECT 1 heat_map_id, -- Primary key of the Table   
       'Product A' heatmap_tootltip,          
        apex_string.get_initials('Product A',6) heatmap_label, -- Initials          
        apex_util.prepare_url ('f?p='||:app_id||':1:'||:app_session
||':::1:P1_ID:'||1) heatmap_link,          
        1000 heatmap_value -- Note: It must be a number field          
  FROM dual
 WHERE 1 = 1;

Sample Query to Render a Report:

Note: Populate sample data by exporting a script "Script to Populate Sample Data.sql" from GitHub directory and compile it in your schema.

WITH final_data AS    
(SELECT dt.dept_id,     
        count(*) total_tasks       
  FROM dept_tasks dt
 GROUP BY dt.dept_id
 )
   SELECT dept.dept_id heat_map_id, -- Primary key of the Table
          dept.dept_name heatmap_tootltip,         
          apex_string.get_initials(dept.dept_code,6) heatmap_label, -- Initials          
          apex_util.prepare_url ('f?p='||:app_id||':1:'
                                 ||:app_session||':::1:P1_ID:'||1) heatmap_link,          
          fd.total_tasks heatmap_value -- Note: It must be a number field          
     FROM department_master dept,     
          final_data fd          
    WHERE 1 = 1    
      AND dept.dept_id = fd.dept_id      
    ORDER BY fd.total_tasks DESC;

Output: Then you output would display like this,


The demo is here.

That's it.

Happy APEXing!!!...

References: 

Comments

Popular posts from this blog

Highlight the cell of Interactive Report based on Search Criteria in Oracle APEX

Number of Tables by The Number of Rows in Oracle Database

Querying Data From Flat Files in Oracle

Highlight Data That Meet Certain Criteria in Oracle APEX Report

Return a Validation Message using JavaScript if the Oracle Application Express Item is Empty

Find Where Specific Table or View is Used in Oracle Database

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Remove X (Close Mark) from Oracle APEX Modal Dialog

[Loner Tables] Find Tables Without Relationships in Oracle Database

Change the color of the rows alternatively using a Simple CSS in Oracle APEX IR & IG Reports