Modify OracleJET Property at Runtime in APEX

OracleJET has attributes galore, but some are are (not yet) available to change at design time, so JavaScript code can be added to the chart attributes to set relevant attributes.

function(options) {
options.styleDefaults.threeDEffect = "on";

return options;
}

See my previous post about modifying these attributes on render.
We can also do this at runtime, perhaps as response to a button click, such as the 2D/3D button in the cookbook.

First, set a static ID on the chart, possibly one of the most common "advanced" properties I use.

Static ID property available on many components

Use this ID in the browser console at runtime to see what JSON was generated for the chart definition.
Any missing properties will use the default specified in the documentation.
apex.widget.jet.getChartJSON('p95_skew')

Browser Console results

The documentation on these attributes is thorough, but I'd love some examples to help keep me moving.
http://www.oracle.com/webfolder/technetwork/jet/jsdocs/oj.ojChart.html#styleDefaults.threeDEffect

We can modify the orientation property by supplying a name/value pair as a JSON string.

$("#p95_skew_jet").ojChart({'orientation': 'horizontal'});

Note the selector has is the static ID with a suffix: $("the_static_id"+"_jet")

It took a little while to find the correct punctuation for the nested properties, so this is really one of those blog posts I created so I don't forget. You've seen Alex's blog by-line, right?

$("#p95_skew_jet").ojChart({'styleDefaults':{'threeDEffect': 'off'}});

Not all runtime tweaks behaved as expected, however. The following property behaves as expected when setting on render, but at runtime it squishes the width of the entire chart.
$("#jet1").ojChart({'styleDefaults':{'barGapRatio': 0.2}});

Refreshing the chart region afterwards did not help in this case.
While looking for answers, I came across this post from Riaz describing similar customisations.

OracleJET JavaScript Customisation in APEX

I've finally got some regular hands on a 5.1 instance, and the shiniest tool in the box for me is OracleJET.

Some months ago I spent a few days learning about OracleJET and the knockout framework with Chris Muir. I doubt I'd ever get to that nitty gritty, but it sure is handy to know some of the finer details now that I'm using them in APEX.

I wanted to have a play with the funnel chart. I took an existing query, and quickly busted out a working chart.

Upon playing with the sample chart in the OracleJET Cookbook, I decided I wanted the 3D option.
In the case of this chart style, I think the subtle effect made a big difference.

The Cookbook is a great guide to what you can play with

I couldn't find the relevant attribute defined declaratively in APEX, but that's fine - I know all of them aren't mapped, and we have a special JavaScript section in the chart attributes to help us customise the content. This expands upon attribute selections, not replacing them like the custom XML did for AnyCharts.

The attribute help in the Page Designer is a good start, and always worth checking when playing with a new field.

Page Designer Attribute Help

I noticed the Sample Charts application did the same thing, but I figured I could take this sample from the help, combined with information from the impressive JET documentation, and try work out the JavaScript myself.

Not all properties are mapped to APEX attributes

This is what I came up with.

function(options) {
  options.styleDefaults.threeDEffect = "on";
  
  return options;
}

Only to find the customisation from the sample application looked same same, but different.

function( options ){
    options.styleDefaults = {
        threeDEffect: "on" 
    };
    return options;
}

Turns out both are effective, once again demonstrating there's always a few different ways of doing the same thing in JavaScript.
I suspect the first option honours the object notation mentioned in the documentation, while the second assigns the value as a JSON name/value pair. I've seen similar behaviour in jQuery:

$('#item').css('color','blue')
vs
$('#item').css({'color':'blue'})

You can find other examples in your workspace with this query on the dictionary view.

select application_id, page_id, page_name, region_name, chart_type
 ,javascript_code
from apex_application_page_charts 
where javascript_code is not null;

You may also enjoy changing series colours with similar treatment by Colin Archer of Explorer UK.
http://www.explorer.uk.com/customising-chart-colours-apex-5-1/
The German APEX community also has a thorough rundown of JET charting in 5.1
https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/5841/index-en.html

In my next post I'll explore how to play with these attributes at runtime, not just on render.

APEX 5.1 Chart Column Mapping

If you've played with the D3 chart plugins you can find in the Sample Charts Packaged Application, you may have noticed the ability to nominate a column from your query as the series name.

APEX 5.1 Column Mapping

APEX 5.1 provides this built into the framework. Combined with some rather granular attribute control at series and axis levels, I think it will be harder to find tweaks that haven't been made declarative.

