Report query with optional parameters

I received a question today from a developer who wanted to write a single static SQL query that could handle multiple optional parameters – i.e. the user might choose to leave one or more of the parameters NULL, and they’d expect the query to ignore those parameters. This is a quite common requirement for generic reporting screens, and there are two different methods commonly used to solve it.

Their sample query, using bind variables (natch), never returned any rows if any of the bind variables were null:

WHERE job = :P_JOB
AND dept = :P_DEPT
AND city = :P_CITY

This is expected, of course, because “x = null” always evaluates to “unknown”, and this causes the rows to be omitted.

Option 1: add “OR v NOT NULL”, e.g.


Option 2: use NVL, e.g.

WHERE job = NVL(:P_JOB, job)
AND dept = NVL(:P_DEPT, dept)
AND city = NVL(:P_CITY, city)

If the columns in the table do not have NOT NULL constraints on them, Option #2 will fail to return rows that have NULL in the relevant column – regardless of whether the user parameter is null or not. This is because “job = job” will always be “unknown” if job is null. In this case, Option #1 must be used.

If the columns do have NOT NULL constraints on them, then both Option #1 and Option #2 will work just fine. However, given the choice I would use Option #2 in order to take advantage of the potential performance optimisation that Oracle 12 can do with these types of NVL queries. There is a 3rd option, which is identical to Option #2 except that it uses the COALESCE function instead of NVL – but I would avoid this option as it will not get the performance optimisation.

On the other hand, if any of the attributes is the result of a costly operation (e.g. a function call), I would always use Option #1 (“OR NULL”) instead, because the NVL does not use short-circuit evaluation to avoid multiple function calls.

If there is a mix of columns that have NOT NULL constraints and others that don’t, I don’t really see any problem with mixing the two methods, e.g. in the case where dept has a NOT NULL constraint but job and city don’t:

AND dept = NVL(:P_DEPT, dept)

Here’s a question for you to think about. What if the business rule states that the report should omit records where a column is null (i.e. the column may have nulls but they don’t want those records to ever appear in the report)? You may as well use NVL, e.g. in the case where dept has a NOT NULL constraint, but job and city don’t, but the report should omit records where job is null:

WHERE job = NVL(:P_JOB, job)
AND dept = NVL(:P_DEPT, dept)

You might argue that future developers might be confused by the above query; it’s not exactly clear whether the developer intended to omit the records with null jobs, or if they made a mistake. Code comments might help, but alternatively you might choose to make the rule explicit, e.g.:

WHERE job = NVL(:P_JOB, job) AND job IS NOT NULL
AND dept = NVL(:P_DEPT, dept)

If you feel strongly about this one way or another, please leave your comments below ūüôā

This topic is a reminder that when there are multiple possible solutions to a problem, the choice should not be taken arbitrarily; and we should avoid enshrining one choice in any standards document as the “one true way”. This is because the answer is often “it depends” – different options may be valid for different scenarios, and have advantages and disadvantages that need to be taken into account.

Further Reading

APEX Reports: One Link, Multiple Destinations

Every Interactive Report has an optional set of “Link” attributes that allow you to specify the location where the user should be redirected if they click a link next to a record in the report. You can choose “Link to Custom Target” and use the Link Builder to easily specify the target application, page, item values to pass, and other attributes.


What if the report combines different entities, and you need to direct the user to a different page depending on the type of entity? Or, if you need to direct the user to a different page with different attributes depending on the status of the record?

One method is to generate the URL in the report query using apex_page.get_url (APEX 5+) or apex_util.prepare_url (APEX 4 or earlier), or (God forbid) you could generate the url yourself using string concatenation.

A more declarative solution is to instead use APEX page redirects. This solution involves the following:

  1. Add some hidden items to the page to store the parameters for each target page;
  2. Add a Branch to the page for each target page; and
  3. Add a Request to the link to signal the page that a redirect has been requested.

Here’s an example. My page 550 has an interactive report which combines TI records with TRQ records (both of which have a very similar structure). If the user clicks on a TI record they should be redirected to p510 with the corresponding TI_ID, and if they click on a TRQ record they should be redirected to p305 with the corresponding TRQ_ID.

Here’s the link attributes for this report:


Notice that the page now redirects back to itself with the request set to “GOTO_TARGET”, along with the IDs required. My report query has been constructed so that every record will only have a TI_ID or a TRQ_ID, never both at the same time; so the link will ensure that only one of the P550_GOTO_xxx_ID values will be set.

