Creating a form in APEX to set variables in a query for an interactive report

Question

I am relative apex noob.

I am running APEX 4.0 for a 10gR2 database.

I wrote a query containing several inputs (two date fields, for start and end and a text field for further filtering), and created a dynamic report from it that works when I pull input variables (: START_DATE ,: END_DATE ,: OFFICE) or Replace them with static values.

I want to create a form on a page that sends these values ​​to a dynamic report page for use in a request to filter the results that the user sees when he or she gets into the report.

I was not very lucky to find a good step-by-step example of this. I created a blank page with two Date Pickers and a LOV dropdown, but I'm not sure how best to translate these values ​​into a dynamic report.

Can someone point me to the correct 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 is taken from the standard scott.emp schema.

Overview

This is page 1, the user can enter empno and / or hire.

When you click the submit button, the following report is displayed on another page:

How it works

On page 1, I created the three items shown. Text elements are called P1_EMPNO and P1_HIREDATE. Button action: "Send page"

On page 1, create a branch with the following values:

This branch moves to page 2 (which has not yet been developed) and sets the values ​​of the elements on page 2 with the values ​​from page 1.

Create a new page, in this example it will be indicated on page 2.

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

select e.* from emp e 

Then create two text elements in the same region as the report and call them: P2_EMPNO and: P2_HIREDATE. I found it useful to show these elements at design time so that you can see that the correct values ​​are being passed to the page. You can always set them as hidden as soon as you are happy with the report.

Finally, modify the query used by the interactive report to use the values ​​shown on page 1

Launch the app.

Answer 2

You want to link to your pages in your request, which means that you will need to submit your page before your request picks up the session state from them. What I do when I provide a small form of the parameter is also to add a button there (ie Marked as "Query"), which does the sending.

In your report, you can link to your products. If, for example, you have 2 elements P1_DATE_START and P1_DATE_END , your query may look like this:

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