Tooltip help for classic report header

Here is how you can add tooltip help to classic report headers.

I have previously write about this topic on Adding help text to column headings in a tabular form.

This post is updated version for APEX 4 and take advantage of dynamic action.

First create table where store help texts

CREATE TABLE DEMO_REPORT_COL_HELP(
  PK_ID               NUMBER NOT NULL ENABLE,
  APPLICATION_ID      NUMBER NOT NULL ENABLE,
  PAGE_ID             NUMBER NOT NULL ENABLE,
  REGION_STATIC_ID    VARCHAR2(40 BYTE) NOT NULL ENABLE,
  COLUMN_HEADER_NAME  VARCHAR2(30 BYTE) NOT NULL ENABLE,
  HELP_TEXT           VARCHAR2(4000 BYTE),
  CREATED_ON          DATE NOT NULL ENABLE,
  CREATED_BY          VARCHAR2(255 BYTE) NOT NULL ENABLE,
  CHANGED_ON          DATE,
  CHANGED_BY          VARCHAR2(255 BYTE),
  CONSTRAINT DEMO_REPORT_COL_HELP_PK PRIMARY KEY(PK_ID)
);

Create trigger for table

CREATE OR REPLACE
TRIGGER BIU_DEMO_REPORT_COL_HELP BEFORE
  INSERT OR 
  UPDATE ON DEMO_REPORT_COL_HELP 
  FOR EACH ROW
BEGIN 
 
  IF inserting THEN 
    
    IF :NEW.PK_ID IS NULL THEN 
      SELECT TO_NUMBER(SYS_GUID(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
      INTO :NEW.PK_ID
      FROM DUAL;
    END IF;
  
    :NEW.CREATED_ON := SYSDATE;
    :NEW.CREATED_BY := NVL(v('APP_USER'), USER);
    :NEW.CHANGED_ON := NULL;
    :NEW.CHANGED_BY := NULL;
    
  END IF;
  
  IF updating THEN
  
    :NEW.CHANGED_ON := SYSDATE;
    :NEW.CHANGED_BY := NVL(v('APP_USER'), USER);
    
  END IF;
  
END;
/

Create report where you like have tooltip help headers.

NOTE! Set static id to report region.

This is important and makes possible have multiple reports on same page with tooltip help.

Check report template "Column Heading Template" <th> tag have id attribute.

Id value must be substitution string #COLUMN_HEADER_NAME#. Add id attribute if it missing from your report template.

Example of Column Heading Template

<th #ALIGNMENT# id="#COLUMN_HEADER_NAME#" class="header">#COLUMN_HEADER#</th>

Report <th> tags need have id that we can easily attach tooltip using jQuery on dynamic action.

Next populate basic information to DEMO_REPORT_COL_HELP table by running below insert on APEX SQL Workshop.

Populate bind variables accordingly.

• APPLICATION_ID: your application id
• PAGE_ID: page id where is your report
• REGION_STATIC_ID: static id you have set to report region

INSERT
INTO demo_report_col_help
  (
    application_id,
    page_id,
    region_static_id,
    column_header_name
  )
SELECT r.application_id,
  r.page_id,
  r.static_id,
  c.column_alias
FROM APEX_APPLICATION_PAGE_REGIONS r,
  APEX_APPLICATION_PAGE_RPT_COLS c
WHERE r.page_id      = :PAGE_ID
AND c.page_id        = :PAGE_ID
AND r.application_id = :APPLICATION_ID
AND c.application_id = :APPLICATION_ID
AND r.static_id      = :REGION_STATIC_ID
AND r.region_id      = c.region_id
;

Update DEMO_REPORT_COL_HELP table HELP_TEXT column with information you like have to headers tooltip.

Create On Demand process GET_REPORT_HELP_TEXT:

DECLARE
  l_sql VARCHAR2(32700);
BEGIN

  l_sql := '
    SELECT COLUMN_HEADER_NAME,
      HELP_TEXT
    FROM DEMO_REPORT_COL_HELP
    WHERE APPLICATION_ID   = :APP_ID
      AND PAGE_ID          = :APP_PAGE_ID
      AND REGION_STATIC_ID = ''' || APEX_APPLICATION.G_X01 || '''
      AND HELP_TEXT       IS NOT NULL
  ';

  APEX_UTIL.JSON_FROM_SQL(l_sql);

EXCEPTION WHEN OTHERS THEN
  HTP.prn('{"row":[]}');
END;

Go edit report page and create dynamic action. Select Advanced

• Name: Get and Set Tooltip Help
• Event: After Refresh
• Selection Type: Region
• Region: {select your report region}
• Condition: No Condition
• Action: Execute JavaScript code
• Fire On Page Load: True
• Code:

  $.ajax({
   type:"POST",
   url:"wwv_flow.show",
   dataType:"json",
   data:{
    p_flow_id:"&APP_ID.",
    p_flow_step_id:"&APP_PAGE_ID.",
    p_instance:"&APP_SESSION.",
    p_request:"APPLICATION_PROCESS=GET_REPORT_HELP_TEXT",
    x01:this.triggeringElement.id
   },
   success:function(jd){
    $.each(jd.row,function(i,jr){
     $($x(jr.COLUMN_HEADER_NAME)).mouseover(function(evt){
      toolTip_enable(evt,this,jr.HELP_TEXT)
     }).find("*").removeAttr("title");
    });
   }
  });
          

• Selection Type: None

Now when you hover mouse over report header you get tooltip.

You can also create form over DEMO_REPORT_COL_HELP table to maintain help texts.