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.

APEX Component Settings for Switch

While I point out a 'hidden' setting for Switch items in APEX, I want to comment on two related item types found in Oracle APEX => radio buttons and checkboxes.

1) Radio button

I really think the standard Template Option for radio groups should 'Display as Pill Button'

It really provides a simple and effective UX when a small set of options are on offer. Touch devices can select the option as if it's a button, and mouse users only need one click, compared to a select list requiring two. And the click doesn't need to be precise for those tiny radio group circles. I wish the application builder adopted the pill button approach more often.

And it's real easy to hook a dynamic action on change of the radio group, perhaps to refresh a classic report with data limited to the selected option. (Don't forget to add page items to submit!)

2) Switch

The Switch item was the answer to a number of methods of deploying an "iPhone-like" on/off switch.

I actually wrote an (internal) item plugin to do just this, but I much prefer using the native option.

A frequent question relates to how switches are rendered. If you don't see what you expect, have a look in Shared Components -> Component Settings.

Application -> Shared Components -> Component Settings

Personally I prefer the 'Pill Button' look, for reasons above. I find the APEX Application Builder seems more washed out with all the Switches in the properties bar, compared to the more softened On/Off pill buttons. If I could change this at the builder level, I would.

The offerings will vary depending on your APEX version, and if the application's Universal Theme has been refreshed. 

Application level settings for plugins can also be found in Component Settings, in addition to built-in APEX feature tweaks. These can something you choose to review after an APEX version upgrade.

3) Checkbox

It seems the humble HTML checkbox will always be extant, regardless of the pain it brings to tabuler forms. However, it suffers the same precision requirement as the native radio group selections - though better deployments allow selection using the label as well.

Go with the Switch (as a pill).

What do you think?

Include new APEX templates in an older APEX instance

Have you seen that super awesome theme in the new APEX version, then wondered how long it will it be before your site upgrades so you can actually use it?

What if I told you that your current version could be retrofitted to use that template?

I really like the look of the Content Row template, I think that's going to serve many developers good purpose.
Sure, I could create something similar now, but if I do as much as I can the same, then it should grease the wheels come upgrade time.

Content Row 18.2 vs 19.2

To make this happen, I

1. Downloaded APEX 19.2
2. Copied the /theme_42/1.4 folder to my 18.2 instance
3. Created the report template, including template options
4. Added the region component in my app

The first two steps are all about making the relevant CSS available to your instance.

After I had the /1.4 folder in place, I thought I would need to increment the Theme folder, but that just introduced other issues.

Instead, I just referenced the ContentRow.css file in the pages I wanted to use it.
This is the path where you can find it, so it can be placed wherever you like on your older instance.
/i/themes/theme_42/1.4/css/core/ContentRow.css
Alternatively, you could include this for all pages in the User Interface attributes.

Then I needed to create the named column (row template) in my 18.2 application, details of which I just copied from a 19.2 instance.

Content Row syntax

I can even transfer the template options, so I started transferring from details on the page
You can also use this query to find instances within my 19.2 application.

select name, display_name, display_sequence, css_classes, group_id
  ,(select display_name
    from APEX_APPL_TEMPLATE_OPT_GROUPS
    where template_opt_group_id = group_id) group_name
  , help_text
from APEX_APPL_TEMPLATE_OPTIONS 
where application_id = 32532
and report_template = 'Content Row'
order by display_sequence

Then I created the template options in 18.2.

Add Template Option

And sure enough, they become available on my 18.2 region component.

Page Designer outcomes

Due to the differences in availability of template option groups, some manifest a little differently, but I personally prefer a one click checkbox to the more fiddly select list.

All that said, I'm not sure I'm entirely sold on the small screen format, though maybe thing will change when I get more realistic data in there.

Content Row - mobile

Now I'm sure when I change over to use the actual template when I'm in 19.2, I'll need to re-apply the template options - but Shakeeb likes the idea of retaining shared template options, so you never know.

This was all done in 18.2. If you're on an earlier version, your mileage may vary.

If you're not using the Universal Theme, then you're probably not going to get very far.

Oracle APEX Radio Group null option fix

APEX 5.1 introduced a template option that transformed radio groups into pill buttons.

I love this concept, it makes for a pleasant UI - an easier target for mouse clicks and finger taps.
I liked it so much I tried to do this myself prior to 5.1.

