D3 Chart in Pentaho CDE

Hey Guys,

In this post i would like to share my experience on D3 charting library along with Pentaho C-Tools.

In this example you will learn:

1) How to render d3 chart in Pentaho CDE
2) How to pass the parameter in D3 chart
3) How to add Legend in D3 chart
4) How to add chart Title along with X-axis and Y-axis label.

Version C-Tools(CDE,CDF,CDA,CGG) - Version14.06.18 stable with bootstrap support.

Example:

Step 1:Layout:Create the layout as per your requirement.

Step 2:Datasource:select distinct first_name as letter,salary as frequency from employee where salary!=20 limit 20

Step 3:Components

D3 Components - D3 component -

In properties for Custom Chart Script write below code.

function f(dataset){
 
   
    var data = this.cdaResultToD3Array(dataset);

    var margin = {top: 45, right: 20, bottom:30, left: 80},
    width = 960 - margin.left - margin.right,
    height = 500 - margin.top - margin.bottom;

 var colors =    [ ["Letter", "steelblue"]];
                 
//var formatPercent = d3.format("%");

var x = d3.scale.ordinal()
    .rangeRoundBands([0, width], .1, 1);

var y = d3.scale.linear()
    .range([height, 0]);

var xAxis = d3.svg.axis()
    .scale(x)
    .orient("bottom");

var yAxis = d3.svg.axis()
    .scale(y)
    .orient("left");
  // .tickFormat(formatPercent);

d3.selectAll("svg").remove();

var svg = d3.select("#"+this.htmlObject).append("svg")
    .attr("width", width + margin.left + margin.right)
    .attr("height", height + margin.top + margin.bottom)
  .append("g")
    .attr("transform", "translate(" + margin.left + "," + margin.top + ")");



var tooltip = d3.select("#"+this.htmlObject).append("div")
        .attr("class", "tooltip")
        .style("opacity", 0);
       
  data.forEach(function(d) {
    d.frequency = +d.frequency;
  });

  x.domain(data.map(function(d) { return d.letter; }));
 y.domain([0, d3.max(data, function(d) { return d.frequency; })]);

 // xAxis
svg.append("g")
      .attr("class", "x axis")
      .attr("transform", "translate(0," + height + ")")
      .call(xAxis);
// yAxis
svg.append("g")
    .attr("class", "y axis")
    .attr("transform", "translate(0 ,0)")
    .call(yAxis)
    ;
// xAxis label
svg.append("text")
    .attr("transform", "translate(" + (width / 2) + " ," + (height + margin.bottom  ) +")")
    .style("text-anchor", "middle")
        .style("font-size", "16px")
    .text("Letter");
//yAxis label
svg.append("text")
        .attr("transform", "rotate(-90)")
        .attr("y", 0 - margin.left)
        .attr("x", 0 - (height / 2))
        .attr("dy", "1em")
        .style("text-anchor", "middle")
            .style("font-size", "16px")
        .text("Frequency");

  svg.selectAll(".bar")
      .data(data)
    .enter().append("rect")
      .attr("class", "bar")
      .attr("x", function(d) { return x(d.letter); })
      .attr("width", x.rangeBand())
      .attr("y", function(d) { return y(d.frequency); })
      .attr("height", function(d) { return height - y(d.frequency); })
       .on("mouseover", mouseover)
      .on("mouseout", mouseout);
     
      svg.append("text")
        .attr("x", (width / 2))
        .attr("y", 0 - (margin.top / 2))
        .attr("text-anchor", "middle")
        .style("font-size", "16px")
        .style("text-decoration", "underline")
        .text("Global & Local Searches");
   
    // add legend  
var legend = svg.append("g")
        .attr("class", "legend")
        //.attr("x", w - 65)
        //.attr("y", 50)
        .attr("height", 100)
        .attr("width", 100)
        .attr('transform', 'translate(-20,50)');

var legendRect = legend.selectAll('rect').data(colors);

legendRect.enter()
    .append("rect")
    .attr("x", width - 65)
    .attr("width", 10)
    .attr("height", 10);

legendRect
    .attr("y", function(d, i) {
        return i * 20;
    })
    .style("fill", function(d) {
        return d[1];
    });

var legendText = legend.selectAll('text').data(colors);

legendText.enter()
    .append("text")
    .attr("x", width - 52);

legendText
    .attr("y", function(d, i) {
        return i * 20 + 9;
    })
    .text(function(d) {
        return d[0];
    });
   
 function mouseover(d) {
   tooltip
       .transition()
       .duration(100)
       .style("opacity", .9);
   
  tooltip
      .html(d.letter  + ": " + d.frequency)
      .style("top", (d3.event.pageY-0)+"px")
      .style("left",(d3.event.pageX+0)+"px");
}

// Toggle children on click.
function mouseout(d) {
    tooltip.transition()
    .duration(200)
    .style("opacity", 0);
}
}
4)Add Parameter.

5)Add select component and on the basis of selecting drop down value the o/p will be shown below.




Output:

Cheers !!!!!
 
If you have any question you can ask me or else you can drop me email :

Sumit Bansal

BI Developer
sumitbansal450@gmail.com
 
References:
1)http://bl.ocks.org/Caged/6476579
2) http://bl.ocks.org/mbostock/raw/3885705/
3) http://bl.ocks.org/mbostock/3885304
4) http://bl.ocks.org/mbostock/raw/3885304/

import sql file from mysql console

Hey Guys..

 Here i am just explaining How to import sql backup file from mysql console.

I have seen people are always facing problem when they are importing sql backup file .Here i m telling one of easiest way how to import sql backup file .you need to follow those step as below mention:

Steps:
From the mysql console:

mysql> use Database name;
mysql>source path /to /file.sql

Enjoy !!!

Sumit Bansal

BI Developer

New Feature in 14.07.29- Bootstrap Panel in layout section of Pentaho CDE

Hey Guys,

 Here i am just giving latest update regarding new release of C-Tools version 14.07.29

The latest stable version of C-Tools in this week is 14.07.29.

The difference between 14.06 & 14.07 releases especially bootstrap panel option available in the new release(14.07) .

Advantage :

Now we don’t need to write our external bootstrap code in HTML code in HTML section either for row and column .

Thanx a lot for pedro and team for such a great effort for that.

Bootstrap includes a responsive, mobile first fluid grid system that appropriately scales up to 12 columns as the device or viewport size increases

The Bootstrap panel latest release information:               

Grid System	Extra small devices Phones (<768px)	Small devices Tablets (≥768px)	Medium devices Desktops (≥992px)	Large devices Desktops (≥1200px)
Grid behavior	Horizontal at all times	Collapsed to start, horizontal above breakpoints
Container width	None (auto)	750px	970px	1170px
Class prefix	.col-xs-	.col-sm-	.col-md-	.col-lg-
# of columns	12
Column width	Auto	~62px	~81px	~97px

 In this image you will see Bootstrap panel concept under layout section :

 Cheers !!!!!

Sumit Bansal

BI Developer

Ref:http://getbootstrap.com/css/#grid