Hiding HTML when downloading an Interactive Report

A common requirement is to format data for display in an Interactive Report, for example showing an icon, making part of the data a clickable link or a button, or showing it in different colours.

The problem with embedding formatting in the data for the report is that it is not only used for display in the web page; it is also used for the Download option, causing user confusion when HTML code is exposed in the generated CSV file.

If the logic for the formatting is not data-driven, the solution is to enter the HTML in the HTML Expression attribute on the report column definition. The HTML Expression is used only when displaying the data in the interactive report; the underlying column data is used for the Download. The underlying column data is also used to generate the filter list for the column (if Column Filter Type is “Default Based on Column Type”).

If the logic for the formatting is data-driven, a simple solution is to generate the HTML in an underlying column in the query. However, you don’t want this HTML appearing in the Download CSV, so what you can do is generate the HTML in an additional, hidden column in the report; then use the hidden column in the HTML Expression attribute. This works because the HTML Expression attribute is allowed to refer to any column in the query, even hidden ones.

For example, here is a query with a generated column that determines an icon to be displayed within the “job” column:

select ename,
       job,
       job
       || ' <span class="fa '
       || case when mgr is null then 'fa-gear' else 'fa-user' end
       || '"></span>' as job_html
from emp

The following column attributes are set:

  • JOB: HTML Expression = #JOB_HTML#
  • JOB_HTML: Type = Hidden Column

When the report is run, we see the icons displayed:

When the report is downloaded as CSV, the Job column is plaintext as desired:

Comments

  1. VP
    24 May 2019 - 11:32 pm

    select ename,
    job,
    job
    case when mgr is null then ‘fa-gear’ else ‘fa-user’ end as job_html
    from emp

    and then in the HTML attributes
    #JOB#’

  2. Kinjan Bhavsar
    14 June 2019 - 5:00 pm

    Hi Jeff,

    I am using id column with anchor <a> tag to redirect to a page. Can you suggest what needs to be done for such a case?

    • Jeffrey Kemp
      14 June 2019 - 5:50 pm

      Hi Kinjan,

      It doesn’t matter what html you’re generating, whether a span, a div, or an anchor or whatever – the concept is the same and you would put it in the HTML expression as usual.

      I hope this helps.

      Jeff

  3. siva
    19 June 2020 - 1:36 am

    Hi Jeff,

    I am using one column as APEX_ITEM.select_list(Processed,Pending) to make editable Report. I am getting generated “PendingProcessed” value in PDF and CSV report. Please suggest what needs to be done in this case?

    • Jeffrey Kemp
      19 June 2020 - 7:23 am

      Use a different column, one for the “real” value, one for the “display” value.

      For example, if your column is called “STATUS”, generate these in your query like this:

      select t.status, APEX_ITEM.select_list(...) as status_display from mytable t;

      Set the column STATUS_DISPLAY to Hidden. Set the HTML Expression on the STATUS column to #STATUS_DISPLAY#.

      Now, on the page the HTML Expression will be used, which shows your select list. When exported, the underlying value (STATUS) will be emitted.

  4. Ashi
    26 February 2021 - 9:09 pm

    Hi Jeff,
    I have the same requirement, there is just one change that I am using listaggr and and maybe that’s why this solution is not working in my case. Any thoughts

    • Jeffrey Kemp
      26 February 2021 - 9:43 pm

      Hi Ashi, I don’t know – whatever your query is doing, I don’t imagine it would be impossible to use this technique to generate the HTML expression needed.

  5. Andy
    4 March 2021 - 10:53 pm

    Hi Jeff it’s not working for me I done just the same as you said.
    I just wanted to assign colours as per text values. But it’s just showing me text without colour.

    • Jeffrey Kemp
      4 March 2021 - 11:09 pm

      I’m sorry Andy but I don’t know how to help you – I can’t see your screen, and I can only guess what your issue might be. You will have to provide more specific details about what you have tried so far, what exactly the result is that you’re seeing, and what result you are expecting.

  6. Andy
    4 March 2021 - 10:54 pm

    Sorry forgot to mention I’m using classic report not interactive report.

  7. Andy
    5 March 2021 - 12:07 pm

    Sorry, it’s working now. I was doing wrong.

  8. Massimo
    16 July 2022 - 8:44 am

    Sorry, I know this is an old post… I have the opposite requirement:
    I need data from a query to be nicely displayed, but has to be downloaded in CSV format. I created a classic report with only one plain text column and added the formatted HTML with all query columns in it.
    All other columns are set as hidden, so the reports displays as an HTML document, but when I download, I just get the column I has set as plain text.
    I tried creating a dummy columns and using that to display the HTML expression, but if I make the other columns visible, they get added to the classic report. If I make them hidden, then I download an empty file.

    Not sure if my issue is clear, and if there is any way to make this work without having to create a report template.

    Thanks in advance!

Leave a Reply

Your email address will not be published / Required fields are marked *