In 18.2 (fixed in 19.1), there's an issue with the way the null option presents for these radio groups - it's not in the same row.

Marko mentions a CSS fix for this problem in his post, but I find it has some side effects.

.apex-item-grid.radio_group{
  display:flex;
}

Stretched items are no longer stretched, and it ignores the Number of Columns attribute.

I went to inspect the generated HTML, and compare it to the 19.1 working solution, expecting to find some difference in CSS, but I quickly realised the null options is just generated in the wrong level on the tree.

You can also test this manually by using the in the browser tool to drag/drop that element to its siblings in the grid-row.

This means the problem can be solved with JavaScript when the page loads.

$('.apex-item-grid > .apex-item-option').each(function(){
  $(this).prependTo($(this).next('.apex-item-grid-row'));
});

This JavaScript identifies any radio options that are immediately below the grid, instead of being within the grid-row. Then for each instance, it moves that element to the next sibling, which is the grid-row.

Using appendTo() will push it to the end of the list.

And this solution honours the stretch option.

Is there a better way?
Other than upgrade to 19.x ;p

References

jQuery Cheatsheet

APEX Classic Report as Alerts

I love classic reports.
You can make your data look like anything, out-of-the-box, as previously highlighted by Christina and Carsten.

When you create a classic report, you can select different Report templates via the region attributes. I use the Cards and Alerts a fair bit, allowing me to produce data driven content, instead of whatever I type in the application builder.

This has been the general premise of APEX templates since the beginning. We define a query, and feed those columns to the template, where the values are substituted in.

Alerts Report Template

We can combine this template with the following query, containing matching column aliases.

select 
  'Alert' as alert_title
 ,'Be alert, not alarmed.' as alert_desc
 ,null as alert_action
 ,'danger' alert_type 
 ,'fa-user-woman' alert_icon
from dual

This doesn't need to be from dual, it can be any query you like, as long as it contains those column aliases. Multiple rows will show multiple alerts, down the page. The alert_type can be any of the pre-defined template options for that attribute.

Over time these templates get upgraded, and now we operate with the Universal Theme, we gain access to improvements after verifying the theme.

For instance, APEX 5.1 introduced #CARD_COLOR# attribute to limit the manual CSS required to make it happen.

What the Alerts Report template still lacks is the ability to customise the icon (as at 19.1).

We can customise the icon with a declarative Alert Region, by setting the Template Option Alert Icons to 'Show Custom Icons', and specific the icon in the Icon field.

If we want to customise the output of a classic report displayed as an alert, we can use an After Refresh Dynamic Action that executes JavaScript on page load to rejig the HTML of the report.

// replace icon in template, since we can't do it in SQL yet
$('#acting .t-Alert').removeClass('t-Alert--defaultIcons').find('.t-Icon').addClass('fa fa-user-woman')

Here I find the region I gave a static ID of 'acting', then removes the default icons from the component with the t-Alert class.
It then finds the t-Icon class to extend with my chosen icon.
This transforms it to exactly what a declarative Alert region looks like.

This example was built for a report with only one record, but hopefully gives you an idea of the potential these type of events have. Check out similar example here.

Happy APEXing.

Hide Pagination if one page results - Oracle APEX

I love this period of development not long after an APEX upgrade, where I discover all these little improvements that will help us out. Stuff that's not quite noteworthy in the wonderful read that is the new features guide (that's not sarcasm. Read it. Now.)

Recently I made an observation regarding a handy new template option, one that hides pagination if there is only one page of records. I've had a bit of jQuery doing this for a while, and I thought I had blogged about it already, but it was a similar feature regarding no data found.

Sure enough, it was still sitting in an email labelled 'blog post'. Now I could write this off, like quite a few others where a built-in feature got released before I got around to writing up a post. But I decided to post about this one anyway, in part because it may take some time for some to reach 18.2, and I think it's part of an interesting conversation regarding the evolution of the product.

Some of the improvements in each APEX release are likely the result of
- a tweet about a feature
- a forum post asking about a quirk
- a conversation at a conference, likely while at least one person was holding a beer
-  a request on apex.oracle.com/vote

If you notice something odd, or think something you've done would help everyone, put it out there.  Chuck it on the forum. Make it your first blog post. You'll help the community, and reap the benefits yourself when the time comes to upgrade to that version with some delicious fruit.

