Teachers

 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 its index */
    //var valueColIndex3=1; /*initializing 1st column with its 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

Hi Guys,

Recently I came across a situation on plotting the 0 values result set on a pie chart and failed to produce the chart with out leaving any message.

Alternatively I have shown a message saying that "All the values in the result set are zero so can not plot the visualization".

Of course, this is not a big matter to blog but I can say that this is a best practice in report designing and would like narrate for the one who wish to follow best practices.

Your end-client can easily understand when the chart is having all zero values.

Example :
Query : SELECT field1, field2, field3 FROM dummyTable
Output : 
field1      field2  field3
----------      ---------   ---------
  0              0           0

For instance assume you are showing data  on pie chart and given expressions in 3 different series lets say "series1", "series2" , "series3".

Now, if you see the preview of the report, you will be getting nothing in output as your query resultset has all values as 0



Drag and drop the "Static Text Field" over the chart from palette section and write your message(message is : All values are zero so can not plot the visualization) and Print this field when ever the values are zero.

For doing this, you have to Write similar like below expression for "Static Text Fields" PrintWhenExpression

($F{field1}==0) && ($F{field2}) && ($F{field3})

Observation or Preview.
Now select some input controls for which you will get result set  0 .. You will get the below similar like output.


Thats it... When ever your query result set is having all 0s you will be displaying message saying that can not plot visualization.


Sadakar
BI developer