How to call groupBy actions on Interactive Grid/Interactive Reports with JavaScript Dynamic Action in Oracle APEX?

Question

I am using Oracle APEX 23.1.0, and I have an interactive grid/interactive report region. I want to make a button that groups the data by some predetermined columns COL1 and COL2, and count the number of rows in each group. The problem is that I want to do all this with a JavaScript dynamic action that is triggered when some button I placed is clicked.

I have not been able to find any information in the documentation on how to find the possible actions or how to execute them. Is this possible?

Answer 1

For the sake of simplicity, lets take an interactive report. I have not tested this approach for a read only interactive grid.

These are the steps:

• Create a pipelined function that uses the function APEX_REGION.OPEN_QUERY_CONTEXT to retrieve the last query executed for the interactive report.
• Loop through the result set and store the group names / group count in a pl/sql table of records.
• Then loop through the table of records and pipe the group name / count
• in your dynamic action you'll be able to select from the pipelined function.

You have not mentioned what you want to do with the results so can't really tell you more. For an example on how to use a APEX_REGION.OPEN_QUERY_CONTEXT, check this blog.