Single Article

APEX interactive report aggregate cell font color

Category APEX Tricks

Here is how change style for APEX interactive report aggregate cell e.g when calculated value is negative.

On this solution I pass aggregate cell value using Ajax to database. Application process checks passed values and returns JSON witch cells have e.g. negative value.

Values are checked on database side because all possible number formats we can have on report. This is of course one extra call to database, but I find this is easier than try handle numbers on JavaScript.

First create application item for holding report number format.

Set Session State Protection to Restricted - May not be set from browser.

 

Create application computation for application item.

  • Computation Point: On New Instance (new session)
  • Computation Type: Static Assignment

Enter format mask you like use to Computation text area.

 

Go edit your interactive report number colums. Place application item to Number / Date Format.

 

Create On Demand application process CHECK_IR_AGGR_VALUES

DECLARE
  l_arr APEX_APPLICATION_GLOBAL.vc_arr2;
  l_num NUMBER;
BEGIN
  FOR i IN 1 .. APEX_APPLICATION.G_F01.COUNT
  LOOP
    BEGIN
      l_num := TO_NUMBER(APEX_APPLICATION.G_F01(i), :F39006_NUMBER_FORMAT);
      IF l_num IS NULL THEN
        l_arr(i) := NULL;
      ELSIF l_num < 0 THEN
        l_arr(i) := 'NEG';
      ELSE
        l_arr(i) := 'POS';
      END IF;
    EXCEPTION WHEN VALUE_ERROR THEN
      l_arr(i) := 'ERR';
    END;
  END LOOP;
  APEX_UTIL.JSON_FROM_STRING(APEX_UTIL.TABLE_TO_STRING(l_arr));
END;

 

Create dynamic Action. Select Advanced

  • Name: Format IR aggerate values
  • Event: After Refresh
  • Selection Type: Region
  • Region: {select your report region}
  • Condition: No Condition
  • Action: Execute JavaScript code
  • Fire On Page Load: True
  • Code:
    var lArr=new Array()
    ,lTd=$("td.apexir_AGGREGATE_VALUE");
    if(lTd.length===0){
     return false;
    }
    lTd.each(function(i){
     var lTmp=new Array();
     lTmp=$(this).text().split(':');
     if(lTmp.length>1){
      lTmp.shift();
     }
     lArr.push(lTmp[0]);
    });
    
    $.ajax({
     url:"wwv_flow.show",
     traditional:true,
     dataType:"json",
     type:"POST",
     data:{
      p_flow_id:"&APP_ID.",
      p_flow_step_id:"&APP_PAGE_ID.",
      p_instance:"&APP_SESSION.",
      p_request:"APPLICATION_PROCESS=CHECK_IR_AGGR_VALUES",
      f01:lArr
     },
     success:function(jd){
      $.each(jd.row,function(i,jr){
       if(jr.R==="NEG"){
        lTd.eq(i).css({"color":"red"});
       }
       if(jr.R==="POS"){
        lTd.eq(i).css({"color":"green"});   
       }
      });
     }
    });
    
  • Selection Type: None

 

Create aggregate to your report from action menu. Now negative values are red and positive values are green.

See working example.

Comment Button

Comments

  • dkubicek 2 Jul 2012  
    Hello Jari, Thanks. That is actually what I needed. Regards, Denes     
  • Jari Laine 2 Jul 2012  

    Hi Denes,

    I do not like give access to workspace.

    But you can download that sample from scroll_report_apex4.zip

    Regards,
    Jari

        
  • dkubicek 1 Jul 2012  

    Hello Jari, would it be possible to get access to the workspace where you post your examples. I am particullary interested in the following example:

    https://apex.oracle.com/pls/apex/f?p=39006:7

    Thanks and Regards, Denes

        
  • JohnS 18 Apr 2012  
    Jari, Full disclosure - it does not add "SUM" literal in APEX 4.02 either. John     
  • Jari Laine 18 Apr 2012  

    Hi John,

    Yes , some reason aggregate function sum do not add any text on APEX 4.1.1.

    This is changed feature from older version. In APEX 3.2 sum function add text "Sum:"

    Regards, Jari

        
  • JohnS 18 Apr 2012  
    The only aggregate I have ever used is SUM. I tested and you are correct about other aggregates (AVERAGE, COUNT) putting a literal description at the aggregate row level. Testing FIREFOX 11 and IE7, SUM does not put any literal (like Total) at the aggregate row level to indicated that the column was actually aggregated. No problem - just curious. Thanks for all your help on this issue. John     
  • Jari Laine 18 Apr 2012  

    Hi John,

    Thank you.

    E.g. text "Average" is displayed in aggregate row automatic by APEX when you use average function on aggregate.

    Regards, Jari

        
  • JohnS 18 Apr 2012  
    Jari, Excellent blog - your solution works very well and I will be using this approach in my application. One question - how were you able to insert the literals "Average" and "Median" into the aggregate row? Thanks in advance for your help. John     

Global Right Column

Search

Archives

Subscribe in a Reader