reports

One of my projects at work called for a tabular form, but the users wanted to have access to a lot of information. We're talking over thirty–and that makes the form almost unusable, since the user constantly has to scroll back and forth to see all their information. Only four of the columns are editable; the others are informational only. Now, most of these columns are only marginally useful, so I convinced the users that we could move them into a modular pop-up window. The form now displays “just” thirteen columns (that's still fairly wide, if you ask me, but the nine display-only columns are always useful, so it makes sense to keep them). One of the display-only columns, through the magic of jQuery, includes a link to open a modular dialog with the other data. Well and good.

…but. (This wouldn't be an interesting post if there wasn't a but, right?) To help my users parse the data efficiently, I added an export to CSV link to the form. And, of course, they want to have all the data in the CSV, not just the columns that are displayed.

When you're editing the column attributes of a report or a tabular form, APEX gives you a few options that seem relevant to this: Show Column, Include in Export, and Display As. If you set Show Column to Yes and Display As to anything other than Hidden, the Include in Export flag properly controls whether or not the column appears in the CSV. But (there's that word again) if you set Show Column to No or Display As to Hidden, the column isn't visible in the report–or in the CSV, regardless of the Include in Export flag. We need to look elsewhere for a solution.

Luckily, there's a post by Jari Laine (nice guy, by the way; you can check out his APEX demo site here–there's lots of useful stuff on it!) on the Oracle forums that gives us the answer: set the column to display as normal, but give it a condition that causes it to only render when it's being exported to CSV. Set the Condition Type to PL/SQL Expression, and use this for the expression:

NVL(:REQUEST, 'MY_REQ') LIKE 'FLOW_EXCEL_OUTPUT%'

(Note that Jari's post has a semi-colon after the expression, but APEX throws an error if you include it). And, just like that, everyone's happy. Yay!

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

tags.

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'
```
instead of
```
#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.

Adding columns to CSV export

When a report should not look like a report