Displaying large numbers in human readable form

While developing the dashboard for the Apex Dashboard Competition I found the challenge of presenting large numbers in a readable form. If you want to display the population of countries in a report there is China and India with more than 1 billion inhabitants and Tuvalu does not reach 10,000. With a straight forward report it would look like the left image. The application apply of thousands separator improves the readability, but there is still too much detail in the numbers to easily get the meaning. The right image, where the numbers are formatted using size prefixes is much more readable.

You can see this in a useful example in the Apex World Dashboard. Select a country and look at the ranking table.
In Oracle Apex you can implement such formatting in your query, using a PL/SQL function. This solution has several disadadvantages:

• Column header sorting is impossible because the actual numbers are replace by a character representation
• Calling a PL/SQL function in a query can degrade performance
• the readability of your query gets less

The solution is to apply the formatting using JavaScript. This way the query is not affected and the formatting is done after the sorting.
The formatting is implemented in a After Refresh DA on the report in question:

format_numbers_in_table('#report_tablespaces .t-Report-report');

The function formats all the cells with numeric content.
The parameter to the function should be the selector to the HTML table containing the data to be formatted. In this case the UT Standard Region and Standard Report are used with a static ID tablespaces.

If the report contains a large number of columns or some columns should not be formatted the columns to be formatted can be limited by passing an array of column names as the second parameter:

format_numbers_in_table('#report_tablespaces .t-Report-report',['LAND_SURFACE','POPULATION']);

Only the mentioned columns will be formatted.

Store the following JavaScript functions on the page or seperately in a file. You can also download the code here:

/******************************************************
 * Display numbers readable
 *
 **********************/
function display_number(number)
{
  var sizes = [ {"base":1000,"symbol":"K"}
               ,{"base":1000000,"symbol":"M"}
               ,{"base":1000000000,"symbol":"G"}
               ,{"base":1000000000000,"symbol":"T"}
              ];
  
  var display = number.toString();

  if ( !isNaN(number) ) 
  {
    for ( i in sizes )
    {
      if ( number > sizes[i].base ) 
      {
        num1 = number / sizes[i].base;
        if ( num1 < 10 ) { num2 = Math.round(num1*10)/10; }
        else { num2 = Math.round(num1); }
        display = num2 + sizes[i].symbol;
      }
    }
  }

  return(display);
}
    
    
/******************************************************
 * Display numbers readable in table
 *
 **********************/
function format_numbers_in_table( table_selector, column_list)
{
  var table       = $(table_selector)[0];

  for (var rownum = 1, row; row = table.rows[rownum]; rownum++) 
  {
    //iterate through rows
    //rows would be accessed using the "row" variable assigned in the for loop
    for (var colnum = 0, col; col = row.cells[colnum]; colnum++) 
    {
      //iterate through columns
      //columns would be accessed using the "col" variable assigned in the for loop

      // format columns
      if ( col.innerHTML.length > 0 && col.innerHTML != ' ' )
      { 
        // if column list has content only format columns for this list
        var header = col.headers;          
        if ( column_list == null || column_list.indexOf(header) >= 0 )
        {
            col.innerHTML = display_number(col.innerHTML);        
        }
      }
       
    }  
  }
}

Happy Apexing