Classic Report with Icons and Percentage Chart Column Format

For this example we are going to use data from the table EBA_DEMO_IR_PROJECTS of the demo application Sample Reporting.

For those who do not have access to this table, I have created a custom table with the same information:

Here are the scripts:

CREATE TABLE  "KIU_PROYECTOS" 
   (	"ID" NUMBER, 
	"PROYECTO" VARCHAR2(30), 
	"NOMBRE_TAREA" VARCHAR2(255), 
	"FECHA_INICIO" DATE, 
	"FECHA_FIN" DATE, 
	"ESTADO" VARCHAR2(30), 
	"ASIGNADO_A" VARCHAR2(30), 
	"COSTO" NUMBER, 
	"PRESUPUESTO" NUMBER, 
	 CONSTRAINT "KIU_PROYECTOS_PK" PRIMARY KEY ("ID") ENABLE
   )
/

CREATE SEQUENCE   "KIU_PROJECTS_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
/

CREATE OR REPLACE TRIGGER  "BI_KIU_PROYECTOS" 
  before insert on "KIU_PROYECTOS"               
  for each row  
begin   
  if :NEW."ID" is null then 
    select "KIU_PROJECTS_SEQ".nextval into :NEW."ID" from sys.dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_KIU_PROYECTOS" ENABLE
/

You can load the table data through the SQL Workshop using this file.

We create a page with a region of type Classic Report which we will call Projects.

In the Source query we place the following SQL query:

select   
      proyecto,  
      fecha_inicio,  
      fecha_fin,  
      estado,  
      case estado   
        when 'Open' then 'fa-clock-o is-open'  
        when 'Closed' then 'fa-check-circle is-closed'  
        when 'On-Hold' then 'fa-exclamation-circle is-holding'  
        when 'Pending' then 'fa-exclamation-triangle is-pending'  
      end estado_icono,  
      greatest(0, least((sysdate - fecha_inicio) / ((fecha_fin + 1) - fecha_inicio), 1)) * 100 Progreso,  
        asignado_a
    from KIU_PROYECTOS 
    order by 2  

We can see in our query that we have the column ESTADO and also the column ESTADO_ICONO which shows the open, closed, on-hold or pending icon depending on the denomination in the ESTADO column.

Expand the Classic Report columns, select the ESTADO column and add in the Column Format section, the following HTML expression:

<span class="fa #ESTADO_ICONO#"></span> #ESTADO#  

With the substitution variable #ESTADO_ICONO# we are setting the name of the corresponding icon.  

Then we hide the column ESTADO_ICONO of the Classic Report.  

We add some CSS rules to give color to the icons, for it from the page attributes we place the following CSS in the Inline CSS section:

.is-open {  
      color: #ff2d55; }  
      
    .is-holding {  
      color: #ffcc00; }  
      
    .is-pending {  
      color: #ff9500; }  
      
    .is-closed {  
      color: #4cd964; }  

At the moment we see our Classic Report as follows:

We go back to the Page Designer and select the PROGRESO column and in the Identification section we select in Type to be Percentage Chart, the project progress will be displayed in a percentage bar.

In the header we place: Progreso

In Appearance:
– Color de Fondo: #E2ECF8
– Color de primer Plano: #5284C1

As we can see in the SQL query we are using the function greatest(). This function returns the greater of one or more expressions. Oracle uses the first expression to determine the return type. If the first expression is numeric, Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before comparison, and returns that data type. If the first expression is non-numeric, each expression after the first is implicitly converted to the data type of the first expression before comparison.

Character comparison is based on the numeric codes of the characters in the database character set and is performed on complete strings treated as a sequence of bytes, rather than on a character-by-character basis. If the value returned by this function is character data, then its data type is always VARCHAR2.

The function least() returns the minimum of the list of expressions. All expressions after the first one are implicitly converted to the data type of the first expression before the comparison.

Now we can see in the Classic report the progress column as a bar chart type column.

I hope this post is helpful!

See you soon!