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

Thursday, 31 July 2014

New Mobile Product Sales Dashboard 3 Example in Pentaho CDE

Hi Guys,

Here is the new dashboard work out in Pentaho CDE.

High lights of the dashboard:  

1) Bar Chart with Trend Line
2) Stacked Bar chart with Trend lines + Line Chart.
3) Submit Button for input controls after selection.
4) Export charts to jpg images.
5) Reload with default inputs
6) Navigation (Home button) to Parent Dashboard(if we assume this as one of the child dashobards).
7) Mobile/iPad/laptop/Desktop comparability.

laptop View:

Share a sample data of yours ...!!! I'll give you the meaning of it.

Download this New Mobile Product Sales Dashboard-1
                         New Mobile Product Sales Dashboard-2
                         New Mobile Product Sales Dashboard-3

References :

Thank you.
BI developer (Japser/Petnaho/Talend ETL).

Tuesday, 29 July 2014

Outer Ring to Pie/Doughnut chart in pentaho CDE - Tip from CCC-Master Leao.

Hi Guys,

This tip will teach you how to draw an outer Ring to donut/pie chat in petnaho CDE.

Source for this tip :

Purpose : To make your pie or doughnut chart more attractive.
Arg = slice_add
Value = write below function with out comments(ignore comments)
 function() {
           var pctBorderOffsetRadius = 0.01; // <== Configure this >= 0 or < 0!
           return new pv.Dot()
               .data(function(scene){ return [scene]; })
               .layoutInfo(function() {
                   return  this.getContext().panel._layoutInfo;
               .zOrder(-10) // below pie slices
               .bottom(function(){ return this.layoutInfo().center.y; })
               .left  (function(){ return this.layoutInfo().center.x; })
                   var li = this.layoutInfo();
                   var borderOffsetRadius = pctBorderOffsetRadius * li.clientRadius;
                   var maxPieOffsetRadius = li.explodedOffsetRadius + li.activeOffsetRadius;
                   var borderRadius = li.normalRadius + 
                                       maxPieOffsetRadius + 
                   return this.getContext().chart.animate(10, borderRadius);

Sample output:

Thank you. 

Wednesday, 23 July 2014

Ivy Gauge Compnent Example in Pentaho CDE --- How to visualize a singel value ???

Hi ,
This post explains how to use Ivy Gauge component in Pentaho CDE dashboards.

What can be visualized using this component ?
1) To represent single value- High level value on the dashboard.
2) To represent single valued result sets from SQL query or any other data source.

Ivy components are developed using SPARKAL application , which is an application plug-in developed by Pentaho..

This Ivy component is actually, justGage component and you can integrate it in your other applications also.

The Official site for justGage Component is :

 This component takes SQL result in below format

column1    value1
TotalSales     345666

i.e., there should be 2 columns  one is of string type and another is of value..

Download the example here and play around : Click Me

Quick Understanding : 

Image 1  type gauge:
1) You can pass parameters on gauge component.
2) For instance, you have 3 different types of sales and on selection of sales in a drop down your gauge has to show the value. (Why 3 gauges for 3 different sales ? Let's save dashboard space for other charts).
1) Create a parameter let's say : param1_sales
2) Create a select and make sure this select is works with param1_sales parameter.
3) for the select give
      arg                 value
unit_sales           Unit Sales
store_sales        Store Sales
store_cost         Store Cost
here arg is  the actual column name and value appears on the drop down.
and the htmlObject for this some thing like "select1" (an id of a div or span tag). 

4) Query looks like below.

'Sales' AS Sales,
        WHEN ${param1_sales}='Unit Sales' THEN sum(unit_sales)
        WHEN ${param1_sales}='Store Sales' THEN sum(store_sales)
        ELSE sum(store_cost)      
END AS "TotalSales"
FROM sales_fact_1997

5) Take the Ivy Gauge component from Components section.

Core points:
a) Download plugin from Market Place & restart the BI server. 
b) On your CDE dashboard Ediotr, find Ivy IS from Components sections and select Gauge Component. 
i) Give name (for instance : gaugeexample).
ii) Data source : above query name.
iii) parameters : give the param1_sales parameter.
iv) Label : space in double quotes  gives nothing label (formally gauge title). [In some case we may not need this as we will give the tittle for this on panel title]
 v) Min and Max values depends on your requirement.
    for %, take min=0 and max=100
    for numeric, it depends on your output.
