{"id":17384,"date":"2023-07-07T18:47:51","date_gmt":"2023-07-07T18:47:51","guid":{"rendered":"https:\/\/kiusi.com\/?p=17384"},"modified":"2023-07-07T19:04:17","modified_gmt":"2023-07-07T19:04:17","slug":"informe-clasico-con-iconos-y-formato-de-columna-de-grafico-de-porcentaje","status":"publish","type":"post","link":"https:\/\/kiusi.com\/en\/informe-clasico-con-iconos-y-formato-de-columna-de-grafico-de-porcentaje\/","title":{"rendered":"Classic Report with Icons and Percentage Chart Column Format"},"content":{"rendered":"

For this example we are going to use data from the table EBA_DEMO_IR_PROJECTS<\/strong> of the demo application Sample Reporting<\/strong>. <\/p>\n\n\n\n

For those who do not have access to this table, I have created a custom table with the same information: <\/p>\n\n\n\n

Here are the scripts: <\/p>\n\n\n\n

CREATE TABLE  \"KIU_PROYECTOS\" \n   (\t\"ID\" NUMBER, \n\t\"PROYECTO\" VARCHAR2(30), \n\t\"NOMBRE_TAREA\" VARCHAR2(255), \n\t\"FECHA_INICIO\" DATE, \n\t\"FECHA_FIN\" DATE, \n\t\"ESTADO\" VARCHAR2(30), \n\t\"ASIGNADO_A\" VARCHAR2(30), \n\t\"COSTO\" NUMBER, \n\t\"PRESUPUESTO\" NUMBER, \n\t CONSTRAINT \"KIU_PROYECTOS_PK\" PRIMARY KEY (\"ID\") ENABLE\n   )\n\/\n<\/code><\/pre>\n\n\n\n
CREATE SEQUENCE   \"KIU_PROJECTS_SEQ\"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE\n\/\n<\/code><\/pre>\n\n\n\n
CREATE OR REPLACE TRIGGER  \"BI_KIU_PROYECTOS\" \n  before insert on \"KIU_PROYECTOS\"               \n  for each row  \nbegin   \n  if :NEW.\"ID\" is null then \n    select \"KIU_PROJECTS_SEQ\".nextval into :NEW.\"ID\" from sys.dual; \n  end if; \nend; \n\n\/\nALTER TRIGGER  \"BI_KIU_PROYECTOS\" ENABLE\n\/\n<\/code><\/pre>\n\n\n\n
You can load the table data through the SQL Workshop using this file<\/a>.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
We create a page with a region of type Classic Report<\/strong> which we will call Projects. <\/p>\n\n\n\n
In the Source query we place the following SQL query:<\/p>\n\n\n\n
select   \n      proyecto,  \n      fecha_inicio,  \n      fecha_fin,  \n      estado,  \n      case estado   \n        when 'Open' then 'fa-clock-o is-open'  \n        when 'Closed' then 'fa-check-circle is-closed'  \n        when 'On-Hold' then 'fa-exclamation-circle is-holding'  \n        when 'Pending' then 'fa-exclamation-triangle is-pending'  \n      end estado_icono,  \n      greatest(0, least((sysdate - fecha_inicio) \/ ((fecha_fin + 1) - fecha_inicio), 1)) * 100 Progreso,  \n        asignado_a\n    from KIU_PROYECTOS \n    order by 2  <\/code><\/pre>\n\n\n\n
We can see in our query that we have the column ESTADO<\/strong> and also the column ESTADO_ICONO<\/strong> which shows the open, closed, on-hold or pending icon depending on the denomination in the ESTADO column. <\/p>\n\n\n\n
Expand the Classic Report columns, select the ESTADO column and add in the Column Format section, the following HTML expression:<\/p>\n\n\n\n
<span class="fa #ESTADO_ICONO#"><\/span> #ESTADO#  <\/code><\/pre>\n\n\n\n
With the substitution variable #ESTADO_ICONO# we are setting the name of the corresponding icon.   <\/p>\n\n\n\n
Then we hide the column ESTADO_ICONO of the Classic Report.   <\/p>\n\n\n\n
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:<\/p>\n\n\n\n
.is-open {  \n      color: #ff2d55; }  \n      \n    .is-holding {  \n      color: #ffcc00; }  \n      \n    .is-pending {  \n      color: #ff9500; }  \n      \n    .is-closed {  \n      color: #4cd964; }  \n<\/code><\/pre>\n\n\n\n
At the moment we see our Classic Report as follows:<\/p>\n\n\n
\n
\"\"<\/a><\/figure>\n<\/div>\n\n\n
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<\/strong>, the project progress will be displayed in a percentage bar.<\/p>\n\n\n\n
In the header we place: Progreso<\/strong><\/p>\n\n\n\n
In Appearance: 
– Color de Fondo: #E2ECF8 
– Color de primer Plano: #5284C1<\/p>\n\n\n\n
As we can see in the SQL query we are using the function greatest(). <\/strong>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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The function least()<\/strong> 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.<\/p>\n\n\n\n
Now we can see in the Classic report the progress column as a bar chart type column.<\/p>\n\n\n
\n
\"\"<\/a><\/figure>\n<\/div>\n\n\n
I hope this post is helpful!<\/p>\n\n\n\n
See you soon!<\/p>","protected":false},"excerpt":{"rendered":"
Para este ejemplo vamos a usar los datos de la tabla EBA_DEMO_IR_PROJECTS de la aplicaci\u00f3n demo Sample Reporting. Para los que no tengan acceso a dicha tabla he creado una tabla personalizada con la misma informaci\u00f3n: Aqu\u00ed tienes los scripts: Los datos de la tabla los puedes cargar a trav\u00e9s del Taller de SQL usandoContinue reading “Informe Cl\u00e1sico con Iconos y Formato de Columna de Gr\u00e1fico de Porcentaje”<\/span><\/a><\/p>","protected":false},"author":132749711,"featured_media":17396,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_seo_schema_type":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_wpcom_ai_launchpad_first_post":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"{title}\n\n{excerpt}\n\n{url}","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_wpas_customize_per_network":false,"jetpack_post_was_ever_published":false},"categories":[121913,121887,122137],"tags":[121903,122152],"class_list":["post-17384","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-oracle-apex","category-oracle-apex-23-1","tag-apex","tag-informe-clasico","entry"],"jetpack_publicize_connections":[],"jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paMvMS-4wo","amp_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/kiusi.com\/wp-content\/uploads\/2023\/07\/portada-personalizar-informe.jpeg?fit=2949%2C1735&ssl=1","_links":{"self":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts\/17384","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/users\/132749711"}],"replies":[{"embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/comments?post=17384"}],"version-history":[{"count":11,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts\/17384\/revisions"}],"predecessor-version":[{"id":17400,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts\/17384\/revisions\/17400"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/media\/17396"}],"wp:attachment":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/media?parent=17384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/categories?post=17384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/tags?post=17384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}