Tip: Chosen jQuery Single Select input control in Pentaho CDE - Will it helpful to you instead normal inputs ?

Hello guys,


 1) set parameter/listener for your select component. (i.e., you have already have a parameter created).

2) There is a property called JQuery Plugin  for select component
 give value as  "chosen" for this property

3)
Write below code for Select component "Post Execution" section and change % values or you can use pixels(px) in place of %'s.

function f(){
           $('select').chosen({ width: '200%' });
           $('.chzn-single').css('width','200%');
           $('.chzn-drop').css('width','200%');
           $('.chzn-search input').css('width','83%');
}
3) Check the output..

Some points to note down..

Can you make this input responsive ?
Will the above code affects the serach & drop down width for multi select ?(This tip works only when 

References

http://harvesthq.github.io/chosen/

http://harvesthq.github.io/chosen/options.html

A first experiment on D3 bar chart in pentaho CDE - Queried output on Bar Chart with few limitations

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 ? Let's have a look at this static bar chart example in CDE( check this : http://jsfiddle.net/enigmarm/3HL4a/13/ )

Image 1:  Ascending

Image 2 : Descending

Default:

To get the above static charts on CDE output... let's 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/

Conditional coloring of cell values in pentaho CDE table component & Table component customization

Hello Guys,

This post will talk about one of the major component of pentaho CDE. i.e., Table component.

Table component is offering many features like sorting, searching, pagination, selected number of rows per page on the dashboard....

Some times we may not need these built in features but need our own features like conditional coloring of cell values .. In that case the below customization tips might useful for you..

This post will brief about below features. 
1. Removing default row banding.
2. Give back ground color table headers.
3. Group by table component.
4. border to all the cells
5. bar Type cell feature along with value.
6. trendArrow cell type feature along with value.
7. circle type cell ( not customized yet, will try to workout in future posts).
8. color change of pagination buttons
9. conditional coloring of cell values.

Future work outs on the table component. 
1. Expand rows.
2. Removing groupHeader preSpace & groupHeader postSpace (if any one done this please post your solution in comment box).
3. Difference b/w Hyperlink type cell and hyperlink on table rows.
4. Group by summation values.
5. Summation of table columns either page wise or total. 

I'd already posted tool tip on table component cell values. 
You can refer that example here along with example code .

Readers of this post is encouraged to add your solutions for the future work outs if you have already done in comment box.. That will be very helpful for the community developers. 

Of course, most of the things can be done using css overriding but it is a bit matter on implementing in CDE.

Software Setup :
Pentaho BA Server 5. 0.1 CE , C-Tools(CDE,CDF,CDA) of Trunk (after 14 verion), foodmart database of postgesql(formally jasper server back end database), Mozilla fire fox along with fire-bug Addin.

I'd like to share the core part of the example and the remaining things you can see by opening the example(source code) attached later here.

Basics about table component. 
1. You can give types (CDE+data tables types) to the columns for table component(which differs from
2. You can insert charts for the cell values.
3. You can  give hyperlink types (should take hyperlink values - not the regular hyperlink)

Steps :
1. From Lay out section  Row - Column(give html Object Name)
2. From Datasource section give code an SQL query for your table component.
 For example: (Name it : query1_TableComponent) assuming you are already aware of giving connections.
select distinct state_province AS state,
city,
CASE WHEN sum(unit_sales)<=5000 THEN sum(unit_sales) ELSE 9000 END   UnitSales,
CASE when sum(store_sales)>=3000 then sum(store_sales) ELSE -sum(store_sales) END StoreSales,
sum(unit_sales) UnitSales2,
sum(store_sales) StoreSales2,
sum(unit_sales) UnitSales3,
sum(store_sales) StoreSales3
from sales_fact_1997 sf7
INNER JOIN customer c ON sf7.customer_id=c.customer_id where country like 'USA'
group by state_province ,city
ORDER BY state_province ,city
Output actions proprery:
Give indexes to your query output: In the above query you are having 8 columns so you will be giving 0,1,2,...7 indexes. 

3. Components section :
Add table component as you generally do and name it and give datasource name and give below types to each of the columns.. 

In the Pre-Execution section you need to write Add-ins code for customization. If you don't write add-in code, the cells will take default behavior of types. 

 function f(){  
//conditional coloring of cells
    this.setAddInOptions("colType","formattedText",function(cell_data){
       
        var percentage = cell_data.value;
       
        if(cell_data.colIdx == 7) // column index
        {
            if(cell_data.value === '' || cell_data.value === null) //checking the null possibility
            {
                this.value = '00000';
            }
        }
       
        if(percentage <=8000){
            return {  textFormat: function(v, st) { return "<span style='color:red'>"+v+"</span>"; } };
        }
         else {
            return { textFormat: function(v, st) { return "<span style='color:green'>"+v+"</span>"; }  };
            }
      });
     
     
    //bars 
    this.setAddInOptions("colType","dataBar",function(column4){
       return {
                includeValue:true,
                widthRatio: 0.8,
                startColor: "red",
                endColor: "green"
              };
    });


    //trendArrow
    this.setAddInOptions("colType","trendArrow",function(column5){
       return {
            includeValue: true,
            good: false,
            valueFormat: function f(v) { return "$" + sprintf('%d', v/1000) + 'k'; }


       };
    });
   
     
 }//main function ending

The core part of this post is conditional coloring of cells... the first part of the above code will conditionally apply the color to the cells.. it'll give some idea on how to increase the chances of customization on table component. 

The other goal of the post is : removing and styling table component using css code. .

Either you can apply below css code in External file and locate or A Code snippet for the dashboard.. 
In this demonstration, I've taken it in Code Snippet and code is :

body{
    margin-top: 0.5cm;
}
.overflow {
overflow: auto;
}

#The below will remove default -row banding
table .even, table .odd{ background-color: white; }
table
{
    align-left:200px;
}
#This will add back ground styling for table header row
table th {
    background: black;
    border: 1px none #B3B3B3;
    color: #FFFFFF;
    font-family: Century Gothic;
    font-size: 14px;
    font-weight: bold;
    text-align: center;
}
#This will add borders to the cells
table td {
    border-left: 1px solid #A9A7A1;
    border-right: 1px solid #A9A7A1;
    border-top:1px solid #A9A7A1;
    border-bottom:1px solid #A9A7A1;
    height: 20px;
    font-size:14px;
    /*padding-left: 10px;*/
    vertical-align: middle;
} 
#This will be useful for changing the style of pagination symbols.
.paging_full_numbers span.paginate_active {
    background-color: #FF0000;
}
.paging_full_numbers span.paginate_button, .paging_full_numbers span.paginate_active {
    border: 1px solid #008000;
    cursor: pointer;
    margin: 0 3px;
    padding: 2px 5px;
}

.paging_full_numbers {
    line-height: 18px;
}
.dataTables_paginate {
    text-align: center;
}


 Download the example here and play around with it. 

or

https://drive.google.com/file/d/0BymV_QP4TGBEU09kN1VUVGJRdVE/edit?usp=sharing

 Sadakar
BI developer






  

Tip - Change Tooltip back ground color in pentaho CDE dashboards

Hi Guys,

A small tip on how to change/override  the default tool tip color and text in it.

Open your dashboard using Mozilla browser & make use of firebug tool as shown in below figure.

In the image just changed the css code.. for your particular dashboard you have to override the the css code by adding Resource(Either code snippet or External css file - you need to name the file if you take code snippet OR you need to locate the .css file if you take External file) ...

i.e., in Code snippet you have to write.

.tipsy-inner {

    background-color: red;

    color: #FFFFFF;

    max-width: 200px;

    padding: 5px 8px 4px;

    text-align: center;

}

 

 

Default Background and text color are : Black & white

 

Sample Image after changing the color

 

In the same way one can change/override the css code for your input controls and for other components(like table component)

:-) :-)

