APEX gives us a lot of options when it comes to reports. First, and most obviously, there's the interactive report, which can give the user a tremendous amount of flexibility in how they process the data. This is well and good, and should be every APEX developer's first thought when designing a report (you can limit the report's flexibility, if you have reason to, but it's nice to let users decide for themselves). There's also the wizard report, which I haven't played with much yet. And then there's the lowly SQL report, which isn't very flexible…right?
Well, yes and no. As we all know, users can be given the ability to sort SQL reports, but not filter them; pagination is provided, but not the ability to turn the data into a graph, etc., etc. But (you knew that was coming, didn't you?) there is one tremendously powerful option available to SQL reports (but not interactive reports): named column templates.
Named column templates allow you to define how each record of the data is presented to the user. APEX's template editor refers to rows, but I think that should be changed; it leads the developer to think in terms of table and tr tags, which are rather limiting. With a named column template, you can use any HTML markup you want–for example, these blog posts are displayed as an SQL report with a named column template that wraps everything up in div tags; my CSS file takes care of the rest. Oh, and the links & blog roll box over in the sidebar? Yep, that's a named column template using
Another nice thing about named column templates is that you can put conditions on rows, beyond the basic odd/even breakdown. You're given four possible row templates. The links report uses three of them–one for rows that are marked as header rows, one for rows marked as separators (I may not keep that one), and one for the links themselves. Note that the first two templates don't show most of the fields in the report–that's ok. The one oddity I ran into is that the field values in the conditions are substituted in literally–so you have to use
'#FIELD#' = 'Y'
#FIELD# = 'Y'
for string fields. Dates could be even more problematic. One possible workaround would be to manipulate any fields you want to have conditions on in your query, using decode, nvl, etc., to return a numeric value which is easier to test, though this puts some of the display logic into your SQL, where it doesn't really belong. Ideally, of course, the APEX team will fix this oversight in an upcoming patch.