Pentaho Tools :

Pentaho C-Tools(CDE,CDF,CDA),Pentaho CE & EE Server,OLAP-Cubes,Analysis using Pivot4J, Saiku Analytics, Saiku Reporting, Ad-hoc Reporting using Interactive Reporting Tool,Dashboards,Reports using PRD, PDD,Data Integration using Kettle ETL,Data Mining usign WEKA,Integration of Servers with Databases,Mobile/iPad compatible Dashboards using Bootstrap Css,Drilldown dashboards,Interactive Dashboards

Wednesday, 19 November 2014

* Rubber band drill down * Remove X-axis * Remove Y-axis * Pre-selection of bars * Conditional coloring and etc. :-)

Hello Every one..!!! :-)

This article is a collection of various tips from the pentaho forum and presented here as a document..

This will be useful as a POC on below topics & the best and easiest way of exploring all these is to download the example from this site and open in Edit mode in you PUC and navigating through all the properties & functions.

This example is an outcome of my R&D.

Features of this work out :

1) Remove X-Axis
2) Remove Y-Axis
3) Pre Select 'N' number of Bars
4) Base Axis(or X-axis) label font color, font size
5) Base Axis label rotation
6) Data label color for Bars
7) $ signed value(lable style) on data bar with bold font.
8) Second Y Axis(plot2) with line chart
9) Line color and value on the line is same.

10) Pre selected related information is displayed
    in pie chart as intra chart commnication when the dashboard loads


11) Rubber band based selection of intra chart drill down
   (i.e., multiple bars selection drill down). 


12) Giving multiple default values for a parameter
   (Example of custom parameter).


Image 1: Initial Loading of Dashboard



Image 2:Applying Rubber Band drill down(i.e., Selection of multiple Bars for drill down)



Image 3: After releasing rubber band check how the drill down happened



Download the Example :
Click Me

NOTE :
Queries are taken to display some data for charts.. might not related to requirement. 

Thank you :-)

Source of R&D :
Pentaho forum & experiments.

Core part of this Example is explained here : 

Main Feature : Rubber band drill down on the same dashboard (Intra chart rubber band based drill down ) OR multiple bars selection drill down on the same dashboard. 

 Scenario:
Lets say you have a bar chart and pie chart on your dashboard. 
Bar Chart : 
X-axis : Cities ( you can call cities as categories here).
Pie Chart:
Cities are categories for pie chart

i.e., Pass multiple cities from Bar chart to Pie chart by selection (i.e., select multiple bars and display the related information on pie chart with the same categories).


Step 1 : Create a custom parameter (param_city) and give default values for it. 

The way of giving default values(multiple values are input to a parameter) using custom parameter is:
In JavaScriptCode window write below

["Lebanon","Burbank","Portland"] 

NOTE : the names inside double quotes are city names


Step 2 : Rubberband selection functionality happens in 2 places and pre selection of bars happens in 1 place as given below respectively


userSelectionAction : 

function f(selectingDatums){
    var selectedCatsSet = {};
    var selectedCats = [];
    selectingDatums.forEach(function(datum) {
        var cat = datum.atoms.category.value;
        if(selectedCatsSet[cat] !== true) {
             selectedCatsSet[cat] = true;
             selectedCats.push(cat);
        }
    });

    // Return a new array, containing all datums having the same categories
    return this.chart.data.datums([{category: selectedCats}]).array();


selecitonChangedAction:

function f(selectedDatums) {
  // Extract the category values of selected datums, assuming that there is only one
  // category dimension and that the name of the dimension is "category" (the default)
  var categories = selectedDatums.map(function(datum) { return datum.atoms.category.value; })
 
  Dashboards.fireChange('param_city', categories);
   
}  


renderCallback:

function f(){
    //var datums = this.chart.data.datums([{series: 'sum'}]);
    var datums = this.chart.data.datums([{category: ["Lebanon","Burbank","Portland"] }]);
    pvc.data.Data.setSelected(datums);
}
 

 Step 3 : Pie Chart & It's Query
1) Add parameter(param_city) to chart component and also give it as listener.
2) For the query of pie chart add parameter(the type here is StringArry - Not just String) and pass the parameter in SQL.
3) Note that the parameter has to catch multiple values.. i.e., your WHERE clause should be as follows WHERE IN (${param_city}) . If you ignore () parenthesis you will not find the result properly. 
 
 That's it.. you have done with multiple bar selection drill down..

:-)

Sadakar
BI developer(Jasper/Pentaho/Talend ETL)