vi) Gauge width Scale = -0.2 ( Decrease the width of gauge and comes as thin ).
vii) Gauge type could be donut or gauge.

Another point ::: If your SQL result is like below you will get multiple gauges in the specified html Object area which is shown in Image 3

sales    sum
Store Sales    565238.1300
Store Cost    225627.2336
Unit Sales    266773.0000

NOTE : For advanced properties you need to set Extension points which you can find at Customize style

The same is listed below for quick reference

Here's a list of all parameters you can setup your gauge with:
  • id : string container element id
  • title : string gauge title text
  • titleFontColor : string color title text
  • value : int value gauge is showing
  • valueFontColor : string color of value text
  • min : int minimum value
  • max : int maximum value
  • showMinMax : bool hide or display min and max values
  • gaugeWidthScale : float width of the gauge element
  • gaugeColor : string background color of gauge element
  • label : string text to show below value
  • showInnerShadow : bool whether to display inner shadow
  • shadowOpacity : float shadow opacity, values 0 ~ 1
  • shadowSize : int inner shadow size
  • shadowVerticalOffset : int how much is shadow offset from top
  • levelColors : array of strings colors of indicator, from lower to upper, in hex format
  • levelColorsGradient : bool use gradual or sector-based color change
  • labelFontColor : string color of label showing label under value
  • startAnimationTime : int length of initial load animation
  • startAnimationType : string type of initial animation (linear, >, <, <>, bounce)
  • refreshAnimationTime : int length of refresh animation
  • refreshAnimationType : string type of refresh animation (linear, >, <, <>, bounce)

Download the example : Click Me

Have fun with open source... 

BI developer
(Jasper/Pentaho/Talend ETL).


Monday, 21 July 2014

Tip : Export JPivot output to excel in Pentaho 5.x version. (By default export to Excel is disabled in 5.x server)

Hi Readers,

This tip is useful to enable the "Print" property of JPivot output to Export into Excel sheet.

By default in pentaho 5.x version servers it is disabled.

Un comment below code in plugin.spring.xml file

    <bean id="Print" class="org.pentaho.jpivot.proxies.ProxyPrintServlet"/>

Location of the file is :


NOTE : Once you enable the code, stop the BI server and start again. [B'z it is a configuration file and changes will be affected iff you restart the server.]

Test: In the xml file it was suggested to replace with fop-0.20.5, but I didn't do it instead just uncommented the code and tested which is worked as expected.. If you still unable to get export work , you might have to replace the specified jar file.


Tip : Convert Stacked Bar Chart to Stacked Bar + Line chart

Hi Community,

Here is the my next work out as an R&D

I'd just want to share the tip on how to convert a stacked bar to stacked+line chart. 

 The SQL result set is should be as follows to develop the kind of chart shown in image. 

city    storesales    storecost    unitsales
Woodland Hills    158.6300    67.6191    63.0000
West Covina    152.0900    63.3554    68.0000
Santa Monica    147.0200    55.9771    60.0000
San Gabriel    303.4000    125.9544    128.0000
Pomona    211.9600    83.3137    98.0000
Long Beach    317.8100    127.5116    143.0000
Lakewood    158.5600    64.4194    74.0000
Imperial Beach    118.1100    46.9418    46.0000
Grossmont    164.1700    65.7190    74.0000
Glendale    176.0400    72.6059    87.0000
Downey    211.0500    80.6608    104.0000
Burbank    225.7400    90.0673    109.0000
Beverly Hills    167.8400    67.1768    85.0000
Bellflower    121.5100    49.3005    66.0000
Arcadia    115.3500    44.6145    55.0000

 1st column -> X-axis label
2nd column & 3rd columns ->Will sit as sacks 
4th column -> coverts to line

Core part : 
Set below properties to get the stacked bar with line. 

Stacked = True
Crosstab Mode=true 
Series in rows =false


That's it.. Save the dashboard and preview it. 

Sample Image 1:

Sample Image 2:

Sample Image 3:

Quickly Want to check it in your environment  ???

Click here to download the example 

(Jasperserver postgresql foodmart database , bootstrap supported mode with latest C-Tools).

Few limitations on the example:
1) X-axis label font size is not working though given
 baseAxisFont = "bold 13px sans-serif"
