APEX

In our APEX interactive reports, we use custom number formats to round numeric values to a given (user-defined precision). E.g. given this data:

create table round\_test(pk number not null primary key, value number);

insert into round\_test(pk, value) values (1, 0.11);

insert into round\_test(pk, value) values (2, 0.19);

insert into round\_test(pk, value) values (3, 0.20);

insert into round\_test(pk, value) values (4, 0.21);

insert into round\_test(pk, value) values (5, 0.23);

insert into round\_test(pk, value) values (7, 0.28);

I've created an interactive report to display the "VALUE" column with format "999D9" (for illustration purposes, I've added an "ORIGINAL_VALUE" column that displays the data without a number format, i.e. with full precision - also see screenshot below).

The SQL query for the report is quite simple:

SELECT

  pk,

  value,

  value AS original\_value

FROM round\_test

Now if I click the column header to filter by this column, I get duplicate values (e.g. 0.2 appears four times - once for 0.19, 0.20, 0.23 and 0.28):

That's bad enough, but if I click one of these values, APEX filters by the exact value instead of the rounded one:

How can I

- get rid of the duplicates in the drop-down

- force APEX to filter by the rounded value

Note 1 Creating a view with only the rounded values and using that in the report definition is not a viable approach, since our reports include a custom export function that allows the users to export the data with full precision. The kind people on StackOverflow (see How do I correctly filter on numeric columns with custom format in Oracle APEX? - Stack Overflow) suggested to modify the SQL query to include both the rounded and exact values, hide the exact values and modify my export routine to use the hidden exact values, but I'd like to avoid this if possible).

Note 2 The behaviour is the same in APEX 4.2 (which we currently use) and APEX 5.0 (which I used at http://apex.oracle.com to create the example).

Any suggestions / comments would be highly appreciated.

Kind regards,

Frank