I have this table with a column of type Date called "Creation_Date" that contains the creation date for each row:

 -------------- -------------- --------------- 
| TaskName     | Validated_By | Creation_Date |
 -------------- -------------- --------------- 
| Task A       | Jim          | 12/1/2022     |
| Task B       | John         | 12/1/2022     |
| Task C       | Jill         | 12/1/2022     |
| Task D       | John         | 11/30/2022    |
| Task E       | John         | 11/30/2022    |
| Task F       | Bill         | 11/30/2022    |
| Task G       | Goerge       | 11/29/2022    |
| Task H       | George       | 11/29/2022    |
 -------------- -------------- --------------- 

I want a classic report for this table in Oracle Apex to appear to the user like this:

12/1/2022 >
 -------------- -------------- 
| TaskName     | Validated_By |
 -------------- -------------- 
| Task A       | Jim          |
| Task B       | John         |
| Task C       | Jill         |
 -------------- -------------- 

And when they click on the arrow next to the date the rows for the previous day show up:

< 11/30/2022 >
 -------------- -------------- 
| TaskName     | Validated_By |
 -------------- -------------- 
| Task D       | John         |
| Task E       | John         |
| Task F       | Bill         |
 -------------- -------------- 

And when they press again, the rows for the day before that show up, etc:

< 11/29/2022 >
 -------------- -------------- 
| TaskName     | Validated_By |
 -------------- -------------- 
| Task G       | Goerge       |
| Task H       | George       |
 -------------- -------------- 

Is there a built-in way in Oracle Apex to do this for a Classic Report (or for an Interactive Report, doesn't matter) ?

CodePudding user response：

As @Scott statet, nothing built in but this shouldn't be that hard to code (for classic report/interactive report). Make sure you have an item P1_DATE that holds the date for which the rows are shown.

--- create test data
CREATE TABLE test_data (task, name, dt) AS 
(

SELECT 'Task A','Jim',    TO_DATE('12/1/2022','MM/DD/YYYY') FROM DUAL UNION ALL     
SELECT 'Task B','John',   TO_DATE('12/1/2022','MM/DD/YYYY') FROM DUAL UNION ALL     
SELECT 'Task C','Jill',   TO_DATE('12/1/2022','MM/DD/YYYY') FROM DUAL UNION ALL     
SELECT 'Task D','John',   TO_DATE('11/30/2022','MM/DD/YYYY') FROM DUAL UNION ALL    
SELECT 'Task E','John',   TO_DATE('11/30/2022','MM/DD/YYYY') FROM DUAL UNION ALL    
SELECT 'Task F','Bill',   TO_DATE('11/30/2022','MM/DD/YYYY') FROM DUAL UNION ALL    
SELECT 'Task G','Goerge', TO_DATE('11/29/2022','MM/DD/YYYY') FROM DUAL UNION ALL    
SELECT 'Task H','George', TO_DATE('11/29/2022','MM/DD/YYYY') FROM DUAL 
);

Report

Report source would be:

SELECT task, name, dt FROM test_data WHERE dt = TO_DATE(:P1_DATE,'MM/DD/YYYY');

Previous button configuration:

• Page Item P1_DATE_PREV.
• Source:

SELECT MAX(dt) FROM  test_data WHERE dt < TO_DATE(:P1_DATE,'MM/DD/YYYY');

• To hide button if there is no previous date: Add Server side condition of "rows returned"

SELECT 1 FROM  test_data WHERE dt < TO_DATE(:P1_DATE,'MM/DD/YYYY');

• The "previous" button would redirect to Page 1 and set P1_PAGE to P1_PAGE_PREV

Next button configuration:

• Page Item P1_DATE_NEXT.
• Source:

SELECT MIN(dt) FROM  test_data WHERE dt > TO_DATE(:P1_DATE,'MM/DD/YYYY');

• To hide button if there is no next date: Add Server side condition of "rows returned"

SELECT 1 FROM  test_data WHERE dt > TO_DATE(:P1_DATE,'MM/DD/YYYY');

• The "next" button would redirect to Page 1 and set P1_PAGE to P1_PAGE_NEXT