2) Left side Y-axis & right side Y-axis label colors and font-size is not set .. Need to 
find what are the extensions points to use on for them..
Community readers are encouraged to add these points if you find in comments box.  

Meet me at for your business needs ..

Thursday, 17 July 2014

Send One Dashboard selects (input controls) to another Dashboard selects(values comes from first dashboard selects and sit in Dashboard-2 selects) in Pentaho CDE

Hi Guys,

This post will talk about below scenario........

Scenario Explained:
When you navigate from one dashboard to another dashboard , in some cases you might need to pass the selected parameter values on Dashboard 1  to  Dashboard 2 selects.

i.e., In simple terms, it is not the category names passing from any chart on performing click action but it is passing the input controls values from one dashboard selects to another dashboard selects. (Take input controls from DB-1 and make them to sit on input controls of DB-2 and then allow user to select other inputs if he/she wants on it)

This sample is developed on : C-Tools of stable version of CDE,CDF,CDA with bootstrap supported.

You can perform below :
1) Default values for selects (dates and other drop downs)
2) Select user wished inputs in selects on Dashboard-1.
3) Provide a button/link to drill down to Dashboard-2 with the selected inputs on Dashboard-1.
4) On Dashboard-2 selects , the values will coming from Dashboard-1 will be placing.
5) Components(Charts and tables and etc) on Dashboard-2 will listen to these input selects.
6) Change the inputs to other values by selecting here on Dashboard-2 and corresponding components will listen to them again.
7) Link/Button to drill back to Home dashboard -It can be default stage of Dashboard-1 OR reversely can apply the same logic to place the changed values of DB-2 up on DB-1 selects.

In this example I have taken 3 inputs on Dashboard-1.
They are :
1) From Date
2) To Date and
3) ID

Dashboard -1 Development : 
Step-1 : Lay Out

Below code is for : A panel with body and to for the place holder for lables and selects(Dates and others) and majorly for a Button(On which we are going to do some magic). 

Row ->Column -> HTML
and the Bootstrap HTML code is :

<div class="col-md-12 ">
            <div class="panel panel-primary">
            <div class="panel-heading">
                <h3 class="panel-title" id="DashboardName">Dashboard-1 Send the Selected Parameters to Dashbaord-2 Selects
                 <button onclick="sendParameters()" type="button" class="btn btn-default btn-xs pull-right">
                <div class="clearfix"></div>  
            <div class="panel-body">
              <span class="labelcss"><b>From Date</b></span>
              <span id="FromDateSelect"></span>
              <span class="labelcss2"><b>To Date</b></span>
              <span id="ToDateSelect"></span>
              <span class="labelcss3"><b>ID</b></b></span>
              <span id="IDSelect"></span>

Step 2: Creating parameters and Selects 
As shown in below figure create 3 parameters and selects for them.
i.e., param1_FromDate, param2_ToDate,param3_ID for parameters
and select1_FromDate, select2_ToDate and select3_ID for selects 

Make the selects to listen to the associated parameters.
Note that in this example I have not taken any SQL for drop down selects instead created an array for drop down.

Step : 3
Have a preview of the dashboard once done with step 2 and you can observe there you will not be drill down action when clicking on button component.

Here, let's come back to Lay out section and add a Java Script resource.
Write below code in Java Script Code Snippet

