Passing Multiple Values to a Parameter in Oracle BI Publisher
Dynamic reporting often requires the ability for users to filter a report by multiple criteria for example, selecting more than one ledger, company, or entity. Oracle BI Publisher supports parameter types that allow users to make multiple selections, enhancing the flexibility and usability of reports.
Setting Up a Multi-Select Parameter
To enable multi-value filtering:
In your data model, create a parameter and associate it with a List of Values (LOV).
Set the parameter type to “Menu” and check the option for multi-selection.
Users will now see a checkbox list or a “Select All” option at runtime and can select one, many, or all values.
SQL for Multi-Value and “All” Selections
You must write your SQL to handle both single and multiple values in particular, accommodating the “All” case when users want no filter applied.
A common pattern is as follows:
AND (
'All' IN (:P_LEDGER || 'All')
OR
Ledger.NAME IN (:P_LEDGER)
)
If “All” is selected in the parameter, the clause always returns true and applies no filter.
When specific ledgers are selected, the
INclause filters the results accordingly.
This approach supports mixed use cases:
Single selection: Only the chosen value filters.
Multiple selections: All selected values are included in the report.
All selected: The filter is bypassed, and all results are shown.
Notes and Best Practices
With text-based parameters, the user can also type a comma-separated list and you can parse it using INSTR/SUBSTR or regular expressions in SQL if needed.
Always verify that the LOV source for your parameter returns only valid, unique values and consider providing an explicit “All” option if not using the concatenation trick.
Document the parameter’s behavior in your report instructions so end-users know how multi-select and “All” operate.
Summary
Using these techniques, you can greatly enhance the interactive power of your Oracle BI Publisher reports, providing end users with familiar, dashboard-style filtering and a seamless analytics experience.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
