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