function sendParameters(){
   //Test whether your parameters holding the values in an alert message 
    alert("From Date="+param1_FromDate+"\n"+"To Date="+param2_ToDate+"\n"+"ID="+param3_ID);
//Perform drill down to 2nd dashboard when clicking on Button Component 
var url=
 //Open up 2nd dashboard with parameters passing along with URL   


Dashboard -2 Development :

Development of this dashboard has to done separately to take it's URL on Dashboard -1 java script

Develop your dashboard with the same input controls (Why to develop again ? Let us SAVE AS with other name and with few modifications - Remove script code, make button component URL in HTML script for HOME dashboard)

Add below script in Java Script Code snippet resource..
i.e., Capturing the values into parameters and internally the selects will take these input values..and later on Dashboard-2 user can select/change on input values on different selection.

param1_FromDate= Dashboards.getQueryParameter('send1_FromDate');
param2_ToDate= Dashboards.getQueryParameter('send2_ToDate');
param3_ID= Dashboards.getQueryParameter('send3_ID');

Dashboards.getQueryParameter(<parameterSenderStringFromDashboad1>) will capture the value from the URL and assign these values to respective parameters.

That's all, you are done....

Sample output Images : 
i). With Default Values On Dashboard-1
ii).Perform Click Action on Dashboard-2 Button and Test the select values in an Alert Message
iii) Observe the values for select components on Dashboard-2 after clicking OK button. 

CASE-II Images : 
i) With User Selected Values On Dashboard-1
ii) Test with alert Message after performing click action on Dashboard-2 button

iii) Observe the values for select components on Dashboard-2 after clicking OK button. 
iv) Make Other selections on Dashboard-2

Download the above qualified Example :  Click Me

Download a quick simple example here :  Click Me

Readers and community developers are encouraged to add your feedback ,suggestions and/or improvement of this post in comments section.

Sadakar Pochampalli
BI developer
(Jasper/Pentaho/Talend ETL)
Hyderabad, Telangana. 

Wednesday, 16 July 2014

New Mobile Product Sales Dashboard-1 Example in Pentaho CDE

Pass Dynamic column to SQL query SELECT clause from the parameter selection in Pentaho CDE

Hi Guys,

In pentaho CDE, you can pass column names dynamically to the SQL query from the selection of an input control..

Why this is useful ?
You need not to develop separate charts for another measure.
You will save the development time and re-usability of CODE.(obliviously reducing the code)
You will save the client time with a single input. 

A scenario : 
 For example in your database you have 2 measure columns let's say unit_sales and store_sales and in your sql query you need to pass these column dynamically..

For this you need to create a parameter with select component.. As these are column names you need not to worry about DataSource in select instead you will take an array of hard coded values of column names.

Let's say the parameter associated with the select is : param_Measure and the default value let's say unit_sales

Make the select to listen this parameter and give the parameter for query data source.

i.e., in select component array : give below

Arg                  value
unit_sales       Unit Sales
store_sales     Store Sales

Arg is the column name and value will appear in drop down.

Now, you need to write some thing similar to below CASE condition ..

For instance :

        DISTINCT brand_name AS "Brand Name",

        WHEN ${param_Measure}='unit_sales' THEN sum(unit_sales)
        ELSE sum(store_sales)
        END AS "Sales"

FROM product p
INNER JOIN sales_fact_1997 sf7
ON p.product_id=sf7.product_id
INNER JOIN time_by_day t
ON sf7.time_id=t.time_id
t.the_date>='2012-01-01' AND t.the_date<='2012-01-07'

GROUP BY "Brand Name"



Sample output images :

Column 1: store_sales Sample OUTPUT
 Column 2 : unit_sales OUTPUT

Example Download here : Click Me
(foodmart database of postgresql - A jasper server sample database).

BI developer
Jasper/Pentaho/Talend ETL 

Export Charts As Images in Pentaho CDE - Export PopupComponent Example in Pentaho CDE

Hi Guys,

This post will describe the functionality of "Export PopupComponent" Component in CDE.

We can export the charts either in the form of Data(SQL result set) or as an image.

Export PopupComponent used for doing the above point in CDE as described.

This example developed On : CDE,CDF,CDA,CGG(

1) Row -Column ->ColumnName i.e., htmlObject(ExportOptionColumn).
2) Row -Column->ColumnName i.e., htmlObject(PieChartColumn)

Data sources
Write your query for a pie chart.

Components :
Design and develop pie chart..[Chart component Name : PieChart](Assuming you are already aware of it)

Now, export the above pie chart to image(png or jpg) [jpeg is not supporting yet -just tested] using "ExportPopUpComponent" component.