The page then just needs two Branches: one for each target. Conditions on each branch ensures they only get activated if the request has been set, and the branch is selected based on which “GOTO ID” item has been set:



For a normal navigation to this report (e.g. from another page), the request should be blank (or some other value) so none of the Branches should be triggered.

For a relatively simple scenario like this, I like the declarative approach. Each branch can take advantage of the full range of APEX features such as the link builder, security conditions, build options, etc.

Note: this method works just as well for Classic report links as well.

The thing to be mindful of is that the order of the branches, and the condition on each branch, must be carefully selected so that the correct branch is activated in each situation. I’ve shown a simple example which works because I have ensured that only one of the ID parameters is set at the same time. If a record has both IDs, the condition on the first branch “GOTO_TARGET (TI_ID)” will evaluate to True and it will be used, regardless of what GOTO_TRQ_ID was set to.

If there were numerous potential destination pages, with a large number of parameters to pass, I might choose the apex_page.get_url method instead.


Show greyscale icon as red

I have an editable tabular form using APEX’s old greyscale edit link icons:


The users complained that they currently have to click each link to drill down to the detail records to find and fix any errors; they wanted the screen to indicate which detail records were already fine and which ones needed attention.

Since screen real-estate is limited here, I wanted to indicate the problems by showing a red edit link instead of the default greyscale one; since this application is using an old theme I didn’t feel like converting it to use Font Awesome (not yet, at least) and neither did I want to create a whole new image and upload it. Instead, I¬†tried a CSS trick to convert the greyscale image to a red shade.

I used this informative post to work out what I needed:

WARNING: Unfortunately this trick does NOT work in IE (tested in IE11). Blast.

Firstly, I added a column to the underlying query that determines if the error needs to be indicated or not:

select ...,
       case when {error condition}
       then 'btnerr' end as year1_err
from mytable...

I set the new column type to Hidden Column.

The link column is rendered using a Link-type column, with Link Text set to:

<img src="#IMAGE_PREFIX#e2.gif" alt="">

I changed this to:

<img src="#IMAGE_PREFIX#e2.gif" alt="" class="#YEAR1_ERR#">

What this does is if there is an error for a particular record, the class "btnerr" is added to the img tag. Rows with no error will simply have class="" which does nothing.

Now, to make the greyscale image show up as red, I need to add an SVG filter to the HTML Header in the page:

<svg style="display:none"><defs>
  <filter id="redshader">
    <feColorMatrix type="matrix"
      values="0.7 0.7 0.7 0 0
              0.2 0.2 0.2 0 0
              0.2 0.2 0.2 0 0
              0   0   0   1 0"/>

I made up the values for the R G B lines with some trial and error. The filter is applied to the buttons with the btnerr class with this CSS in the Inline CSS property of the page:

