Dynamic action using date picker in APEX 5.0

2
votes

I want to use dynamic action using date picker in APEX 5.0 such that upon selection of start_date and end_date the interactive report should display the data for the date range selected.

I first created an interactive report in APEX and then created a page items with date pickers. In my database table, I have a column name time_stamp which is of datatype varchar2 with date format as YYYY-MM-DD-HH24:MI:SS.

My query for interactive report looks like below:

Select * from table1 where time_stamp between to_char(to_date(:p1_item,'YYYY-MM-DD-HH24:MI:SS'),'DD-MM-YY') and to_char(to_date(:p2_item,'YYYY-MM-DD-HH24:MI:SS'),'DD-MM-YY')

( where p1_item and p2_item are the page items for date pickers), the "Format Mask" attribute for the date pickers I have set to DD-MM-YY. Now I need to define a dynamic action to execute my interactive report SQL query based on the start_date and end_date selected. I need to further know the steps to create such a dynamic action and looking for the detailed steps for what attributes needs to set to achieve this.

2
oracle-apexoracle-apex-5
Hi, just to be clear, are you trying to make an interactive report that displays data based on the query above? – brenners1302
Yes I am trying to display an interactive report based on date range. – Mukul Sharma

2 Answers

4
votes

to get started, change your interactive report query to this:

          Select * from table1 
          where  
          (:p1_item is null and :p2_item is null) or time_stamp
          between to_date(:p1_item,'DD-MM-YY') and 
          to_date(:p2_item,'DD-MM-YY')

then create a dynamic action under your p2_item. Set its event to "change" then set it's true action to "submit page"

2
votes

A better way to get the data on the page to load would be to create a dynamic action under each date picker. For each date picker dynamic action set event to change; set it's true action to Execute Pl/SQL code and create another true action and set it to refresh.

For the Execute Pl/SQL code put in its code:

BEGIN
  null;
END;

and set the Page Items to Submit to :p1_item (and :P2_item for its corresponding dynamic action).

For the refresh action, set Selection Type to "region" then set region to your interactive report.

Your query should be as @Vance said:

  Select * from table1 
  where  
  (:p1_item is null and :p2_item is null) or time_stamp
  between to_date(:p1_item,'DD-MM-YY') and 
  to_date(:p2_item,'DD-MM-YY')

Doing it this way will be beneficial down the road as your pages become more complicated and you may not want to submit your entire page from a single date selection. It also is a little faster as you don't have to wait for the entire page to reload.