CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
ckp7blessed
ckp7blessed

Reputation: 135

Oracle Apex 22.21 - Popup LOV to Chart - include 'Select All' option as default

I have a table ORDERS which contains a column STATE which has states based in USA. I have a chart that filters a range of dates with two datepicker items. I want to include another filter where the user clicks a LOV of all the states.

LOV

select distinct state
from ORDERS
order by state asc

enter image description here

Is there any way to include an 'ALL' option at the top of the LOV? In which all the states are selected. It would be even better if 'ALL' is selected by default. Without the user having to click it. Currently, the user has to click on at least one state for a report to generate.

Source SQL Query for one of the reports

select
        COUNT(ORDER_NUMBER) AS ORDER_COUNT,
       TO_CHAR(ORDER_DATE,'YY-MM-DD') AS ORDER_DATE,
       TRUNC(ORDER_DATE)
from ORDER_TOTAL_VIEW
where ORDER_DATE between TO_DATE(:P8_DATE_FROM,'YY-MM-DD') and TO_DATE(:P8_DATE_TO,'YY-MM-DD')
and STATE IN 
  (select regexp_substr(:P8_STATE, '[^:]+', 1, level)
   from dual
   connect by level <= regexp_count(:P8_STATE, ':') + 1
  )
GROUP BY TO_CHAR(ORDER_DATE,'YY-MM-DD'), TRUNC(ORDER_DATE)
ORDER BY ORDER_DATE ASC

Upvotes: 0

Views: 519

Answers (1)

Littlefoot
Littlefoot

Reputation: 143053

Mind letter case! If you choose to use "All", then it has to be "All", always (not "all", "ALL" or whatever else).

Modify List of Values query so that it includes "All" at start of the list:

SQL> with temp as
  2    (select 1 rn, 'All' state from dual
  3     union all
  4     select 2 rn, state from orders
  5    )
  6  select state as display_value,
  7         state as return_value
  8  from temp
  9  order by rn, state;

DISPLAY_ RETURN_V
-------- --------
All      All
Alabama  Alabama
Colorado Colorado
Texas    Texas

SQL>

Modify query so that it takes into account what's being selected in the LoV:

select ...
from order_total_view
where (state = state and :P8_STATE = 'All')
   or (state in (select regexp_substr(:P8_STATE, '[^:]+', 1, level)
                 from dual
                 connect by level <= regexp_count(:P8_STATE, ':') + 1
                )
         and :P8_STATE <> 'All'
      )

Set P8_STATEs default value to All. If it doesn't work, include NVL function into query (for all references to P8_STATE):

where (state = state and nvl(:P8_STATE, 'All' = 'All')
                         --------------------
                         this

Upvotes: 2

Related Questions