How to properly filter custom formatted numeric columns in Oracle APEX?
In our APEX reports, we use special number formats to round numeric values to a specified (user-defined precision). For example. 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 created an interactive report to show the column VALUE
with the format 999D9
(for illustration purposes, I added a column ORIGINAL_VALUE
that displays the data without numeric format, ie with complete accuracy - see the screen shot below.).
Now, if I click on a column header to filter on that column, I get duplicate values (for example 0.2 appears four times - once per 0.19, 0.20, 0.23 and 0.28):
That's bad enough, but if I hit one of these values, APEX filters the exact value instead of the rounded one:
How can I
- get rid of duplicates in the dropdown
- force APEX to filter by the rounded value
Note. Creating a rounded view and using that in a report definition is not a viable approach because our reports include a custom export function that allows users to export data with full fidelity.
UPDATE The SQL query for the report is pretty simple:
SELECT
pk,
value,
value AS original_value
FROM round_test
The behavior 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 an example).
source to share
To eliminate duplicates in a list of values item:
First you need to create LOV on Application -> Shared Components -> Other Components -> List of Values
With this sql query:
select distinct to_char(value,'999.9') d, round(value) r
FROM round_test order by 1 asc
Then, in Report Atributes, edit the column containing the value "value", and in the "List of Values" section, specify "Column Filter Type": use "Named List of Values to Filter Exact Match" and then "Named List of Values", select the one which you created earlier.
To filter by the rounded value:
Create a report, but instead of applying the format mask as you did (this maintains the actual value despite the format applied in the view), enter the value in the query like this:
SELECT
pk,
to_char(value,'999.9') value,
value AS original_value_
FROM round_test
I think this will suit your needs.
source to share
The ideal behavior would be to have one report column for value
. Clicking on the heading will give you standard options, including a filter that will display (and allow searches for) rounded decimal numbers. Unfortunately, Apex doesn't let you do this. The values that appear in the filter are determined using different values selected in the original report query; subsequent formatting in the report column definition only affects how the values are displayed, not the actual values, hence the apparent duplicates in the filter list.
I think the crux of your problem is that without any heavy customization of the interactive report filter (good luck if you go this route), I think you will need to have two columns for value
, one containing the actual value. and one containing the rounded or other formatted value to be used for filtering.
As suggested elsewhere, you can create a new SQL column for each of your reports:
SELECT
pk,
value,
to_char(value, 'fm999D9') AS formatted_value
FROM round_test
Alternatively, if your users are ready and able, you can show them how to create a computed column from a button Actions
:
Whichever way the column is created formatted_value
, it must be hidden to stop it messing up the export. Filtering then has to be done with a button Actions
:
source to share