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

Filter drop down

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

Filter result

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

+3


source to share


2 answers


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.

Here is a working example

+2


source


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

:

Actions-> Format-> Compute

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

:

Actions-> Filter

0


source







All Articles