When building an Oracle APEX application, one of the most used regions is the Classic Report. This region allows us to configure a report that returns the results of any SQL query.

These results can be presented to the user in different ways, taking into account the intended result. One of the most used formats is, of course, the report in tabular format:

Among the various options for configuring a Classic Report is the possibility to change the format of the columns returned. One of these possibilities is the option of configuring one, several or all columns to the Link format. This link can redirect the user to a page within the same application, to a page in another application within the same workspace, or to a web link.

This configuration is very simple, and with 4 or 5 clicks, you quickly get a report with the same aspect as the previous one, but with all its columns defined as Link:

This link can be independent between the different columns, for example, the “Tipo” column can forward the user to a page in the current application, the “Plataforma” column to a page in another application and the “Componente” column to a page on the web like www.google.pt.

Problem:

And if only one link per column is not enough and what you want is something more dynamic, for example, if the “Tipo” column has the value “Boas práticas”, the destination of the link is different from the destination of the link of the value is “FAQ”?

This situation is not so simple. We must resort to other, more laborious alternatives, such as constructing declared links.

Option A – Use a Branch Process

Step 1: In the columns where you want a dynamic link, define the “Target” LINK as being of type URL and the destination as a Javascript function, in this case with the name “setItems.” In this function, the values of the different columns of the selected row are defined as arguments of the same. For this, the name of the column preceded and followed by the sign # is used.

This way, by clicking on a row in a column with this setting, the javascript function is called instead of the page redirect.

Step 2: Define the “setItems” function.

This function is a simple Javascript function, which uses the received values as arguments and sets them as the Session value of the respective Items (page variables with default value null) through the APEX function “apex.item (ITEM).setValue(VALUE)”:

Step 3: Create a Dynamic Action (DA) associated with the last Item to be set in the “setItems” function, in this case the “P532_TYPE”:

This DA is set to be triggered at the time this item changes its value, which will occur as soon as the line “apex.item(“P532_TYPE”).SetValue(type);” is executed:

Set the True condition of this DA with a Submit Page action:

This action simply submits our webpage, that is, reloads the page with the Items having already the new values assigned.

Note: It is important for the DA to be associated with the last item to be changed in the setItems function. As the values are assigned in the same order as declared in the function, we ensure that the DA is only triggered after all values have already been changed.

Step 4: Create a Branch type process (this type of process redirects the application to different pages):

Set the processing point as “After Submit” so that the process runs right after the page is submitted. Set the Behavior type to “Function Returning a URL (Redirect)”:

In this function, all the target possibilities that we intend for our report are defined according to the values of the Items and returning a URL to an APEX page (different from the “normal” URLs) with the function “APEX_PAGE.GET_URL”.

In short:

When clicking on a linkable column of our report, we call a Javascript function that will assign the column values of the selected row to items on the page. In doing so, we change the value of Item P532_TYPE, which triggers a DA that submits the page and loads it with these same items with the new values stored. When the page submission occurs, the Branch function is assigned where, using the new values of the Items, the destination page to which we will navigate is defined.

Option B – Define a hidden column in the report query with the link

Step 1: Add to the select query that feeds our report a column that stores our destination URL using the defined options:

Step 2: Hide this column from the user:

Step 3: Now that we have the desired link value saved in the URL column, we define that the content of this column is the URL to which we want to navigate when we click each line:

In short:

In each row of our report; we have a hidden column whose value is a dynamic URL, defined by the values of the other columns of the same row. When we select a report row, the chosen column invokes the link that is stored in the respective “URL” column.

Conclusion:

Oracle APEX contains several very useful options that allow us to create applications with powerful features that are easily configurable. However, we are not always able to replicate all the requirements of our applications with these same integrated options. When that happens, with some knowledge of HTML, CSS and Javascript/Jquery and some out-of-the-box thought, we can achieve our goals. These are just two examples of how to achieve functionality that is not yet integrated from the start with APEX. Another option, not described, is the construction of an additional page, whose only function is to receive the values of the report and redirect the user to the final page according to these same values. Option B is, at first glance, simpler than solution A. The problem is that it requires a change to the query of our report, which may have a greater impact on the performance of the application; in addition, some developers may find this solution less clean. None of the solutions presented are perfect, mainly because all intended cases have to be hardcoded. But for some cases, necessarily simpler, these two options can be a good alternative for those who need to navigate to different pages according to the value of a column within a Classic Report.