And if you do, there is a section in the chart attributes for JavaScript code. This also means if you do need to start hacking your chart, you're just adding to the existing attributes.

OracleJET Custom JavaScript

Compare this with the XML API for AnyChart 6.x where once you start using XML, say goodbye to most of your declarative attribute settings.

The ability to nominate a series column is optional, bringing more flexibilty to the SQL that no longer needs to conform to positional columns. I'm not sure yet if this will impact how often I use pivot/unpivot, but I'm sure it will help.

This post was brought to you by the result of last minute presentation screenshot prep and a prompt from this tweet, so thank Peter.

Charting Predictions, al a AskTom

The 'grand algorithm' favoured this particular tweet from Connor McDonald in my 'highlights'.

Use regression functions in Oracle to do basic forecasts https://t.co/gaKGHafJaQ

— Connor McDonald (@connor_mc_d) June 14, 2016

I found this intriguing considering a side project I've been tinkering on. The solution ended up looking much simpler than a model clause, though I'm going to need to let it digest for a while before I fully understand how it works. Maybe read the relevant documentation.

Trouble is, the final result isn't the sort of thing you want to print out on your dot matrix printer to show the big cheese, right? You want to graph that sucker.

So I create a page in Oracle APEX 5.1 (apexea.oracle.com) and created a line chart.

I split the results into two series using Connor's query within an inline view, in part because that was the fastest way I could think to get the graph plotted

select dte
 ,sz as actual
from (
with reg as (
    select regr_slope(sz, to_number(to_char(dte,'j'))) slope,
           regr_intercept(sz, to_number(to_char(dte,'j'))) intcpt,
           max(dte) nxt
  from t
  )
  select t.*, 'actual' tag
  from   t
  union all
  select nxt+r dte,
         trunc(intcpt+slope*to_number(to_char(nxt+r,'j')))
         ,'predicted'
  from reg,
       ( select rownum r
         from   dual
         connect by level <= 30 )
  order by 1
)
where tag ='actual'

The result was compelling.

A picture paints a thousand words

I also like the fact the declarative series name honoured, rather than a double quoted "Column Alias".

It will be interesting to see how this plots more varied data, considering the data variance issues Connor described.

I added a to_char(dte, 'DD Mon YYYY') to get a prettier tooltip, but interestingly, it changed the angle of the line.

TO_CHAR() added

Then I spotted this field here, I could use the query as supplied by Connor without having to think about pivoting the result to get it into the right format the engine requires.

A new declarative option

However the result didn't come out quite right. I think that's more an issue with the communication between APEX and OracleJET that anything else, ie: a bug.

Not quite right

It appears the declarative charting options have been engineered in 5.1 to reduce the amount of hoop jumping with the result set. Charting in general appears more declarative, easier to navigate and work out what's going on.

Nice work APEX team.

Analytics in APEX Charts - Moving Average

Consider a chart with a trend that might be quite jagged across data points (blue line).

What if you would like a smoother version of that line - a moving average, if you will (red line). This stabilises the results, like looking at climate vs weather.

Oracle APEX Line Chart - 2 series

It's fairly easy from a SQL point of view - in this case it's another column in the original chart query

SELECT null lnk
  ,diff   label
  ,count(*) AS qty
  ,round(avg(count(*)) over (order by diff rows 
                             between 3 preceding and 3 following)
        ,2) AS moving_avg
...

The third column uses an analytical function to calculate the average of the 6 surrounding counts at any given point on the x-axis. The "rows between" syntax is often left to the default, which is everything up to the current value (see post comments), based on the order provided. In this case it explicitly specifies the 'window' of rows to average to be only nearby data points.

Note this report looks a lot cleaner with display setting 'Marker' using 'None', as opposed to 'Circle'.

Nice, simple way of providing the user information that can be easier to read & interpret.

Implement Bullet Sparkline using extended Enkitec Plug-in

This post details the implementation of an extended version the Enkitec Sparklines Plug-in to include bullet charts. Previously I posted how this extension was made.

First you need to download and import the plug-in. Once this is done, you can define the data used, then create a dynamic action to convert the data into the sparkline chart.

In my case I didn't need to consider string aggregation techniques as my data came from different fields. I defined a dynamic PL/SQL block that generated HTML similar to
<span id="jobBullet">12,15,15,13</span>
Where the values are respectively Target (red line), Performance (inner blue line), Range (full background shading), Expected (first layer of shading, near red line)

Target, Performance, two ranges

We will probably use the inner blue line to represent "expected" target, and have just one background colour, so this data set would not have the fourth number.

Target, Performance, Range

