Creating a Form in APEX to set Variables in a Query for an Interactive Report

Question

I am a relative APEX noob.

I'm running APEX 4.0 against a 10gR2 database.

I've written a query that takes a few inputs (two date fields, for start and end, and a text field for further filtering) and created a dynamic report out of it that works when I pull the input variables (:START_DATE, :END_DATE, :OFFICE) out of it or replace them with static values.

I want to create a form on a page that submits those values to the dynamic report page for use in the query to filter the results the user sees when he or she hits the report.

I'm not having much luck finding a good step-by-step example of this. I created a blank page with two Date Pickers and a LOV select dropdown, but am unsure how to best translate those values into the dynamic report.

Can somebody point me at the right documentation for this?

Answer 1

The following was developed using Apex 4.1 but apart from some cosmetic changes the principles should be the same.

The data comes from the standard scott.emp schema.

Overview

This is page 1, the user can enter an empno and\or a hiredate.

When submit is pressed the following report on a different page is displayed:

How it works

On page 1 I have created the three items shown. The text items are called them P1_EMPNO, and P1_HIREDATE. The action for the button is "Submit Page"

Still on page 1, create a branch with the following values:

This branch navigates to page 2 (which is yet to be developed) and sets the values of items on page 2 with the values from page 1.

Create a new page, in this example this will be referred to page 2.

On page 2 create a new interactive report using the following query:

select e.* 
from emp e

Next create two text items in the same region as the report and call these :P2_EMPNO and :P2_HIREDATE. I have found it useful to show these items during development so you can see that the correct values are being passed through to the page. You can always set them as hidden once you happy with the report.

Finally amend the query used by the interactive report to use the values supplied by page 1

Run the application.

Answer 2

You want to reference your page items in your query, which means you'll have to submit your page before your query will pick up the session state of them. What I do when I provide a small parameter form, is to put a button up there as well (i.e. labeled 'Query'), which does a submit.

In your report you can then reference your items. If for example you have 2 items P1_DATE_START and P1_DATE_END, your query could look like:

SELECT firstname, lastname, job
  FROM employees
 WHERE employment_start BETWEEN to_date(:P1_DATE_START) AND to_date(:P1_DATE_END);