Reputation: 53
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.
Upvotes: 2
Views: 7481
Reputation: 41
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.
Upvotes: 2
Reputation: 897
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"
Upvotes: 4