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

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

Friendly URL: Redirect to Different Page after Login in Oracle APEX 20.1

Oracle has updated apex.oracle.com to APEX 20.1 which includes among other features the new Friendly URL option. Here i am giving an example to redirect to different page after login in Oracle APEX 20.1 [Friendly URL Enabled] Step 1: Define home page for each user in emp master table as below Step 2: To enable Friendly URL Syntax, follow below steps, 1) On the Workspace home page, click the App Builder icon. 2) Select an application (The Application home page appears). 3) From Application home page, you can access the Definition page in TWO ways: Click the Edit Application Properties button. From Shared Components:              1) Click Shared Components .              2) Under Application Logic, click Application Definition Attributes . The Definition page appears. 4) Under Properties, configure the Friendly URL s attribute: Click Apply Changes to save your ch...

Open modal/inline dialog in Oracle APEX using JS

Analysis: From Oracle Apex 4.2,  Inline dialog can be opened as modal dialog/popup using JS. Open inline dialog region as modal dialog: Step 1: Create static region. Step 2:  Set Template => Inline Dialog. Step 3:  Assign the static id to the region. Fig 1: Set template and Static ID Step 4:  Create button or change one of the report column type to Link. (Action: Redirect URL). Step 5:  Put the below JS to open the region as modal dialog.                javascript:openModal('unitpricedetails');            Fig 2:  Open simple modal dialog Open inline dialog region as modal dialog and pass values to the items: Common Steps:  Step 1 to Step 4. Step 5:  To pass value to that region, create P1_UNIT_ID in the inline  dialog.       javascript:$s('P1_UNIT_ID','#UNIT_ID#');javascript:openModal('unitpricedetai...