Did you know you can easily modify any pivot using java script?
Today I’ll show you how to add Sparklines into a pivot using JS API in Prism web.
First you will need to create a Pivot in BI-Studio, one with a single dimension on the rows, a single measure on the background and some time or any other sequential dimension over the columns.
Now publish it to Prism Web. File->Publish
Next step is to edit the JS controlling the Pivot look and feel, so using the admin user open the dashboard with the Pivot you have just published and click on the Customize Script (<>) button.
[The JS Editor allows you to customize the Pivot and even hide it and add a different visualization with the same data.]
You will need to modify the function that is being called after results loaded,so not so surprisingly it is called this.afterResultLoaded = function (args) {} . next we need to import the js file that will help us creating the sparkline visualization, it is called jquery sparklines (more info http://omnipotent.net/jquery.sparkline/).
First we will add the import script call:
$.getScript('http://[SERVER ADDRESS]/jquery.sparkline.js', function(data, textStatus)
{
//some code here
}
of-course we need to copy the file to our Prism Web folder before we can run it.
The code below should be within the get script brackets (Explanations within the code).
var i = 0, r, rows = $("tr", args.element), l = rows.length;
//Add new column for each values row
for (; i < l; i++) {
r = $(rows.get(i));
// Check if the row is not defined and make sure we start adding form row 3 only
if (!defined(r
) || $
(r
).hasClass
('rdMiddle') || i
< 3) { // Add the columns header a Chart text
if (i === 2) {
var clone = $($(r).find('td').eq(2)).clone(true);
//Replace the header
$(clone).find('div span').text('Chart ') // Add the cloned cell after the first column
$(r).find('td').eq(0).after(clone);
}
continue;
}
//Set the parameters to know how many cells are in each row
var vals = '',
c;
var cells = $("td",
r);
var cl = cells.length //Loop the cells and add the Sparkline charts
for (c = 1; c < cl; c++) {
var cellData = $(r).find('td').eq(c);
//Get the text and remove all comma's
var cellText = $(cellData).text().replace(',',
'');
//Create rows values string
if (c === cl - 1) vals += cellText;
else vals += cellText + ',';
}
}
//Copy the column 2 style
var cClone = $($(r).find('td').eq(2)).clone(false);
//Setting the values attribute for the Sparklines script use
$(cClone).attr('values', vals);
//Replace the span in the clone td
$(cClone).find('span').replaceWith('<span class="sparklines" values="' + vals + '"></span>');
// Add the new Sparkline column after the first one
$(r).find('td').eq(0).after(cClone);
}
//Set Sparklines script and some properties like type color etc.
$('.sparklines').sparkline('html',
{
type : 'line', lineColor : 'blue'}
);
This is the result of the code modifications:
Live demo can be found here:
Pivot Sparklines