img.btnerr {filter:url(#redshader);}

The result is quite effective:


But, as I noted earlier, this solution does not work in IE, so that’s a big fail.

NOTE: if this application was using the Universal Theme I would simply apply a simple font color style to the icon since it would be using a font instead of an image icon.

Quick Pick in APEX Report

I have an Interactive Report that includes some editable columns, and the users wanted to include some “quick picks” on these columns to make it easy to copy data from a previous period. The user can choose to type in a new value, or click the “quick pick” to quickly data-enter the suggested value.


Normally, a simple page item can have a quick pick by setting the Show Quick Picks attribute on the item. This is not, however, available as an option when generating APEX items in a report.

To do this, I added code like the following to my report query: NOTE: don’t copy this, refer to ADDENDUM below

         ,p_rows => 1
         ,p_cols => 30
         ,p_item_id => 'f05_'||to_char(rownum,'fm00000'))
       || case when x.prev_ytd_comments is not null
          then '<a href="javascript:$(''#'
            || 'f05_' || to_char(rownum,'fm00000')
            || ''').val('
            || apex_escape.js_literal(x.prev_ytd_comments)
            || ').trigger(''change'')">'
            || apex_escape.html(x.prev_ytd_comments)
            || '</a>'
       as edit_ytd_comments
FROM my_report_view x;

This results in the following HTML code being generated:


In the report definition, the EDIT_YTD_COMMENTS column has Escape Special Characters set to No. This runs a real risk of adding a XSS attack vector to your application, so be very careful to escape any user-entered data (such as prev_ytd_comments in the example above) before allowing it to be included. In this case, the user-entered data is rendered as the link text (so is escaped using APEX_ESCAPE.html) and also within some javascript (so is escaped using APEX_ESCAPE.js_literal).

So, if the data includes any characters that conflict with html or javascript, it is neatly escaped:


And it is shown on screen as expected, and clicking the link copies the data correctly into the item:


This technique should, of course, work with most of the different item types you can generate with APEX_ITEM.

Recommended further reading:

ADDENDUM 6/2/2017

A problem with the above code causes this to fail in Internet Explorer (IE11, at least) – when clicking on the quickpick, the user is presented with a page blank except for “[object Object]”. After googling I found this question on StackOverflow and fixed the problem by moving the jQuery code to a function defined at the page level.

I added this to the page Function and Global Variable Declaration:

function qp (id,v) {

And modified the report query as follows:

         ,p_rows => 1
         ,p_cols => 30
         ,p_item_id => 'f05_'||to_char(rownum,'fm00000'))
       || case when x.prev_ytd_comments is not null
          then '<a href="javascript:qp(''#'
            || 'f05_' || to_char(rownum,'fm00000')
            || ''','
            || apex_escape.js_literal(x.prev_ytd_comments)
            || ')">'
            || apex_escape.html(x.prev_ytd_comments)
            || '</a>'
       as edit_ytd_comments
FROM my_report_view x;

Target=_Blank for Cards report template

cardsreport.PNGI wanted to use the “Cards” report template for a small report which lists file attachments. When the user clicks on one of the cards, the file should download and open in a new tab/window. Unfortunately, the Cards report template does not include a placeholder for extra attributes for the anchor tag, so it won’t let me add “target=_blank” like I would normally.

One solution is to edit the Cards template to add the extra placeholder; however, this means breaking the subscription from the universal theme.

As a workaround for this I’ve added a small bit of javascript to add the attribute after page load, and whenever the region is refreshed.

  • Set report static ID, e.g. “mycardsreport”
  • Add Dynamic Action:
    • Event = After Refresh
    • Selection Type = Region
    • Region = (the region)
  • Add True Action: Execute JavaScript Code
    • Code = $("#mycardsreport a.t-Card-wrap").attr("target","_blank"); (replace the report static ID in the selector)
    • Fire On Page Load = Yes

Note: this code affects all cards in the chosen report.

Report Link: Save before Navigation

“We always click ‘Apply Changes’, then we click the button we actually wanted” – a user

Typically an Apex report with an “Open” or “Edit” icon¬†will simply do an immediate navigation to the target page, passing the ID of the record to edit. When the user clicks this link, the page is not submitted and the user is instead redirected.

It’s easy to quickly build large and complex¬†applications in Apex but it’s also very easy to build something that confuses and disorients your users. They only need one instance when something doesn’t work how they expected it to, and they will lose trust in your application.

For example: if most buttons save their changes, but one button doesn’t, they might not notice straight away, and then wonder what happened to their work. If you’re lucky, they will raise a¬†defect notice and you can fix it. More likely (and worse), they’ll decide it’s their fault, and begrudgingly accept slow and unnecessary extra steps as part of the process.

You can improve this situation by taking care to ensure that everything works the way your users expect. For most buttons in an Apex page, this is easy and straightforward: just make sure the buttons submit the page. The only buttons that should do a redirect are those that a user should expect will NOT save the changes – e.g. a “Cancel” button.

For the example of an icon in a report region, it’s not so straightforward. The¬†page might include some editable items (e.g. a page for editing a “header” record) – and if the user doesn’t Save their changes before clicking the report link their changes will be lost on navigation.

To solve this problem you can make the edit links first submit the page before navigating. The way I do this is as follows (in this example, the report query is on the “emp” table:

    1. Add a hidden item P1_EDIT_ID to the page
      • Set Value Protected to No
    2. Add something like this to the report query (without the newlines):
         set:{''P1_EDIT_ID'':''' || emp.rec_id || '''}
        })' AS edit_link
      • Set this new column to Hidden Column
    3. Modify the edit link Target:
      • Type = URL
      • URL = #EDIT_LINK#
    4. Add a Branch at point “After Processing”
      • Set the Target to the page to navigate to
      • Set the item for the record ID to &P1_EDIT_ID.
      • Set the Condition to the following PL/SQL Expression:
      • Make sure the branch is evaluated before any other branches (at least, any others that might respond to this request)
    5. Modify any existing Processing so that the request SAVE_EDIT_ROW will cause any changes on the page to be saved.

