Using Conditions with Column Templates

Posted on Posted in APEX, Oracle

Report templates can be a powerful feature for altering the way a report looks. They fall under two main Template Types: Generic Columns and Named Columns. This blog post covers Generic Columns and the ability we have to specify a condition for these columns.

With a Generic Column template, we are able to specify how an individual cell will display. We have four template definition placeholders. APEX will look at “Column Template 1” first and if there’s no condition or if the condition is met, it will use it. Otherwise, it moves on to “Column Template 2” and so on.

We’ll use the following SQL for a Standard Classic report.

select PRODUCT_ID
      ,PRODUCT_NAME
      ,PRODUCT_DESCRIPTION
      ,CATEGORY
      ,PRODUCT_AVAIL
      ,LIST_PRICE
 from PRODUCTS

Our goal is to highlight the rows for products that are not available any more (PRODUCT_AVAIL=’N’)
We start by making a copy of the current report template. You can see which one you’re using in the Report Attributes. I also like to see which one I use by opening the templates portion of the tree under the Shared Components column on the page designer.

all templatesChose a new name for your template:

Copy TemplateAssign this new template to the report (also under Report Attributes).

Assign template in Report Attributes
Assign template in Report Attributes

At this point, our report will still look identical. Lets get started with the changes.
Edit the template and add a new class, for example “highlight” to the column template. Also, change the Column Template Condition to “Used Based on PL/SQL Expression” with :PRODUCT_AVAIL = ‘N’

Edit Report Template
Edit Report Template

Notice how we’re able to use the name of our columns as bind values in the condition. This change that makes this template specific to a report that includes the mentioned columns. This is why I recommend you use a name that ties the template to the report in some way. Also notice how we add the class highlight and we don’t replace or change the other classes (like “data” and “rptCol”. This is needed so the look and feel of the report is not affected.

Then, if your template is alternating colors, “Column Template 2” should contain the template for rows with a class that will change the background. Usually a class name like dataalt.

#COLUMN_VALUE#

 

The Column Template 2 Condition can be set to “Use for Odd Numbered Rows”.

Finally, we need one last template with no condition as a catch all. It will be used when the other two conditions are not met.

Here’s the CSS that we want to include on the page for our highlight class:

td.highlight {
background-color: #FAFFBD;
}

Here’s the end result. Or visit a quick demo here.

Report with highlighted rows via Conditions.
Report with highlighted rows via Conditions.

In future these blog posts I will explore the very powerful named column templates.

About

Hi, I'm Jorge Rimblas. Father, husband, photographer, Oraclenerd, Oracle APEX expert, Oracle ACE, coffee lover, car guy, gadget addict, etc... I'm an APEX Tech Lead DRW. I have worked with Oracle since 1995 and done eBusiness Suite implementations and customizations. Nowadays I specialize almost exclusively in Oracle APEX.

6 Comments on “Using Conditions with Column Templates”

    • Hi Jeroen,

      In order to highlight certain columns you probably need a Named Column template or a Dynamic Action/JavaScript solution. It would depend on the requirement. For example, if it’s always cells on the same column(s) that need highlighting then the Named Column template will work great. If the approach is more complex it may require JS.

      Hey, feel free to elaborate.

      Thanks
      -Jorge

      Reply

      • Thanks for your reply Jorge. I cannot use a named column template because my report has to be dynamic (i.e. the number of columns is dynamic, not fixed). Will update here if I can get it done.

        Cheers
        Jeroen

        Reply

  2. Hi Jorge,
    thanks a lot for the blog post (and all other interesting content you’re sharing)! I’m fairly new to APEX, please excuse me if I’m asking a silly question.

    I managed to highlight some of the columns I wanted. I use the the condition “:HIGHLIGHT =’+'” and evaluate HIGHLIGHT in the query. Unfortunately, it only works on even rows, not on odd ones. When I inspect the elements, all that should be highlighted get the class=”t-Report-cell highlight”, it just doesn’t do the same for even and odd rows. No matter how I change the settings for “Alternating Rows” or “Row Highlighting” in the region’s attributes (if this setting even matters). I also tried with a second condition for even (and odd) rows and third as catch-all rule. I didn’t help. Which piece could I be missing?

    I’m working on APEX 20.2. The page in question is a faceted search with Classic Report. I copied the Standard Template, and use Universal Theme, Vita – Dark. On page level I set td.highlight as inline CSS.

    Cheers
    Reto

    Reply

    • Without looking at the exact problem, I’m fairly confident this is because there’s another CSS rule (from APEX) that’s taking precedence over your highlight rule. Usually it’s the “Alternating Rows” option, but in your case it may be the something else. I hate to recommend this because it’s overused, but in this case adding “!important” to your highlight rule should raise the precedence of your rule.

      td.highlight {
background-color: #FAFFBD!highlight;
}
      Reply

I love comments, write me a line