During one of my recent training courses a delegate asked
how you deal with data coming from items that hold multiple values such as
multi-select lists, shuttles and radio buttons. So I thought I would try and
explain things via my blog.
First of all it’s important to understand that these
datatypes don’t actually return multiple values they are actually returning a
single value that is in a particular format ie ‘Value1:Value2’ where Value1 and
2 relate to your data eg jobs ie ‘MANAGER:CLERK’. The main crux of the issue
here is how to split up this string into its component parts ie
MANAGER
CLERK
For this we can use the APEX_UTIL. STRING_TO_TABLE(:P10_JOBS_FILTER)
function to take in the item values (in the format ‘ANALYST:MANAGER’) and
return them into a variable based on APEX_APPLICATION_GLOBAL.VC_ARR2 ie an
associative array with a varchar2 index.
So let’s see a working example.
First of all create a blank page with a classic report on
it. Use the following for the source code of the report :
Next
create a Multi-Select item called P10_JOBS_FILTER with the following in the
List of Values section:
And add a Go button to submit the page – so your page should look a bit like
this:
Of course you can avoid all the PL/SQL and just use the
following SQL as the report source code:
But no way near as much fun or future proof – and definitely
not enough to write a blog on 