I found the jQuery Sparkline documentation lacking when attempting to modify the tooltip values.
I ended up finding the information I need in the related Google Group.
tooltipValueLookups: { fields: {r: 'Range', p: 'Performance', t: 'Measurement'} }

This information forms part of the onLoad dynamic action that will convert the span of data into a sparkline.

The action utilised comes directly from the plug-in definition: Enkitec Sparklines [Plug-in]
I directly identify the data span with the jQuery selector #jobBullet

When generating a sparkline for every row in a report, it's likely your selector will be a class.

I wanted my sparkline to be larger, so I adjusted the plug-in to allow me to enter the common width property each time.

My next step is to parameterise the colour scheme so I can modify the Performance colour to be green or red, depending on the Target value.

Extend APEX sparklines plugin to include bullet chart

Dan McGhan recently announced a new plug-in using sparklines. This post details how I extended the plug-in to support another chart type. A separate post details how I implemented this in an application.

If you haven't seen them, check out his brief summary - there's a great image showing examples of those he's built into the plug-in from those available in the original jQuery plugin.

They are basically word/sentence sized graphs - which I think are great because a picture often paints a thousand words.

Out of all the info I've read since coming home from leave, this really stood out because I knew some perfect applications for them in a current project - using the bar chart with negative values, it can display a visual indicator for the users that encourage them to target "zero", which is a big culture change in regard to what's being attempted.

Since I was curious, and noticed they had only enabled some of the available charts, I checked out the documentation on the jQuery plugin and saw the bullet charts. These were another potential solution for us since the AnyChart options licensed through APEX did not include horizontal linear gauges.

The APEX evangelists have built an integration kit. I also considered looking into RGraph and jqChart, but since finding the bullet sparklines I tried tweaking the example and scaled it up using Chrome - there was my answer.

My APEX plug-in authoring skills are still developing, as is my jQuery - but I didn't find it hard to adapt Dan's code to include bullet sparklines as another chart type in the plug-in.

Bullet Sparkline - scaled up

I also adjusted the custom attributes to make width always available. I've made an export of the extended plugin available here.

This segment of code shows how easy it was to extend - all I needed to do was modify the attributes to match those in the jQuery Sparklines documentation

ELSIF l_chart_type = 'bullet'
   THEN
      l_js_function :=
         'function(){'|| l_crlf ||
            'this.affectedElements.sparkline("html", {' || l_crlf ||
               'type: "' || l_chart_type || '",' || l_crlf ||
               'height: "' || l_height || '",' || l_crlf ||
               'width: "' || l_width || '",' || l_crlf ||
               'targetColor: "' || l_bullet_target_color || '",' || l_crlf ||
               'targetWidth: "' || l_target_width || '",' || l_crlf ||
               'performanceColor: "' || l_bullet_perf_color || '",' || l_crlf ||
               'rangeColors: ' || l_bullet_range_colors || ',' || l_crlf ||
               CASE
                  WHEN l_enable_tooltips_and_hl = 'N'
                  THEN 'disableTooltips: true,' || l_crlf ||
                     'disableHighlight: true'
               END ||
               CASE
                  WHEN l_additional_options IS NOT NULL
                  THEN l_crlf || l_additional_options || l_crlf
                  ELSE l_crlf
               END ||
            '});' || l_crlf ||
            CASE
               WHEN l_add_click_event_bindings = 'Y'
               THEN
                  'this.affectedElements.bind("sparklineClick", function(evnt) {' || l_crlf ||
                     'var sparkline = evnt.sparklines[0];' || l_crlf ||
                     'apex.jQuery(document).trigger("enkitecsparklineclick", {' || l_crlf ||
                        '"sparklineId": "' || l_sparkline_id || '",' || l_crlf ||
                        '"sparklineObj": sparkline,' || l_crlf ||
                        '"xValue": sparkline.currentRegion,' || l_crlf ||
                        '"yValue": sparkline.values[sparkline.currentRegion]' ||
                     '});' || l_crlf ||
                  '});' || l_crlf
            END ||
         '}';
   END IF;

And just quietly, I really love the floating bookmark index in the jQuery Sparklines documentation.

Thanks Dan, Doug & Enkitec, for the headstart on this versatile solution.

Scott

APEX Gauge Chart example

How ready is tomorrow's presentation?

...never to reach 100%

Create Chart page
Gauge - dial %
Look 5
Series source: select 95, 100 from dual

Edit Chart
Gauge pointer - bar
Uncheck Values
Major/minor interval 10/5

Scott