You can, of course, choose different item names and request names if needed (just update it in the code you entered earlier). For example, to make it work with the default Apex¬†DML process you might need to use a request like “APPLY_CHANGES_EDIT_ROW”.

Now, when the user makes some changes to the form, then clicks one of the record Edit links, the page will first be submitted before navigating to the child row.

Adding buttons to Apex pages is easy. Making sure every last one of them does exactly what the user expects, nothing more, and nothing less, is the tricky part!

Checkbox Item check / uncheck all

If you have an ordinary checkbox item based on a list of values, here is a function which will set all the values to checked or unchecked:

function checkboxSetAll (item,checked) {
 $("#"+item+" input[type=checkbox]").attr('checked',checked);

For example:

checkboxSetAll("P1_ITEM", true); //select all
checkboxSetAll("P1_ITEM", false); //select none

It works this way because a checkbox item based on a LOV is generated as a set of checkbox input items within a fieldset.

Note: If it’s a checkbox column in a report, you can use this trick instead: Select All / Unselect All Checkbox in Interactive Report Header

Declarative Tabular Form dynamic totals

A common APEX project is to take a customer’s existing spreadsheet-based solution and convert it more-or-less as is into APEX. I’ve got one going at the moment, a budgeting solution where users need to enter their budget requests. They currently enter their requests into an XLS template file which generates subtotals and totals for them.

To do this in APEX I’m going to use a tabular form, and to do the subtotals I’ll use jQuery in a way not too dissimilar to that I described earlier.

Here is a mockup of the screen so far:


There are column totals that need to be added up and updated dynamically (indicated by the green arrows) as well as subtotals within each row (indicated by the red arrows).

I started by looking at the generated items, getting their ids (e.g. “f09_0001” etc) and writing the jQuery code to detect changes, add them up, and put the totals in the relevant items. I then started repeating this code for each column, and thought “hmmm”.

There were two problems with this approach that I could foresee:

  1. The generated ids in a tabular form can change if the structure of the query changes  Рe.g. what was f08 + f09 => f10 might change to f09 + f10 => f11
  2. I was aware of another form that I would need to build, with a similar structure except that there will be two sets of “Jan-Jun” + “Jul-Dec” columns, each with their own subtotal.

I wanted a more declarative solution, so that the heavy lifting will be done in one set of generic javascript functions, and I simply need to put attributes in the relevant columns to activate them. This is how I’ve approached this:

  • Create the tabular form as usual (mine is based on an APEX Collection) and remove the standard DML processes, replaced with my own that calls APEX_COLLECTION instead.
  • Create a standard report that generates the total items by calling APEX_ITEM.text, with p_attributes=>'data-total="x"' (with a different “x” for each column, e.g. year1).
  • Set the Static ID on the tabular form region (e.g. tabularform).
  • Set Element Attributes on the Jan-Jun column to data-cell="year1" data-col="year1_jan_jun", similarly for the Jul_Dec column.
  • Set Element Attributes on all the Year columns in the tabular form to data-col="yearx", where x is 1..5.
  • Set Element Attributes on the total for the first year to data-subtotal="year1".

The following is the query for the totals report region:

select APEX_ITEM.text(1, TO_CHAR(SUM(year1_jan_jun),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1_jan_jun"') as year1_jan_jun
      ,APEX_ITEM.text(2, TO_CHAR(SUM(year1_jul_dec),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1_jul_dec"') as year1_jul_dec
      ,APEX_ITEM.text(3, TO_CHAR(SUM(year1_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1"') as year1_total
      ,APEX_ITEM.text(4, TO_CHAR(SUM(year2_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year2"') as year2_total
      ,APEX_ITEM.text(5, TO_CHAR(SUM(year3_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year3"') as year3_total
      ,APEX_ITEM.text(6, TO_CHAR(SUM(year4_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year4"') as year4_total
      ,APEX_ITEM.text(7, TO_CHAR(SUM(year5_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year5"') as year5_total
from budget_collection_vw

So, to summarise: all the data-cell items get totalled to the data-subtotal item in the same row; and all the data-col items get totalled to the data-total item below the tabular form.

To do all the hard work, I’ve added the following code to my page’s Function and Global Variable Declaration:

function getSum (qry) {
  //get the sum over all items matching the given jQuery search criterion
  var t = 0;
  $(qry).each(function() {
    t += parseFloat($(this).val().replace(/,/g,''))||0;
  return t;

function updateSubTotal (item) {
  // update a row-level subtotal
  // the items to add up are identified by data-cell="x"
  // the item to show the total is identified by data-subtotal="x"
  var cell = $(item).data("cell") //get the data-cell attribute
     ,rn = $(item).prop("id").split("_")[1]
     ,t = getSum("input[data-cell='"+cell+"'][id$='_"+rn+"']");

  // we need to temporarily enable then disable the subtotal
  // item in order for the change event to fire

function updateTotal (item) {
  // update a column total
  var col = $(item).data("col") //get the data-col attribute
     ,t = getSum("input[data-col='"+col+"']");


In case you’re wondering, I’m re-using the formatMoney function here.

There’s a number of things happening here. On page load, we add a listener for changes to any input item that has a data-cell attribute; this calls updateSubTotal, which detects the row number for the triggering item, adds up all the values for any input item that has the same data-cell value; and puts the total in the input item with a matching data-subtotal attribute.

We also have a listener for changes to any item with a data-col class; when these are changed, updateTotal adds up any item with the same attribute, and puts the total in an item with attribute data-total.

The jQuery selector [id$='_"+rn+"'] makes sure that the row-level code only finds items ending with the given row number (i.e. '*_0001').

The benefit of this declarative approach is that it is much easier to re-use and adapt.

EDIT: fixed the change trigger so that I¬†don’t need to call updateTotal from updateSubTotal.

BIG checkboxes

Getting older, it’s getting harder to see and click those tiny checkboxes…



input[type=checkbox] {
/* Double-sized Checkboxes */
-ms-transform: scale(2); /* IE */
-moz-transform: scale(2); /* FF */
-webkit-transform: scale(2); /* Safari and Chrome */
-o-transform: scale(2); /* Opera */


CAN YOU SEE THEM NOW? Ah, good. That’s all right then.

Brought to you by dept-of-coding-by-copy-and-paste.

Refresh APEX Calendar

Sometimes¬†it’s the simple little things that can add polish and make your Apex application shine. One simple little thing that you can do is add a Refresh button to improve the usability of your¬†Apex 5 calendar.¬†This¬†makes it easy for the user to see recent changes on the database, e.g. if events had been added or changed since the page had last been loaded.

  1. Set the Static ID on the Calendar region (e.g. “eventscalendar“)
  2. Add an Icon button (Button Template = “Icon”) to the calendar region
  3. Set the button’s¬†Static ID (e.g. “refreshbutton“)
  4. Set Icon CSS Classes to “fa-refresh
  5. Set Action to “Defined by Dynamic Action”
  6. (optional) Set Template Option -> Style to “Remove UI Decoration”
  7. Add a Dynamic Action to the button, Event =¬†“Click”
  8. Set Fire on Page Load to “No”
  9. Add a True Action “Execute Javascript Code” with the code below:

This calls the refetchEvents method of the FullCalendar object. Replace the “eventscalendar” part of the id with whatever static ID you set on the Calendar region in step #1.

Now, to add a bit of pizzazz you can get the refresh button icon to spin while the calendar is being refreshed. To do this, change the dynamic action code to this instead:

$("#refreshbutton span.t-Icon").addClass("fa-spin");
window.setTimeout(function() {
  window.setTimeout(function() {
    $("#refreshbutton span.t-Icon").removeClass("fa-spin");
  }, 1000);
}, 50);

This code starts the refresh icon spinning before invoking refetchEvents, then stops the icon spinning after it has completed. Note that these are done via timeouts (otherwise the icon isn’t repainted until after the entire javascript function has completed). I added a wait of 1 second prior to stopping the spinning because most of the time the refresh is too quick to notice the spinning effect.

You can, if it makes sense in your case, also make the calendar automatically refresh itself periodically, using some simple javascript: add the following function to the page Function and Global Variable Declaration:

function refreshCalendar() {
  $("#refreshbutton span.t-Icon").addClass("fa-spin");
  window.setTimeout(function() {
    window.setTimeout(function() {
      $("#refreshbutton span.t-Icon").removeClass("fa-spin");
    }, 1000);
  }, 50);

Then add this to start the timer in the page attribute Execute when Page Loads:

var periodicrefresh = setInterval(function() {
                                  }, 30000);

In this example, I’ve set the timer to go off every 30 seconds. Not only does it refresh the calendar, but the user gets feedback on what’s going on because the refresh button icon is spinning. Be careful not to set the timeout too low, or else your database could get¬†very busy!

The function I’ve declared can now also be reused by the button’s dynamic action, so I can replace the DA javascript with simply: