The other day, a client came up with a request: When the user calls an Interactive Report page in APEX, no query results should be displayed whatsoever until he enters at least some search criteria and clicks the GO button. How to achieve that?
There is more than one way to accomplish a task in APEX. I’m really fond of tricky CSS and jQuery solutions, but why not try a declarative approach using standard APEX features? Here’s a shorthand version of how it works – the easy way.
Say you have just created a new page.
- Add an interactive report as usual. In this example, it will be based upon “select * from emp”.
- Create a hidden item in the report’s region and name it SHOW_RESULTS. For “Source Used”, choose “Always, replacing any existing value in session state”. “Source Type” must be set to “Static Assignment (value equals source attribute)”. In the next field “Item Source Value”, type 0 (like zero). This means that on each page startup, SHOW_RESULTS will always be initialized with a value of 0.
- Add a Computation for “Item on this page”. From the Compute Item dropdown list, choose SHOW_RESULTS. Sequence doesn’t matter – keep the proposed value. For Computation Point, pick “After Region(s)” and leave “Computation Type” with “Static Assignment”. On the next wizard page, type 1 in the Computation field (as opposed to 0, which was this item’s Source Value).
- Returning to the Report Definition, alter the SQL statement into:
select * from emp where :SHOW_RESULTS = 1
(Step 4 is optional as you could have included the final SQL rightaway in step 1.)
That’s it. We’re done. Why does it work?
Because each time the page gets called from scratch, the SQL sent to the database evaluates to “… where 0 = 1″. Which means there will be no rows returned, thus keeping the result grid empty. In the meantime, the “After Region” computation has set the SHOW_RESULTS flag to 1. Any subsequent user action within the interactive report (click the Go button, invoke a filter,…) will not reload the page as a whole. Instead, an AJAX call will be posted with the new search criteria and issued to the wwv_flow.show process. This will return only the HTML that contains the updated resultset (based on “where 1 = 1″) to refresh the reports region.
A more elaborate sequel of this plot will be available next week (and also on my new applicationexpress Youtube channel). Then I’ll get rid of the misleading “no data found” message, and we’ll take care of report invocation from other page’s links, branches, with saved reports and the like.