Core part
Others -->ExportPopUpComponent-->

Name : Export (Appear on the output)
Chart Component to Export : PieChart  [Pie Chart Name]
Data Component to Export : PieChart [Pie Chart result set will be exported]

Chart Export Label : Export Chart
Data Export Label : Export Data
(these two names appear in like tool tip on clicking "Export" )

Chart Export Type: png
Data Export Type : xls

HTML Object : ExportOptionColumn

NOTE : On the dashboard editor , on the working dashboard press shift+G which will pop up CGG window.. check the components for which you want to generate java script in the back end for images..  After checking desired charts to generate graphic in the back end save the dashboard..

See the preview of the dashboard..

Output action images :

Export as Image :

Export Data to xls

BI developer

Thursday, 10 July 2014

Tip: Remove/Disable Edit Option/Mode For .wcdf (CDE) Files in Pentaho 5.x Production Community Server

Hi Guys,

Below tip is used for disable Edit Mode of CDE dashboards ..

When this is useful ?
CASE 1 : 
If you are not going for Embedding BI to integrate the CDE dashboards.

CASE 2: 
If you don't want your end user to open the CDE file to view the CODE.. If you are using the Server itself in your end client location he/she also may not need all these except logging into the server and see the dashboards...

For doing this.. i.e., to disable the Edit mode of CDE dashboards, you just need to comment below code in plugin.xml file of pentaho-cdf-dd folder.

File location : /home/sadakar/softwares installed/Pentaho/biserver-ce/pentaho-solutions/system/pentaho-cdf-dd


Sample Image:

NOTE: As it is a configuration file, you need to restart the server once the changes done to it.

Another Imp NOTE: 
As C-Tools(CDE,CDF,CDA) are uneatable from Marketplace, and if you go for any upgrade of them then you might lost this configuration.. what it'll do internally when you upgrade to new versions of C-Tools, internally it'll uninstall the plug-ins and then download the new.. hence as you will get a fresh copy you there might be a chance of losing your settings.. This is not tested..

Thank you :-)

BI developer
Pentaho/Jasper/Talend ETL

Wednesday, 9 July 2014

Protovis Sunburst Chart Example in pentaho CDE -- A thanks blogpost to the CCC Master - Leao

Hi Guys,

This post will talk about the power of CCC charts(formally protovis charts) in pentaho CDE . I'd like to say thanks to the CCC- Master Leao on the tricky of converting CCC-TreeMap chart to Sunburst  Chart .

Yes, I believe a requirement makes us go in depth of subject though it'll take time. Here in this post I'd like to share what I have explored with the help of Leao.

You will learn below stuff :
1. What is Sunburst chart ? When it'll useful to visualize the data ?
2. What is the result set format of data to visualize ?
3. Code to Convert the CCC-TreeMap chart to CCC- Sunburst chart.
4. How to make the chart dynamic with input control / Keeping the chart in bootstrap panel /Pulling the input control right on the panel header and etc.

1. What is Sunburst chart ? When it'll useful to visualize the data ?(Collected points over net)
  • Sunburst chart is a nice way of presenting relational data sets together in a compact form. 
  • This chart type is very strong in summarizing data and it looks pretty. 
  • Sunburst chart is basically a stacked pie chart. 
  • It is created by stacking necessary amount of pie charts on top of each other
2. What is the result set format of data to visualize ?
For instance your data is of 2 or more of categories(2 or more string alike fields/columns) grouped by and 1 is of measure field (the value column).
Sample Result set looks like :
field1   field2 value
A         x         10
A         y         33
A         z         87
B         p        15
B         q        98
C         m       33
C         n        92
C         o        23

3. Code to Convert the CCC-TreeMap chart to CCC- Sunburst chart.
  • As of C-Tools(CDE,CDF,CDA) release of 14.06.18 release , there is NO direct CCC- Sunburst component available. 
  • Either you have to write protovis code in Protovis component under charts and implement OR go with D3 charts again with code implementation. 
  • You just have to take TreeMap Component from Charts section and set height ,width,htmlObject, datasource for TreeMap Componet and then in the PreExecution section write below code. 
