Infokus

Hi Guys,

I love experimenting/Playing with Pentaho C-Tools where it took me to explore D3 bar chart with SQL query result set...

Not a big deal in implementing it, but want to share some thing with community folks.

What will you learn in this post ?
You will get an idea on how to implement D3 bar chart.
1) Passing query output to D3 chart code.



Limitations on the Example :
1) Query and Chart is not parametrized yet.
2) Y-Axis labels are not dynamic yet.
3) Tool tip is not implemented.
4) Not tested the bootstrap support.
5)  and more.....

Readers are encouraged add your support to improve the sample of this in comment box...


Software setup :
a) C-Tools(CDE,CDF,CDA,CGG) - Version14.06.18 stable with bootstrap support.
b) Database server for a sample query - In this example postgres - foodmart database.
3) Install D3 components plug-in from market Place

 Example 1 : 

 Step 1 : Layout : Create your layout .... Row---column--Html... Span 24 for this example
 Step 2 : Data Source : 
Driver : org.postgresql.Driver
URL : jdbc:postgresql://localhost:5432/foodmart
UserName/Password : postgres/postgres
Query : query1
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.
Datasoruce : query1


function f(dataset){
   

    
    var data = this.cdaResultToD3Array(dataset);
   
    
    var margin = {top: 20, right: 20, bottom: 30, left: 40},
        width = this.getWidth() - margin.left - margin.right,
        height = this.getHeight() - margin.top - margin.bottom;
   
    var formatPercent = d3.format(".0%");
   
    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);
  
  
    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 + ")");
       
       
 
   
    // Commenting this out, we have data already
    // d3.tsv("/pentaho/api/repos/d3ComponentLibrary/static/custom/data/data.tsv", function(error, data) {
   
      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; })]);
   
      svg.append("g")
          .attr("class", "x axis")
          .attr("transform", "translate(0," + height + ")")
          .call(xAxis);
   
      svg.append("g")
          .attr("class", "y axis")
          .call(yAxis)
        .append("text")
          .attr("transform", "rotate(-90)")
          .attr("y", 6)
          .attr("dy", ".71em")
          .style("text-anchor", "end")
          .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); });
   
      d3.select("input").on("change", change);
   
      var sortTimeout = setTimeout(function() {
        d3.select("input").property("checked", true).each(change);
      }, 2000);
   
      function change() {
        clearTimeout(sortTimeout);
   
        // Copy-on-write since tweens are evaluated after a delay.
        var x0 = x.domain(data.sort(this.checked
            ? function(a, b) { return b.frequency - a.frequency; }
            : function(a, b) { return d3.ascending(a.letter, b.letter); })
            .map(function(d) { return d.letter; }))
            .copy();
   
        var transition = svg.transition().duration(750),
            delay = function(d, i) { return i * 50; };
   
        transition.selectAll(".bar")
            .delay(delay)
            .attr("x", function(d) { return x0(d.letter); }
           

            );
           
        transition.select(".x.axis")
            .call(xAxis)
          .selectAll("g")
            .delay(delay);
      }
    // });
   
}
NOTE :
1)  As the code is a replica of build in Example in Pentaho D3 plug-in , the code depends on 2 column result set of data.....

Step 4: Save dashboard and test the output
Image 1 : With sort enabled - For this you need to write below HTML code for the column of this chart in layout section .

<label><input type="checkbox"> Sort values</label>  


Image 2 : With sort check box disable

Example 2:

Why to wait ? Lets have a look at this static bar chart example in CDE( check this : http://jsfiddle.net/enigmarm/3HL4a/13/ )

Image 2 : Descending
Default:

To get the above static charts on CDE output... lets do this..

Hmmmm...!!! the same  code I used from http://jsfiddle.net/enigmarm/3HL4a/13/ in CDE as an experiment..

Add HTML code at Row-Column-HTML
Add Css code at : Code Sinppet Css
Add D3 chart script code at : Custom chart script for D3 component.
Save your dashboard and preview it..


Are you eager to check it in CDE ? Download the examples here 

Click Me folk...!!! :-) 


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/
5) http://jsfiddle.net/gregfedorov/Qh9X5/9/
6) http://jsfiddle.net/uedatakuya/tXPEV/light/
7) http://jsfiddle.net/weXNd/6/

Hi guys...!!!

Community Dashboard Editor is the best reporting/dash boarding  tool that I have worked. Its smart functionality made me love to work with it and exploring the things time by time.

Youll learn how to fetch different columns from result set of a single query and use them in different analysis purpose in charting.

Recently I needed to work with a single query data source(SQL) of having 3 columns result set ...
Lets say there are 3 columns A,B and C where as A column is having some category names and B and C are having some columns.

A  B   C
--------------
abc 2   4
pqr  6   8 
xyz 10 5
and etc.

From the result set A&B are on first chart and A&C are on other chart..
Now how ?????? This question leads me to check the "Data sources" section of CDE.

Follow the steps below.

1) Click on the "sqloverjndi" which  you created for your SQL query.
2)  In the properties section you can find an option called "Output options". Just click on  it.
3) Lets say you have 3 columns in your result set and these 3 columns takes index values starting from 0 to n.. i.e., A column index is 0 , B column index is 1 and for the C  value 2 is the index.

  NOTE: if you have more numbers of columns you can give as many indexes by clicking "Add" button as many times.
4)For Chart 1:
  i) Click on the chart component where you want show A& B columns ( A is category B is value)
  ii) In the properties(Click on Advanced properties) click on "Pre Execution"
  iii) Write this below code
            function f() {
                                 this.chartDefinition.readers = [
                                 {names:category, indexes: 0},
                                  {names: value, indexes: 1},
                                    {indexs: 2}
                                    ];
             } 
  Why this {indexs:2} ? if you omit this the values of index2 append to the category names... to eliminate that problem you need to write it.

 5) For Chart2 :
 Repeat the steps in in point 4)
 Slight changes in code ...

function f() {
                                 this.chartDefinition.readers = [
                                 {names:category, indexes: 0},
                                  {names: value, indexes: 1},
                                    {indexs: 2}
                                    ];
             } 

You are done with reading data from single query of 3 columns where 1&2 for one chart and 1&3 for another chart...

Save your dashboard and see the preview.

Sadakar
BI developer 
( "Learning never exhausts the mind" )