Fetch selected columns from SQL query on Table Component using Penaho CDE

 Hi....
A requirement made me to write this post which will talk about fetching few columns on the table component from SQL query.....
Requirement :
Get 5 columns using query in CDA but fetch only 4 columns on the table component...
Fetch columns first column to fifth column excluding 4th column...

Write below code PostFetch of table Component.

function removeColumns(cdaData) {
var valueColIndex = 3;
    //var valueColIndex2=2; /*initializing 2nd colum with it's index */
    //var valueColIndex3=1; /*initializing 1st column with it's index */

    // Remove metadata column
    cdaData.metadata.splice(valueColIndex, 1/*remove count*/);
    //cdaData.metadata.splice(valueColIndex2, 1/*remove count*/);
    //cdaData.metadata.splice(valueColIndex3, 1/*remove count*/);

    // Remove resultset column, from each row
    cdaData.resultset.forEach(function(row) {
    row.splice(valueColIndex, 1/*remove count*/);
    //row.splice(valueColIndex2, 1/*remove count*/);
    //row.splice(valueColIndex3, 1/*remove count*/);
    });
    // Return modified cda data set
    return cdaData;
}
Write below code in Pre Execution of Table Component
//Specify the columns which you are removin
function f() {
this.chartDefinition.readers = [
  {indexes: 3 /* Value2 */},
    //{indexes: 1 /* Value2 */}
      //{indexes: 2 /* Value2 */}
 
];
}

Alternative solution which will reduce writing above code :

Write below code in "Post Execution" of table component. column indexes start from 1 ,2,3 and etc if you write this code.

function myTip()
{
$('th:nth-child(5),td:nth-child(5)').hide(); //hides 5th column

$('th:nth-child(),td:nth-child(10)').hide();//hides 10th column
}





NOTE:
1) Query fields indexes start from 0 and ends with N-1 where N is the number of fields coming from query.
2) You can fetch as many columns from the query result set as you want.
3) See the commented code to remove the other columns...

NOTE that the code in Pre Execution may not accurate but worked fine for the requirement.

Thank you.


References :
1) http://forums.pentaho.com/archive/index.php/t-143075.html
2)  http://forums.pentaho.com/showthread.php?143075-specifying-columns-for-pie-chart-in-CDE
3)http://www.webdetails.pt/ctools/charts/jsdoc/symbols/pvc.options.charts.Chart.html#readers