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

SQL Analytics 101 - Row_Number()

Here is a simple example for when SQL Analytical Functions are simple yet useful.

I wanted a basic 1,2,3,4 count so I could alternate colours in a report.

select ename, sal, rownum rn
  ,mod(rownum,4) mod_rn
  ,mod(row_number() over (order by sal),4) mod_rna
from emp
order by sal

ENAME      SAL     RN     MOD_RN  MOD_RNA 
---------- ------- ------ ------- --------
SMITH      800     7      3       1       
JAMES      950     13     1       2       
ADAMS      1100    12     0       3       
MARTIN     1250    10     2       0       
WARD       1250    9      1       1       
MILLER     1300    14     2       2       
TURNER     1500    11     3       3       
ALLEN      1600    8      0       0       
CLARK      2450    3      3       1       
BLAKE      2850    2      2       2       
JONES      2975    4      0       3       
FORD       3000    6      2       0       
SCOTT      3000    5      1       1       
KING       5000    1      1       2       

 14 rows selected 

Column "MOD_RN", based on ROWNUM, is unpredictable due to the order by.

Replace ROWNUM with ROW_NUMBER(), which mimicks the ORDER BY for the statement, and we return to predictability.

ROW_NUMBER() is one of my favourite and most frequently used analytical functions. I've used it for basic numbering devices such as this, identifying duplicates, compressing sequences, APEX checkbox values, defining precedence within an in-line view, and probably much more.

Scott