Skip to main content

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

Post a Comment

Popular posts from this blog

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template
2 comments
Read more

Generating the report with APEX_DATA_EXPORT

With the APEX_DATA_EXPORT package, you are able to export data from Oracle Application Express in the following file types: PDF, XLSX, HTML, CSV, XML, and JSON. Step 1: Create a table and populate it with some sample records. CREATE TABLE emp   (     empno        NUMBER,     first_name   VARCHAR2(240),     last_name    VARCHAR2(240),     mgr          NUMBER,     deptno       NUMBER,     sal          NUMBER,     created_date TIMESTAMP (6),     comm         NUMBER,     hiredate     DATE,     JOB          VARCHAR2(240),     ename        VARCHAR2(240),     PRIMARY KEY (empno) USING INDEX ENABLE   ); /    INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (1, 'Larry', 'Ellison', ,                  10, 5000, LOCALTIMESTAMP);   INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (2, 'Juan', 'Juan', 1,  
5 comments
Read more

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  manager ,                emp . hiredate ,                 nvl ( emp . sal , 0 )  salary ,                 nvl ( emp . comm , 0 )  commission            FROM  eba_demo_chart_emp emp ,                eba_demo_chart_dept dept ,                eba_demo_chart_emp mgr           WHERE  emp . deptno = dept . deptno             AND  emp . mgr      = mgr . empno  ( + )           ORDER   BY  emp . ename
9 comments
Read more