Refresh Apex 5.0 chart results with different dynamic actions on same SQL query

Question

I have created a bar chart on APEX 5.0 where my query looks like below:

select col1 as label,col2 as value  from table1 where :P3_NEW_1 = col3;

here: P3_NEW_1 the page item I have created of type "Select List".

The list of values in "Select List" page item are pre-populated in the dropdown using the "Shared component" type I have created and so far this worked fine and I am able to display the results of above query by passing the value through the page item select list.

Now I need to add 2 data pickers on the same Apex page such that I should now be able to filter the results using date picker through dynamic action. I know that using an additional button I can create a dynamic action but my problem is how do I modify the above query such that following should happen.

During the page load, once I select a particular value from the "Select List", it should display records based on the value selected from dropdown
This I already achieved using above sql query for the bar chart.
Once the bar char is displayed, I should next be able to filter and display the results using date ranges through date pickers.

Here my problem is my bar chart query is same but I need to now pass the start_date and end_date to the same above sql query which but I am not sure how to achieve this. If I add a button for dynamic action I need to select the report region which is the "bar chart" region and here my query needs modification.

Once the bar chart display the results, at the next step how do I filter the results by having the date filters with dynamic action on the same region where bar chart was displayed. How to achieve this?

Cristian_I Cristian_I · Accepted Answer · 2015-10-01T07:16:54

You can change the query to something like this:

SELECT  COL1 AS LABEL,
        COL2 AS VALUE
FROM TABLE1
WHERE :P3_NEW_1      = COL3
AND (:P3_START_DATE IS NULL
OR TO_DATE(TIME_STAMP,'YYYY-MM-DD-HH24:MI:SS') BETWEEN :P3_START_DATE AND NVL(:P3_END_DATE,SYSDATE));

Where :P3_Start_date and :P3_End_date are the Start and End date pickers and TIME_STAMP is your column where you store the date.

After modifying the query you can simply add a button where at Behavior>Action select Submit Page.

This way when you click the button, the page will be submitted and chart refreshed.

If you want to take your chart to the next level you can do a partial refresh on it. Here is a short video tutorial of partial refresh on a report but you can apply the same login on your chart.

Refresh Apex 5.0 chart results with different dynamic actions on same SQL query

1 Answers