Add new comment

I normally get a lot of mileage out of Application Express interactive reports. Unfortunately there are some restrictions on the SELECT statements that can be used with them. Here is a demonstration of a technique I use when I want to add some interactivity to a plain old Apex report.

I will create a Select List dropdown that submits and refreshes the report data automagically. The value in the select list will be applied as a filter to the report when it refreshes.

1) Create the Select List

• Items -> New Item -> Select List
• Choose "Select List with Submit"
• Next
• Enter Item Name and select where you would like the Select List to display.
• Next
• Enter Select List details including a Static or Dynamic List of Values. For my case I wanted to handle the situation where NULL shows all records, so I set "Display Null Option" = Yes and filled in "All" for Null Text.
• Next
• Enter Label Info
• Next
• Leave Dta Source info at defaults.
• Create Item

2) Create an unconditional page branch.

Assuming this is the only action of its type on the page, just add an unconditional branch on submit.
Have the submit redirect back to the same page.

• Branches -> New Branch
• On Submit: After Processing (After Computation, Validation, and Processing)
• Branch to Page or URL
• Next
• Enter the current page as the Branch Target
• Select "Reset Pagination for this Page"
• Enter your Item to pass through during the submit. In this case set :P01_PRIMARY_DBA with &P01_PRIMARY_DBA.
• Next
• Create Branch

3) Create the Report

Use this form as the basis for your report Select query:

SELECT *
  FROM DB_LIST
WHERE 1=CASE
        WHEN :P01_PRIMARY_DBA IS NULL THEN 1
        WHEN :P01_PRIMARY_DBA = DB_LIST.PRIMARY_DBA THEN 1
        ELSE 0
      END

Note the CASE in the WHERE will return 1=1 for all rows when the Page Item P01_PRIMARY_DBA is null.
Otherwise it will return 1=1 for rows where P01_PRIMARY_DBA is NOT NULL and the Select List value matches the PRIMARY_DBA column.
It will return 1=0 for rows where the Select List value do not match the PRIMARY_DBA column.

4) Run the report!
The first time you run it you should see all rows from your SQL source. Flip the Select List to another entry and your page should refresh and show you rows filtered by the entry you picked. Flip the Select List back to All in order to view all rows again.