function()  {
    this.cccType = pvc.SunburstChart;
    // Set any sunburst specific properties not available as same-named Treemap CDE properties here.
    // this.chartDefinition. ...

4. How to make the chart dynamic with input control / Keeping the chart in bootstrap panel /Pulling the input control right on the panel header and etc ?
  • This is as simple as we can do for other charts - i..e, create a parameter, give place holder for select component which takes the created parameter and make the chart in components and datasource query to listen the parameter  and add it as parameter to chart component and for query
Panel Code - Bootstrap HTML code. (Row-Column-HTML component and write below code)
<div class="col-sm-6">
<div class="panel panel-success">
  <div class="panel-heading">
  <!--  <h3 class="panel-title">SunBrust Chart Example in Pentaho CDE</h3> -->
    <h3 class="panel-title">SunBrust Chart Example in Penthao CDE<span class="pull-right" id="select1"></span></h3>
  <div class="panel-body" id="Panel1">
    Panel content

id=select1 is the place holder for input select which is pulled right on the panel title using pull-right property.

This is the way how you can represent the data on Sunburst chart.

Example : 
1) Software Ready : Pentaho Bootsrap supported version of C-Tools.
2) Browsers : Mozilla or Chrome preferable.
3) Database : foodmart database of postgresSQL.
4) Download Here : Click me to download the example 
5) Deployment in your environment: Download the zip file from step 4 and upload it from Pentaho PUC upload option.
6) Change the database settings according your setup if any.
7) Run the example - Open in new window.

Sample output Images : 
  Desktop Views with Click Action :

Image 1:
Image 2: 


Thank you for reading this document. Readers/Developers are encouraged to add your points/comments to improve this example in comment box.

Let's the community be on top of BI technologies :-) :-)

BI developer.

Friday, 4 July 2014

Introduction to WEKA GUI Chooser - Basic Layouts Explained(Are you bored of lengthy paragraphs of doucments?) - A quick write up.

Hi Guys,

This post will teach you basic layout structure of WEKA and in upcoming posts with examples.

Read my other article on how to download and install WEKA in Cent OS here

You can refer for the official documentation here : Click Here.
Alternatively you can find the pdf documentation in your WEKA installation :
Path : <WEKA installation folder>/weka-3-6-11/WekaManual.pdf

The official documentation gives you the clear & in depth details though I'd like to share in my words how the layout looks like.

1) Once you launch the WEKA, the window opens with 4 different options which are 4 different applications with in WEKA.

2) The 4 Applications are
     i) Explorer
    ii) Experimentor
    iii) Knowledge Flow and
   iv) Simple CLI

3) Also, the window has Menu contains options "Programs", "Visualization","Tools" & "Help".


Explorer An environment for exploring data with WEKA

Experimenter An environment for performing experiments and conduct-
ing statistical tests between learning schemes.

KnowledgeFlow An environment supports essentially the same func-
tions as the Explorer but with a drag-and-drop interface. One advantage
is that it supports incremental learning.

SimpleCLI Provides a simple command-line interface that allows direct
execution of WEKA commands for operating systems that do not provide
their own command line interface.

Thank you... Next post with some other basics on DataMining & with a simple example...


Installation of Pentaho WEKA(Data Mining Tool) in Cents OS 32 bit OS or Linux like Operating systesms.

Hi Guys,

Follow below steps to download and install WEKA software for predictive analytic s OR Data Mining.

Here we go with installation and in upcoming posts will move onto basics to start with WEKA. 

1) WEKA official Website :
2) Download WEKA :

For linux like OS's go to below text on page

Other platforms (Linux, etc.)
Click here to download a zip archive containing Weka
(; 24.2 MB)

3) Extract the zip file --> then it'll come as folder named as weka-3-6-11

4) Open the folder in Terminal and issue below command.

  java -Xmx1000M -jar weka.jar

5)  The WEKA HOME is here ...

Note that Java needs to be installed on your system for this to work. Also note, that using -jar will override your current CLASSPATH variable and only use the weka.jar.

For other installation types in other OS's visit the official website.(Click here)

Thank you..

(Just started WEKA after having 2+ years of working exp on Open source technologies ..)