Display Binary Data in Oracle Apex using Checkbox

We want to use checkbox to display a column in interactive report, which contains binary data 0 and 1.

Solution 1: Not perfect. This will cause problem in exported CSV file

In Region Source for interactive report, using CASE function to format this filed.

select t1.sponsor,
CASE
    WHEN T1.EXPERIENCE = 1 THEN 'checked=checked'
END AS EXPERIENCE_CHECKBOX
from sponsor t1

Then add

<input type="checkbox" disabled readonly #EXPERIENCE_CHECKBOX# />

to HTML Expression of EXPERIENCE_CHECKBOX column formatting.

This solution works fine by looking. However, you will notice problem in downloaded file, the content of experience column was replaced by checked="checked" and null, not original values of 0 and 1.

Solution 2: a Better solution

Use a pl/sql function to format filed content based on request type.

CREATE OR REPLACE FUNCTION FORMAT_STRING (P_INT       IN INT,
                                          P_REQUEST   IN VARCHAR2)
   RETURN VARCHAR2
IS
   V_STRING   VARCHAR2 (40);
BEGIN
   IF P_REQUEST NOT IN ('HTMLD', 'XLS', 'CSV') OR P_REQUEST IS NULL
   THEN
      IF P_INT = 1 -- or whatever value used to indicate checked
      THEN
         V_STRING := 'checked=checked';
      END IF;
   ELSE
      V_STRING := P_INT;
   END IF;

   RETURN V_STRING;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END FORMAT_STRING;

In Region Source for interactive report, use FORMAT_STRING function to format experience field.

select t1.sponsor, format_string(T1.experience, :request) EXPERIENCE_CHECKBOX from sponsor t1

Also add

<input type="checkbox" disabled readonly #EXPERIENCE_CHECKBOX# />

to HTML Expression of EXPERIENCE_CHECKBOX column formatting.

Final Product