--***

So, that being said, here was the template option, available in APEX 18.2, but only to existing applications only after refreshing your theme.

Classic Report Region Template Option

Applied to a classic report, it will hide certain pagination styles with there is less records than that specified per page on that report.

This will save me creating a dynamic action on the region, calling a small bit of jQuery I have in a library. It's also something I figured could be practice for turning into a plug-in, but I never got there, either.

The trouble is, this template option doesn't appear to work with my favourite pagination type (another reason for the post).

I like to use the Search Engine pagination style, on the top left - available to the user regardless of vertical/horizontal width of the region. I have chapter in my book describing how to turn standard HTML links in this search engine pagination for a legacy theme into buttons.

Trouble is, it seems a little superfluous when there's only one page of results. And it takes up vertical space. Hence the idea of the template option.

With a little JavaScript invoked in a dynamic action after refresh on the region - this is the default dynamic action when you right-click to create dynamic action on a region in page designer. Kudos to the APEX developers responsible for those little touches.

Here's the code I used to do the job.

if ($(this.triggeringElement).find('.t-Report-paginationText').children().length == 1
  && $(this.triggeringElement).find('.t-Report-paginationText').children().text() == 1)
    $(this.triggeringElement).find('.t-Report-pagination').hide();

A few points:

• this.triggeringElement refers to the region, since that's what the dynamic action is firing off
• find() is looking for page elements with the t-Report-paginationText class, in this case, the button group
• children() counts how many buttons, so true if there is only one
• I added the second condition when I realised it was hiding results when there were 11 pages of results, so I check if the button contains the text "1"
• then it applies .hide() to the pagination group

Note, this is designed for Search Engine pagination in the Universal Theme, and the template option probably works with a different mechanism. There might be a clever way to solve this with CSS?

The real solution also belongs in a library so it can be re-used. I did it like this once, but I'm sure it could be neater.

function hide_pagination_iff_one_page (p_region) {
  if ($('#'+p_region+' .t-Report-paginationText').children().length == 1
  && $('#'+p_region+' .t-Report-paginationText').children().text() == 1)
    $('#'+p_region+' .t-Report-pagination').hide();
}

I would then put this in the after refresh dynamic action. It passes the ID of the region triggering the action, ready for embedding in the selector.
hide_pagination_iff_one_page($(this.triggeringElement).attr('id'));

So my open question is, how would you tidy such JavaScript? how would you place it in a library?
I'm still getting the hang of namespaces, and how to organise our stubs.

Make Radio Group look like UT Buttons

It's pretty easy to convert links in APEX reports to use the Universal Theme button look & feel, as I described here

http://www.grassroots-oracle.com/2015/12/tutorial-include-action-button-in-report.html

We can apply the same technique to radio groups, but it requires a little more work than just adding some classes, but nothing like jQuery mobile radio fiddles.

It took me a few goes to get the correct string in the correct APEX attribute to make the individual buttons look & behave the way I imagined, so I thought I'd share the journey. Show my working, so to speak.

First, create a radio item:

A humble radio group

Then set 'Option HTML attributes' to add template option classes, just like I did for the report action buttons. This applies the string to each option, rather than the entire item.

class="t-Button t-Button--simple"

If we check the page, the radio group should have button boxes surrounding the text labels.

Radio group with button class

To work out what CSS selector I needed to hide those radio buttons, I used Chrome's Inspect element tool (right click a radio button) so I can see how the HTML is constructed.

Inspect Element is the best browser tool. The best.

One shared property for each item is how the input tag has type=radio.
Further up you can see the entire fieldset as an id of P47_RADIO, our item.
Combine these to identify any radio buttons within this specific item.

Edit page 'Inline CSS' to using this combined CSS selector, with a property to hide any web component identified with that selector.

#P47_RADIO input[type=radio] {
  visibility:hidden; 
  position:absolute;
}

Edit: Commenter Yol had success with visibility:hidden instead of display:none, when it comes to submitting the values. This may be a browser dependent issue.
Hence I have also set position:absolute to ensure the radio buttons don't take up space, as per visibility attribute definition.

This tidies the button group, trouble is,  the current selection is unknown without the native radio button. This can be solved by creating a dynamic action that toggles button classes upon click of each radio item.

Create dynamic action on click of jQuery selector

