Sorting & limit the bars on bar charts in Pentaho CDE using java script and without ORDER BY and limit functions in SQL Query ( Descending order)

Hi Guys...!!!
This post tech you how you can make your bar chart dynamic..
You can limit the number of bars on bar charts with out using the "limit" function in your query as well you can also sort the bars in ascending or descending order with out using ORDER by clause  in SQL.

Some times you might have to deal with this type of functionality in your dashboard.
So here is a working example. Follow the steps...

Environment:
Pentaho BI server 4.8 stable with C-Tools(CDA,CDE,CDF 13.06) installed.
PostgreSQL

Aim : 
1) Creating a dashboard with two horizontal bar charts.
2) How to use single query result set for two charts
3) Order by the bar on the dashboard with out writing ORDER BY clause in SQL.
4) limit the number of bars on charts with out writing "LIMIT" clause in SQL

Suppose your result set is like below

Query Eg :
SELECT ColumnA, columnB,ColumnC from table_Name  GROUP BY ColumnA

Result set Assumption:
ColumnA          ColumnB       ColumnC
pentaho               45                     75
jasper                  23                     34
pdi                       90                     22
and assume there are 30 rows in the result set ...


You are using single query to plot the two charts on dashboard using ColumnA,ColumnB & ColumnA,ColumnC.

When you use ORDER BY clause in the query, you can only either order by columnB or ColumnC but you can not order by with ColumnB and ColumnC.


* In CDE, there is a functionality called "Indexs". Indexs for columns in pentaho CDE starts from 0,1,2 and etc.
* So ColumnA index is 0, ColumnB index is 1 and vice versa.

* Prepare your environment for the dashboard and have a look at the "Data sources" now.
* Click on "Output Options" and give index values as 0,1,2 by clicking "add" button multiple times.

How to get ColumnA, ColumnB on first chart ? (B'z you are using single query result set)
* Now click on the first chart component
* In the Advanced properties click on "Pre Execution"
* Write the below code to fetch ColumnA, ColumnB on first bar chart.

function f() {
      this.chartDefinition.readers = [
                                                        {names:'category', indexes: 0},
                                                          {names: 'value', indexes: 1},
                                                            {indexs: 2}
                                                       ];
}

NOTE:
ColumnA=category and  is accessed with Index 0
ColumnB=value and is accessed with Index 1
index2 is written in the code b'z to ignore the value appending to the category showed on bars.

How to sort and limit the bars on first chart ?(B'z you are using single query result set)

* Click on the "Post Fetch" option from the Advanced properties.
* Write this code, this code will limit the number of bars on chart and sort the bars in descending order.
* In the code below "param_no_of_end_points" is the parameter created in the "Generic" section.
* And for the parameter create a select in the "Selects" section with "Text input" component.


function f1(cdaData) {
    var categIndex = 0;
    var valueIndex = 1;   
   
    var param_no_of_end_points = +Dashboards.getParameterValue("param_no_of_end_points");
    
    if(isNaN(param_no_of_end_points))
    {
        param_no_of_end_points = 0;
    }
       
    var resultset = cdaData.resultset.slice();
    var compareDesc = function(a, b)
    {
        return a === b ? 0 : a > b ? 1 : -1;
    };
    resultset.sort(
                    function(rowa, rowb)
                    {
                       return compareDesc(+rowa[valueIndex],+rowb[valueIndex]);
                    }
    );
   
    if(param_no_of_end_points > 0)
    {
        resultset.splice(0,resultset.length-param_no_of_end_points,0);
        cdaData.resultset = resultset;
    }
    cdaData = {
        metadata: cdaData.metadata,
        resultset:  resultset
    };
    return cdaData;
}

Sources for the CODE:

1) http://forums.pentaho.com/showthread.php?148352-Order-by-on-bar-chart-with-out-touching-the-query-in-CDE

2)http://www.w3schools.com/jsref/jsref_splice.asp
  and etc.

Write the same code for the second chart but make sure to use different function names and Index value. In an application we can not write two functions with same name.
 
Save the the dashboard and see the preview.


Sadakar
BI developer