How do I correctly filter on numeric columns with custom format in Oracle APEX?

4
votes

In our APEX 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 - see screenshot below).

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):

Filter drop down

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

Filter result

How can I

  • get rid of the duplicates in the drop-down
  • force APEX to filter by the rounded value

Note: Creating a view with 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.

UPDATE The SQL query for the report is quite simple:

SELECT 
  pk,
  value,
  value AS original_value
FROM round_test

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).

2
oracleoracle-apex
Could you post the sql query on the report? – Typo
That's actually really confusing, and now I can't tell right away how this behaved in apex 4.2. You might want to bring this to attention on the oracle forums too. – Tom
@Typo I've added it to the question – Frank Schmitt
@Tom It's the same in APEX 4.2 and APEX 5.0 (I've updated the question accordingly) – Frank Schmitt
Could you elaborate a bit on your custom export function? – Tom

2 Answers

3
votes

To eliminate duplicates on the List of values filter:

First you need to create a 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 on Report Atributes edit the column that contains the 'value' value, and in the List of Values Section specify on 'Column Filter Type': Use Named List of Values to Filter Exact Match and then on 'Named List of Values', select the one you created earlier.

To filter by the rounded value:

Create the report but instead of applying the format mask as you did (this maintains the actual value despite the format applied on the view) format the value on the query like this:

SELECT 
  pk,
  to_char(value,'999.9') value,
  value AS original_value_
FROM round_test

I think that will cover your needs.

Here's a working example

0
votes

The ideal behaviour would be to have a single report column for value. Clicking on the header would give the standard options, including a filter which would display (and allow searching on) rounded decimal numbers. Unfortunately Apex doesn't let you do that. The values that appear in the filter are determined using the distinct values selected in the initial report query; subsequent formatting in the report's column definition only affects how values are displayed, not the actual values, hence the apparent duplicates in the filter list.

I think the essence of your problem is that without doing some heavy customisation of the interactive report filter (good luck to you if you go that route), I think you'll have to accept having two columns for value, one containing the actual value, and one containing the rounded or otherwise formatted value, to be used for filtering.

As suggested elsewhere, you could create the new column in the SQL for each of your reports:

SELECT 
  pk,
  value,
  to_char(value, 'fm999D9') AS formatted_value
FROM round_test

Alternatively, if your users are willing and able, you could show them how to create a computed column from the Actions button:

Actions->Format->Compute

Whichever way the formatted_value column is created, it should be hidden in order to stop it messing up the export. Filtering would then have to be done from the Actions button:

Actions->Filter