#P47_RADIO span.t-Button

Add action to Execute JavaScript

// add simple class to all options
$('#P47_RADIO span.t-Button').addClass('t-Button--simple');
// remove from current selection
$(this.triggeringElement).removeClass('t-Button--simple');
// Ensure underlying radio selected
$(this.triggeringElement).prev().click();

The result:



Edit 2: I found that clicking in the green padding area did not change the radio selection, only when I clicked in the blue label. So I added the third line in the JavaScript to ensure the input value simulates the click event.

In my case the t-Button--simple template option is removed from the current selection, but you can play with these options to choose a combination that suits your desired contrast, using the various colours:

t-Button--danger
t-Button--warning
t-Button--success
t-Button--primary

Or "hotness", a highlight:
t-Button--hot

Or contrast, well, not highlighting the entire button:
t-Button--simple
All demonstrated in the button builder reference in the Universal Theme app.

You can achieve consistent button width by adding this to your Inline CSS:
#P47_RADIO .t-Button {width: 100px;}
After your page loads, you can use the inspect element tool and increase the width 'live', to find a figure/unit that works for you.

Now we need to ensure this selection is defaulted when the page opens. I prefer to execute the click() event as to simulate the actual action, as opposed to replicating the relevant addClass() function.

You can use a similar technique to conditionally hide a specific radio button in the group (though you still need to validate the availability to the user on the database end.)

Add this to your 'Execute when page loads' page attribute, instead of adding a Default Value the item itself.

$('#P47_RADIO input[value=P]').next().click()

Where the value=P represents the relevant radio item value. The .next() moves selection to the next sibling, which is the span element, where click is triggered.

Or you could consider this button group plugin.

Or in APEX 5.1 you can use the pill template option.

Or you could use some form of List template button group.

Or you can stick with tiny buttons only people using a mouse on a desktop PC can hope to aim for.

One of the beauties of Oracle APEX - so many options. I think I need to try convert this to a plugin.

Happy APEXing!

KScope15 APEX Symposiums

Kscope15 officially kicked off on Sunday with a set of symposiums, I stayed in the APEX room the whole day, though I really wanted to hear what Steven Feuerstein had to say after lunch in the other room. I also hear a great discussion was had in the ADF room on tablet first vs mobile first.

APEX Episode 5 (A new frontier)

Joel Kallman kicked off the movie themed symposiums with a great talk about how APEX really is the enterprise solution people want/need, despite what other people say about it - and the fact it has been so for some time.

It wasn't a technical talk, but set a great tone for rest of the conference. Kudos to his delivery, it seemed well thought out and provided a powerful message about the wonderful tool that it is.

He mentioned interactions with various community members and highlighted how powerful the community has become in driving the evolution of the product. His praise indicated how vital community interaction is for the success of APEX compared to other tools on the market that have limited to no interaction with their customers.

This reminded me of something mentioned by Mike Hichwa regarding how the team uses the product to build packaged applications, compared to the Oracle Forms team who never used the final product. It makes a big difference.

Need for Speed (Page Designer)

Patrick Wolf formally introduced the group to the APEX 5 Page Designer, and various little tools within it that make development so much easier. The IDE truly is a marquee feature that shows how APEX has matured as a modern, advanced development tool.

Here are some great nuggets that are worth checking out

• A specific grouping for dynamic actions fired on Page Load
• Additional context menu facilities on various components, such as "create opposite action", something originally found just within the create DA wizard
• Right click to delete, no prompt since we can just undo actions in the IDE
• Drag/drop in the grid layout, far advanced compared to 4.0
• Bulk property edits, just like Oracle Forms - one of my favourites
• Updated help text for component properties
• Code editor highlighting, including validation and auto-complete for page items and core packages
• Block edit within code editor
• In-browser searching including option for regular expressions
• Shared component references and grouping, another of my favourite innovations 

Polite applause was extended for some of the features as Patrick announced them, showing community appreciation.

Interstellar (Universal Theme)

Shakeeb Rahman gave us an overview of the fantastic Universal Theme. It was an overview as opposed to a detailed look at how it all works, I think other presentations will be going into further detail on specific features.

One admission that came early was that further enhancements are to come as further feedback comes in, but the theme has been integrated into the APEX environment in such a way to make it easier to incorporate these in your applications in future releases.