Future update in the same post : Need to find :
Dynamic tooltip back ground & border and text color
i.e., You can find tool tip of HTML5-Charts .. If you hover the mouse on green colored bar or slice of a chart the tooltip border has to come in the same color along with text included in the tooltip..

If anyone knows this tip already feel free to share your tip in the comment boxes...


Thank you.

Sadakar
BI developer.

High Performance Dashboard - Sales Dashboard 4 Demonstration in Pentaho CDE /Bootstrap Tabs Example in Pentaho CDE

Hi Guys,

Features of the dashboard :

1) Visualize Single value using Ivy Gauge component. Either a Gauge or doughnut.
2) Display single value in bootstrap panel with formatted styles.
3) Videos on your dashboard using Ivy Video component. 
4) Bootstrap Navigation tabs feature in pentaho CDE dashboards.
     - Single place/Page to show multiple dashboards. 
      - Why to drill down when you have the feature of navigation of tabs in single page itself.
      - Not only the navigation bars but also the drop down tabs of navigation
         in single page itself.
5) ICC - Inter chart/component communication in the single page.
6) Popups
     - click on a slice of pie chart and open a table report in popup or again a chart report.
7) Chosen JQuery input components (OR) Bootstrap input components.
8) Tool tip color change.
9) iPad/laptop/desktop/TV like big screen  supported dashboard ( Need to check in Mobile)
10) Formatted table component included.
11) Embeddable in 3rd party application.

