Highlight cell background in APEX report

It seems a very common question in APEX is how do you highlight the background of cells within reports?

There is a heap of information out there, and some of it is becoming dated so I thought I'd offer what seems to be an elegant solution - particularly when you're not targeting a specific column.

We can thank Tyler Muth for an APEX 3.x compatible solution that highlights the text within the column. He described how to use the HTML Expression column attribute - I still use this frequently today.

There are a few solutions that use report templates, but they can be a little fiddly and I don't find them as flexible.

Jari Laine brings us into the APEX 4.x world using jQuery within a dynamic action.

Then I found this stackoverflow post by Tom Petrus (who has also assisted me with jQuery on the OTN forums), which I thought I could adapt for my purposes.

Since I wanted to highlight cells with certain values regardless of column/row, I defined a classic report with a static ID of 'pivot' with the PIVOT sql found in this post.

I added this to the "Execute when Page Loads" page attribute - it could utilised within a dynamic action, if required.

$("#report_pivot tbody tr td").each(function(){
   if ($(this).attr('headers') != 'TOT') {
     if (parseInt($(this).text()) < 1000)
        $(this).css({"background-color":"SandyBrown"});
     else if(parseInt($(this).text()) > 2000)
        $(this).css({"background-color":"lightgreen"});
   }
});

I wanted to ignore the 'Total' column, so I found the first IF statement satisfies that easier than a CSS exclusion clause.
This block could be adapted to do all sorts of things - for instance, I've cleared out the cells in other reports where the value equals 100.

I also added this as inline CSS just to border all the report cells.

table.uReportStandard>tbody>tr>td {
  border: 1px solid #ddd;
}

Here is a screenshot of the final output.

Classic Report with cell highlighting

Run the demo to see it in action. Note all under 1000 are orange, and above 2000 are green. This is done after the page is generated.

Ubuntu Milestones - Oracle

For many, many years I've yearned to move away from windows. Everyone has their reasons, some have motivation, I finally had opportunity to make some baby steps.

I needed to set up an environment using APEX 3.2, and I had an old XP laptop that I finally moved my ipod synch off so I figured it was free to experiment with. I was already running it with dual boot, but I never pulled my finger out to restart & play around with the Ubuntu half.

Earlier in the year I experimented a little with Ubuntu using VirtualBox on my existing Windows7 laptop - I've even drafted a blog post rambling about my experience, but I've got some fresh thoughts with my recent jump in the deep end.

First impressions - it's an OS worth me pursuing.

It's certainly a cultural change. Of course, you need to be able to do many of the same type of operations - move files, close windows, setup Oracle... but to use a balinese catch phrase - it's same same, but different.

Installing Oracle

I was a little bit of a cowboy with this - while I was keeping note of the websites that I was using as a reference, I didn't take note of each command I performed. This was going to be a temporary db, and I figured this first attempt justified a little gun-ho action.

Besides, my ultimate plan is to use Virtualbox to run different environments in their own confined buckets. This old laptop is destined to be a sandpit.

I used this Linoob address to help install the db. In the end I'm pretty sure the apt-get just opened up the software centre.

Oracle in Ubuntu Software Centre

I think it's a selling point for this particular distribution of *nix based systems, installing software is just like searching Google Play or the Apple Store - they aren't the scary domain of sysadmins anymore.

Setting up SQL Developer was a little more tricky since there is no download available specifically for Ubuntu. Instead, there is a package builder for .deb packages, so I still just needed to download the base file, run a few commands and it could be installed no problem.

Installing a JDK to support SQL Developer was a little more interesting. I used the manual option, which again is just a list of commands. I'm positive I haven't put the JDK folder in a 'typical' location, but that's the burn of learning.

Having to run commands like this in a way reminds me of the days of DOS, which you could take as no really progressing from the Windows environment of just clicking "Next" - but I don't see this as a con. I see it as liberation, a way to gain flexibility. Some of the commands seem foreign now, but I don't think it's far away from recognisable, then second nature.

All that being said, you could look at setting up SQL Developer in Ubuntu on par with setting it up in Windows. In the latter, I still need do download a set of files, extract, and on first run point it to the java.exe that I always need a few attempts with getting the path just right. In Ubuntu, I surprisingly got this path first go!

The only oops I did was download the latest and greatest JDK (7), which SQL Developer is not ready to support, though it ran anyway. I'll fix that later...

The closest I got to the OS was when I had to add my user account to the DBA group. I needed to do this before the database fired up. After an initial scare of making it all this way and not being sure why it wouldn't start - I found a pretty clear instruction on the OTN forum.

With all this gun slinging, I forgot to configure the DB.
/etc/init.d/oracle-xe configure

Configuring APEX

From here it was familiar territory in upgrading APEX from v2 to my target of v3.2.1

These commands I could run from SQL Developer
Upgrade
@apexins SYSAUX SYSAUX TEMP /i/