He covered three major points.

1. Responsiveness - the new theme is leagues ahead of the capabilities of Theme25, which admittedly had shortfalls. It was interesting to note that accommodations have been made for things like tap event handling, to hand the 300ms wait time back to the users. I've used Touch Punch in the past to help with this.
2. Versatility - Lists are more heavily used in APEX 5 for navigation aids, which I think is great because they are a versatile feature. The menus are built with Accessibility in mind, and support keyboard navigation. Icon libraries have been included, with room to extend in future releases.
3. Options - I can't remember if this was the actual heading, but the declarative options now provided with templates will help developers build more consistent applications without the need for deeper CSS skills. Template Options give developers the same type of declarative CSS assistance Dynamic Actions did for JavaScript. It's a deep topic that needs it's own sessions but there are plenty built in demonstrated in the Universal Theme sample app.
   The Theme Roller enables re-colouring the application to a far greater extent than 20 variations on a similar theme could ever do. I reckon even my colour-blind friend could use it to build a scheme that contrasts well.
   Entire themes can also now be subscribed.

The Fifth Element (Mobile + HTML5)

Marc Sewtz braved the world of live demos to show developers improvements made to the mobile user interface. I found this interesting to compare to how we solved problems in developing for 10" tablets using the desktop interface. 

Not much appears to have changed from the developer's perspective (except for integration with the new IDE), but many HTML5 features have been incorporated into the build.

Out of the various suggestions Marc made for developing responsive apps with the universal theme, I think the most important was thinking "mobile first". This is regardless of the technology you're using.

Matrix Reloaded (Interactive Reports)

Anthony Rayner (one of a few faces I'm not sure I've ever seen) ran through what features APEX 5 provides for Interactive Reports.

• Intro - reminder for the group on what IR are all about
• Modernised - more CSS, more client side processing means more snappy interaction for the users.
  Accessibility standards are now met and plenty of people seemed to have questions regarding this, though it's a shame it got picky and slowed the flow of what Anthony had to say. His main point on this was regardless of whether you need to meet these standards, you should consider them while developing to help promote better applications.
• Action icons have been restored. 
• Single row view has been modernised. 
• Column options have been modified to help accessibility issues, but all will reap benefits. 
• Report and column widths are responsive to the page. 
• Dialogs are used instead of dropping down content. 
• Settings and filters have been cleaned up and enhanced.
• Extended - plenty of new features added in APEX 5
• an impressive pivot feature has been added to user options. 
• Multiple IRs can now be defined per page, it's best to ensure they have their own ID.
• Headers fix to the top of the page
• Theme roller also integrates with IR 
• Upgrading - Main issue coming out of complete IR revision relates to the gReport object and upgrading older reports. Any markup on 4.x report will probably no longer work.

The Prestige (Migrating to Theme 42)

Shakeeb came back to help the community migrate to the Universal Theme, using pre-packaged applications as an example..

Head to apex.oracle.com/ut and find the Bookmarklet to help with migration to switch to Theme 42.

Reset grid option. Default option probably handy, "Reset fixed region positions" but worth exploring what happens to your application with each. It will really depend on where you're coming from. Plenty of help on the topic by clicking ? icon next to option.

Shakeeb started by demonstrating how to update the login page to match a pre-packaged application built on APEX 5. He showed how easy it was to find, copy and apply various properties using the Page Designer.

Icons were real easy to add to the navigation menu using pop-list options to select font-awesome icons.

Existing regions had plenty of customisations to aim for responsive layout. Shakeeb removed a bunch of these, showing how ready Theme 42 is out of the box.

Buttons were easy to adjust and modify with some dragging and dropping in the grid layout, with basic tweaks to make them iconic.

Template options also came into play to help tweak page layout.

We can now create copy (and/or subscribe) of existing plugins from another application which was missing from previous releases.

List template attributes are introduced in APEX 5 to help work out what the 1-10 attributes are for the relevant tempate - very neat.

Change one attribute to convert existing pages into modal pages and all the links automatically treat the page accordingly. Now clean up the page to suit being modal, again solved with declarative features and region template options.

Rinse and repeat for your entire application, I guess! Shakeeb certainly had plenty to say about conversion and offered to give the crowd more (but I think many are ready for a beer). I look forward to more session content from him.

Conclusion

Bring on day 1