The powerful Business Analytics Server - Pentaho

Thank you pentaho & webdetials for the awesome product.

This post will be up-datable as soon as the development of example done ..

The first look of the dashboard images for my regular readers.....

Click me for sample download

:-)






 


      

Popup Component Example in pentaho CDE - Popup on Pie & in the popup showing bar chart or any other CDE component

Popup Component Example in pentaho CDE

Hi Guys,

This post will talk about the popup component usage, tips & tricks included in it to make it work properly.

As a developer point of view this popup component is suitable on line charts or area charts  or dot/bubble charts but can also implement on any component like button, pie chart, bar chart and etc.

This post is a replica of already available example in CDE reference in the BA server. Just worked out with a pie chart pop up component, with parameters and would like to share the procedure.

Software Set up :
BA Server 5.0.1 stable, C-Tools of Trunk version & foodmart database of postgresql(formally jasper server postgresql).

Who can benefit using this functionality ?
The end user may not want vertical scroll down for detailed chart report /information report  & also doesn't want drill down. In that case we can make use of this popup component to meet your client requirement.

Thank you Pentaho & web details for the awesome feature in Pentaho .....


Few points from Pedro's blog post & material

Popup Component Reference

1. You can create standard layout element & place it inside a popup.
2. All the generic interaction rules can be applied on popup component.
3. Any html component(button) & any component(charts, images, table cell)  from CDE can be used for binding the popup.

Following Pedro's 3 steps Rules the example is explained below.
3 steps are
Defining the content that will appear on the popup
Configure popup component
Call the popup

Example Taken in this post:
1) Button Component popup (not explained - Edit the file and observe once you deploy the example given )

2) Chart components popup

Main Chart : Pie Chart
Popup Chart : bar chart

Functionality : On clicking any slice of the pie chart the related information should come in the    bar chart ( like ICC(Inter Chart Communication) & like drill down functionality)
Defining the content that will appear on the popup

1. Define the lay out as you'd normally.
2. When you configure the popup component , the component will be responsible for detaching teh selected elements from the dom and only showing it when the popup is called.

Step 1 & Step 2  in Simple terms :
Develop pie chart & bar chart as you normally do.. Remember as bar chart will come in popup it should have row name ,also bar chart should take parameter in it's sql query ( No need for pie chart sql query b'z on clicking of slice the category name will become the parameter input and that parameter you have to pass for bar chart & before to bar chart you have to pass the same parameter to popup component )

