Tweaking Classic Reports

I like classic reports in Oracle APEX.

They're so versatile, and while it may not look it in this example, adjusting particular declarative settings can make a real difference in a small region displaying pivotal data.

And this still looks like a report. You should see what else they can do.

Left region is default settings, right region has a few options set

Base Behaviour

The classic report is straight out of the wizard, this example performs a cross join to inflate the data set.
select e.* from emp e cross join emp e2
where :P29_DEPT is null or e.deptno = :P29_DEPT

A where clause has been added to filter by Dept, if supplied.

Page Items to Submit

Note one of the most important properties in APEX - Page Items to Submit, in this case nominating the P29_DEPT item.

There is a Dynamic Action defined on Even Change for the item, which simply refreshes the nominated region.

Simple Dynamic Action

If we don't set Page Items to Submit on the region, then the database won't know about the change made on the browser. Any item specified in this list upon refresh will have the current value set in the browser sent to APEX session state (a key/value table in the database), so when the query binds the value, the database knows what the browser knows.

So when I select a department, the employee list refreshes to show the relevant department.

Make Left look like Right

This may seem like a long list, but it doesn't take long at all once you know where to click. I estimate 23 clicks, as of 19.2.

1. Modify Region properties
1. Change Template Options for region
1. Tick Remove Body Padding
2. Tick Show Region Icon
2. Add Icon: fa-list
3. Add Static ID: zhuzh
2. Modify Report properties
1. Change Template Options for report
1. Tick Stretch Report
2. Report Border Horizontal Only
2. Change Pagination Type to Search Engine.
3. Sometimes you may which to turn Heading Type from Custom to Off, or at least disable the sort (by adding order by to SQL, or disabling sort on columns)
3. Modify Item properties
1. Change Type to Radio Group
2. Change Number of Columns to 4 (something relevant to your list. Usually useful for items with small number of options)
3. Change template option Item Group Display to Display as Pill Button
4. Modify Page Property
1. Set Execute when Page Loads
   $('#P29_DEPT_Z_CONTAINER').appendTo('#zhuzh .t-Region-headerItems--buttons')
   This moves everything holding the radio group together, to a spot made for buttons in the region.
   Not something I do often, but can be an economic use of space.

Notes

We've found the search engine pagination style great for touch devices, but I tend to prefer the Display Position on the Left, and at the Top, or at least Top & Bottom.

Report Template Options offer a facility to hide pagination when all rows displayed, but I've never seen it consistently work as I expect, so I continue to use my own JS library call for that.

The static ID can be whatever you like, so long as it doesn't clash with other IDs on the page, such as item names.

See my next post for more detail on how I derived that line of JavaScript, and what you can do with the browser tools.

See a video on how to action this blog here.