Tuesday, June 15, 2010

How to only Display Column when Downloading

Sometimes you may have a column that contains inline HTML. A simple example of this is if you have a column called "color" and you want to display the color in the report. Your query would look like this:

SELECT '"' || color || '' color
FROM my_colors
Note: That they're ways to get around this simple example using Report Templates and Column Formatting. I'm just using it for demo purposes

If you were to download this report the "color" column would contain all the html (i.e. span tags etc). This may confuse users since they expected to see "red, green, blue, etc..." in their download file, but instead see the colors wrapped in a lot of html.

A workaround that I've used is to create 2 columns: color_html and color

SELECT '"' || color || '' color_html,
color color
FROM my_colors
And modify each report column's attributes:

Standard Reports

color_html
Column Definition:
Include In Export: No

color
Conditional Display:
PL/SQL Function Body Returning a Boolean: return apex_application.g_excel_format

Interactive Reports

color_html
Conditional Display:
Request is NOT Contained within Expression 1: CSV,HTMLD

color
Conditional Display:
Request is Contained within Expression 1: CSV,HTMLD

Now when a user downloads the report they'll get the non-html version of the column. This solution works in APEX 4.0 and APEX 3.x

4 comments:

  1. Hi martin..

    This solution is not working 4.1. Is there any work around for it in 4.1?

    ReplyDelete
  2. Today I can confirm this is still working on Classic Reports in 4.2.0

    ReplyDelete
  3. The interactive report solution works only until someone chooses "Select Columns." As soon as any changes are made, the hidden (downloadable only) column is silently no longer included in the downloaded report.

    ReplyDelete
  4. I had the same issue as blk above - I solved it by going back to a single column, but put a conditional in the query, e.g.:

    SELECT CASE WHEN NVL(:REQUEST,'-') NOT IN ('CSV','HTMLD') THEN '(html version here)' || color || '(end html)' ELSE color END color
    FROM my_colors

    ReplyDelete