Step 1: Develop Main Chart where you are going to perform click for popup ( pie chart)
a) Take a row and name it as : PieChartDemo
b) Under this row take a column & name it as : PieChart
c) Define a query for populate pie chart ( for example :
i.e., define data source with name : query2_forPieChart

SELECT distinct state_province,sum(store_sales) FROM customer c INNER JOIN region r ON c.customer_region_id=r.region_id
INNER JOIN sales_fact_1997 sf7 ON c.customer_id=sf7.customer_id
group by state_province
order by sum desc

d) Define the pie chart(make it dough nut chart using extension point)
Html object for this is : PieChart ( column name from step b)
Data source : query2_forPieChart
& set height & width and other properties.

Step 2: Develop popup Chart which will appear on clicking of slices of pie chart( formally this is a bar chart in popup)
a) Take a row and name it as : PopUpContent2
b) Under this row take a column & name it as : ChartForPopup2
c) Define a query for populate pie chart ( for example :
i.e., define data source with name : query1_forPopUp

SELECT distinct sales_city,sum(store_sales) FROM customer c INNER JOIN region r ON c.customer_region_id=r.region_id
INNER JOIN sales_fact_1997 sf7 ON c.customer_id=sf7.customer_id
where sales_state_province=${Param1_state}
group by sales_city
order by sum desc
limit 5

d) Define the bar chart
Html object for this is : ChartForPopup2 ( column name from step b)
Data source : query1_forPopUp
& set height & width and other properties.

Step 3: Defining parameters :
Create a parameters :
1) For state selection on click of pie chart slice : Param1_state
2) For color selection of slice : param3_color

Step 4: Define the popup component :
a) Click on components Panel
b) Click on others then click on Popup component & name it as PopUpComp2
c) Html Object :
This plays the main role in popup .
PopUpContent2 is the htmlObject for this popupcomponent( Row name of bar chart)
d) Add parameters & listeners to this component (Param1_state)


Step 5 : Let's back to the chart properties :
Pie Chart:
a) Make pie chart clickable=ture
b) In the click action write below code

function f(e){
  var color = this.pvMark.fillStyle();
  Dashboards.fireChange('Param1_state', e.vars.category.value);
  Dashboards.fireChange('param3_color',color.color);
  alert("you have clicked on"+" "+Param1_state); 
  render_PopUpComp2.popup($(this.event.target));
}
c) No need of adding parameters to the chart component in the properties.

In the above code :
Line 1: slice color will store in color variable
Line 2 : Picking the state name ( category name ) into Param1_state parameter.
Line 3: storing the color code of line one in param3_color parameter.
Line 4 : Testing of state clicked
Line 5 : Popup rendering. PopUpComp2 is the row name(htmlObject name) which you want to show on the popup .

IMP NOTE :
The rendering of popups for CCC2 version charts is slightly changed from the normal behavior in terms of code. It'll take event target.

Bar Chart:
a) set parameters & listeners ( Param1_state & param3_color).
b) In the postFetch area write below code for color filling of bars

 function fun(){
 var selectedColor = Dashboards.getParameterValue("param3_color");
    if(selectedColor) {
        this.chartDefinition.colors = [selectedColor];
    }
 }

Save your dashboard and test it..

Sample output will looks like below images. 

Download Example here :
Click here 
OR
https://drive.google.com/file/d/0BymV_QP4TGBEVGN1d1BGOGtlSXM/edit?usp=sharing


meet me at sadakar.1988@gmail.com for any queries / other workouts / suggestions and any thing related to BI in pentaho & jasper ..


Thank you for reading this post.

Sadakar Pochampalli
BI developer - Pentaho/Jasper/Talend
Hyderabad, Telangana.

Pentaho Community Edition CE 5.0.1 stable Vs Jasper Professional 5.5 - BA Server & Tool selection decision making points on 3 categories

Hello Guys,

Here is a small document prepared on differentiating Pentaho Open source Vs Jasper Professional.