Load images to EPG (I wasn't game to attempt APEX Listener just yet)
@apxldimg.sql /home/scott

Unlock the public user account (I was doing this mostly from memory, after setting up APEX so often, I even remembered this one after my attempt to log in just stalled in the browser)
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

The only script that didn't want to run from SQL Developer was the one to change the ADMIN password (apxchpwd.sql)
Instead, I just pasted the code in the SQL worksheet and ran it from there.

Restarting the OS

After restarting, the database didn't restart (an option I chose), but the scripts in the menu did not work. Again, I think this just came down to me lacking understanding of some permission issues, and I found a command that worked for me.
sudo /etc/init.d/oracle-xe restart

Conclusion

I got up and running in a few distracted hours. While there was a few hurdles, there was never anything that really concerned me or had me stuck for too long. Google was my dear friend, but I'll keep perusing my Beginning Ubuntu Linux book.

Next I'll be looking for alternatives to some programs I regularly use in Windows.

Oracle APEX Techniques - Video Course

I'm happy to finally announce a project this little green pig was assisting me with last year - a video series on Oracle Application Express techniques, published thanks to Packt Publishing.
http://www.packtpub.com/content/oracle-apex-techniques/video

The course targets new to intermediate APEX developers who have a general understanding of SQL and PL/SQL, and is based on APEX 4.2.

The course is 8 sections of 5 videos, each about 3-4 minutes each. The videos are screencasts from my laptop, with my voice describing what's happening on screen - very similar to a webinar.

Check out this sample clip looking at Interactive Reports detail view.

After having read a number of technical books and heard from other authors regarding the work involved, I can certainly say it was an interesting project to be involved with. I would love to include many other techniques I've acquired over the past six months in addition to the wealth of information that's out there - but we only had a limited number of minutes available - they sure add up quickly!

So if you're starting to learn Oracle Application Express; or you've been using it for a while and you're wondering if you've missed anything useful - you might like to consider this video series. I hope you find it valuable.

Oracle APEX Techniques

Cheers,

Scott

Performance of APEX conditions

I often think about performance of conditional expressions. I'm not just talking about the expense of whatever the test is, but the difference between the condition types used.

I’ve heard about it from a few sources, and Tony Andrews blogged about it recently, but never really been able to/had a chance quantify it. Roel Hartman has also blogged about this after reading my tweet. He shows some figures, but difficult to rinse, repeat & measure like a PL/SQL process.

For example, while I’ve always encouraged this type of expression for consistency (which runs dynamically), particularly with new developers learning the differences between item substitution syntax.

This performs better (declaratively):

I don't see this second debug statement when using the declarative method.

The benefit is minor, but if multiplied by 50 separate events, that’s 1.5 seconds extra in rendering time.

Use the low-code options when you can, your database will appreciate it. You could even nest regions to get around multiple conditions.

INSYNC13 National Conference series

In 2013 the Insync Conference Series has expanded to include six capital cities within Australia, thereby bringing Insync to multiple east coast locations and incoporating the Oracle with 20:20 Foresight Perth leg.

The website has been updated, and the call for papers is coming to a close this Friday 24th May.

2013 Conferences

• Sydney 15-16 Aug
• Melbourne 19-20 Aug
• Canberra 27-28 Aug
• Brisbane 2-3 Sept
• Adelaide 5-6 Sept
• Perth 12-13 Nov

We've already received a range of abstracts, but I would love to see more submitted before the end of the week - including mine!

Keeping with tradition, the Perth conference is a couple months after the rest (after Oracle Open World), so we might be a little more lenient with late submissions.

We encourage abstracts for any of these fields, and this year we hope to bring even more workshops to the programme.

• DBA: Application | Development | Production
• Dev: PL/SQL | Forms & Reporting | Apex | ADF
• E-Business Suite/Fusion Apps: Financials | OBIEE | Hyperion | Primavera
• Middleware: Content | WL Server | BPM | BPEL | SOA
• Other Oracle Products that you believe are a current 'HOT TOPIC'.

If you've never visited Australia before, I recommend you take the opportunity to attend and/or speak at least one of these events. I would personally hope to see some first time international guests from the APEX space (hint hint!)

Use the #INSYNC13 or #AUSOUG tags to keep up with details on Twitter.

Scott Wesley
2013 Program Chair (Perth)

Analytic functions within Pivot statements

Today I found that SQL analytics go hand in hand with PIVOT statements.

I had percentage data pivoted by a certain column, but I needed to sort it by
the total across each row.

For the purpose of this demo I create a table with some random monthly data

create table my_data as 
select /*+ no_merge */ username, round(dbms_random.value(1,12)) mth, round(dbms_random.value(1,100),0) val 
from my_users connect by level < 4;

Now with the following query I transpose the monthly totals across as columns using the PIVOT.

select * from (
  select  sum(val) av, username, mth
     ,sum(sum(val)) over (partition by username order by null) tot
  from my_data
  group by username, mth
) 
pivot 
(avg(av) -- avg() because I didn't need to modify the value any further
         -- I'm open to other suggestions
  for mth in 
  (1  as jan
  ,2  as feb
  ,3  as mar
  ,4  as apr
  ,5  as may
  ,6  as jun
  ,7  as jul
  ,8  as aug
  ,9  as sep
  ,10 as oct
  ,11 as nov
  ,12 as dec
  )
)
order by tot desc

Note the highlighted line - an analytical statement that sums yearly total for each username. This was the simplest way I could determine
a) a way to determine that total
b) order the report by the user with the highest totals

I haven't used PIVOT queries too often yet, but I can see how analytical functions will be vital to providing, er, pivotal information.

APEX Easter fun

This isn't really an easter egg, just more of a mild bug that makes you think - hopefully a little more about your own applications.

If you type in "0" in the page navigation bar, it takes you to the typical global page / page zero for desktop.
In the component view, instead of displaying page processing and shared components, it displays application level components - items, process, computations; and an "About" region in the third column, above recently edited pages. Useful stuff...

If you type "-0", it renders page zero detail as if it were a normal page.

If you type "-1", or any other negative number,
- in 4.2.1 it takes you into a horrible "no data found" that's annoying to recover from, similar to if you type a positive page number that doesn't exist
- in 4.2.2 it handles it a little more elegantly, a little better than it did pre 4.2 - displaying no page detail.

Harmless "fun".