Single Article

Tooltip help for classic report header

Category APEX and jQuery

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.

 

See working example.

Social Buttons

Comments

  • Theresa Hilding 7 Apr 2015  

    Yes, Jari, you are right. Thank you for pointing out the problem with duplicate id elements in the same document.

    To fix the problem I simply created a new report template and changed 'id' to 'class' in the <th> column header. I then updated the dynamic action appropriately:

    var myID = "th#" + jr.COLUMN_HEADER_NAME;

    is now

    var myID = "th." + jr.COLUMN_HEADER_NAME;

        
  • Jari Laine 5 Apr 2015  

    Hi Theresa,

    Great that you did get it working.

    Just one comment,
    if you have same column alias in three reports, standard classic report templates generates same id for all those column headings.
    This is not according HTML standard because id values should be unique in document.

    You should create new report template and e.g. remove id from th tags.

    Regards,
    Jari

        
  • Theresa Hilding 4 Apr 2015  

    I was able to solve my problem although I am not sure if this is the most efficient way to modify the selector to choose the same column header from multiple reports. Since APEX classic report column headers are <th> elements with id's equal to their column alias, I chose to use 'th#column_alias' as my jquery selector.

    success:function(jd){

    $.each(jd.row,function(i,jr){

    var myID = "th#" + jr.COLUMN_HEADER_NAME;

    $(myID).mouseover(function(evt){

    toolTip_enable(evt,this,jr.HELP_TEXT)

    }).find("*").removeAttr("title");

    });

    }

        
  • Theresa Hilding 4 Apr 2015  

    Thank you for this solution, Jari. It solved my problem and works great when I have only one report on my page. When I have more than one, however, only the topmost report will have tooltips.

    My application requires that I have three instances of the same report on one page. Each report has unique parameters and unique data but the column header are the same. In the DEMO_REPORT_COL_HELP table I would like to have only one row for each column header for ease of

    future maintenance.

    I created a "wrapper" html region with the static id "REPORTWRAPPER". Inside of this region

    I have 3 report subregions. None of the subregions have static ids. I've populated DEMO_REPORT_COL_HELP with the id REPORTWRAPPER and help_text for each of the column headers. When I run the page, only the topmost report has tooltips applied to it. Juggling the position of the subregions results in only the topmost report having tooltips.

    I suspect that there might be a way to modify the element selector in the dynamic action success function so that all three column headers get tooltips instead of just the top one but I have been unable to get this to work. I thought that '.find("*")' in the success function should be selecting all three column headers for each jd.row but it is not.

    ...

    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");

    });

    }

    ...

    I am new at jquery/json and although I have tried various modifications to '$($x(jr.COLUMN_HEADER_NAME))' nothing has worked so far. Is what I am trying to do possible and if so how?

    Thank you in advance for any help you can offer! Theresa

    Th

        

Global Right Column

Search

Archives

Subscribe in a Reader