Based on below information one can take decision in tool selection for BI needs.

I would like to present a clear insight & readers of this document are encouraged to add your points in the comment box..

IMP NOTE:  Remember , I am comparing the Pentaho Open source(Community) 5.0.1 stable  with Jasper Professional(licensed) 5.5 

Dashboards :

Key feautue	Pentaho Open source Dashboard	Jasper Professional dashboard
Tool	Community Tools(C-Tools) – Server plug ins CDE(Community Dashboard Editor) CDF(Community Dashboard Framework) CDA(Community Data Access)	iReport/Jasper Studio Ad-hoc Reports Desktop stand alone tools and Professional Dashboard designer on server
Development of Reports done at	Fly on the server itself	Depends on stand alone designing tools & need to publish to the server and again need to design the dashboard
Responsiveness /Mobile support	Responsiveness is achievable using bootstrap css framework	Lack of responsiveness even though there is Proportional property of the dashboard designer
Dashboards output end view-able devices	Compatible with Mobile/iPad/laptop/Desktop/TV like big screens	Compatible with laptop/desktop
Charts	Protovis charts implemented, extend able to D3 charts.. can integrate fusion and HTML charts also.	Embedded charts engines – Jfree, Fusion & HTML5 charts. Can integrate protovis/D3 charts using html component of iReport/Jasper Studio
Loading of charts on the dashboard	Will take lesser time using Pentaho's C-Tools  powerful caching mechanism	Will take longer time to load the charts on the dashboard
Embedding BI	Achievable using iframe tags or java script	Achievable using iframe tags
Community support	Active	Active as it is a professional .. general community is not so active
Interactivity/Drill down/ Inter charts(frames) communication	Achievable	Achievable
Multi-tenancy	Need to check	Achievable
Customization	Developer friendly	Not developer friendly
Download here	Pentaho MaketPlace or install using ctools-installer.sh http://community.pentaho.com/	Purchase license https://www.jaspersoft.com/four-ways-test-drive-jaspersoft-bi-software

Analysis : OLAP Cubes

Key feature	Pentaho Open source Analysis	Jasper Opensource/Pro Analysis
Desktop Stand alone development Tool	Pentaho Schema workbench	Schema Workbench
Front End tool to view the output	Pviot4J Analytics (or) Saiku Analytic's (or) JPivot View (or) BTable (or) Ivy Schema Editor	JPivot View Ad-hoc Editor
Ad-hoc Reporting on Cube out put	Yes, using Saiku Analytics	Ad-hoc Editor (For jasper pro & enterprise.Iit is an add-on feature. http://www.jaspersoft.com/editions
Query Language	MDX ( Multi Dimensional Expression)	MDX (Mullti Dimensional Expression)
Caching	Enabled	Enabled
Community Support	Very Active – Quick Responses	Not so active
Documentation	Online documentation available with examples	Online documentation is available with Examples

Ad-hoc Reporting

Key feature	Pentaho Open source Ad-hoc	Pentaho Enterprise Ad-hoc	Jasper Pro Ad-hoc
Source for ad-hoc reporting	Meta data layer creation	Meta data layer creation	Create topics OR Create Domains
Tool	Pentaho Meta Data Editor(PME) (Stand alone desktop tool) (OR ) Create JOINS on the fly on the server and use it for ad-hoc reporting	PME	No desktop tool.. directly on the server create Domains OR topics and use Ad-hoc editor
Output Server tools	WAQR – Web Ad-hoc Query and Reporting(legacy) (OR) Saiku Reporting compatible with 4.8 server as of now	Interactive Reporting - Advanced of WAQR But WAQR is not supported by any more. Interactive reporting is an extension of WAQR in Enterprise Version of pentaho	Jasper Ad-hoc Editor
Support	less	On demand support	On demand – Need to be jasper customer
Visualization of Data in the form of charts	No	No	Yes

Readers of this post is encouraged to add your points/suggestions in comment box.. 

Thank you. 

Sadakar

BI developer