Hitachi Vantara Pentaho Community Wiki
Skip to end of metadata
Go to start of metadata

This action executes the specified Javascript. Parameters specified as inputs will be available to the script for use. The JavascriptRule can have one or more outputs.
The component can also define library elements in the component definition. Each specified library file must exist in the solution, and will be pre-pended to the script that's specified in the component definition. In this way, you can create a library of commonly used javascript code, and include it at runtime execution.

Component Name: JavascriptRule

Inputs:

REQUIRED
Javascript - The Javascript to be executed.
Imported Javascript - Any files in the solution containing Javascript functions referenced by this Javascript.

OPTIONAL
Any inputs to the action sequence and any outputs from preceding actions in the action sequence can be specified. Inputs will be available as a variable to the scripting engine.

Outputs:

Any variable in the Javascript may be specified as an output from the action. Any output that is defined in the outputs, and not actually assigned a value in the Javascript Rule will be created and assigned a null value when the rule finishes execution.

Using query results in JavascriptRule

The query result from SQLLookupRule, MDXLookupRule, XQueryLookupRule, HQLLookupRule and MQLRelationshipDataComponent all generate an output of type IPentahoResultSet usually named query_result. You can access the data directly through JavaScript.

You can also create a result set of type JavaScriptResultSet which implements IPentahoResultSet and can be used by components that accept a IPentahoResultSet like the reporting component.

Some JavaScript examples for working with result sets

getColumnCount()                   // Number of columns
getRowCount()                      // Number of rows
getValueAt( row, col )             // Returns the value os a cell, row and column are zero based
setColumnHeaders( array )          // array size should match the number of columns
setColumnHeaders( array1, array2 ) // array1 for col headers, array2 for column types
addRow( array )                    // array is a data row to add to the end of the result set
getMetaData()                      // Returns a IPentahoMetaData object


// Example: Create a result set

var results = new JavaScriptResultSet();
results.setColumnHeaders( 'DIAL_DATA', 'DIAL_MIN', 'DIAL_MAX' );
results.addRow( new Array(57.0, 0.0, 100.0) );

// Example: Create a result and access it's data

var results = new JavaScriptResultSet();
results.setColumnHeaders( 'REGION' );
results.addRow( new Array( 'Eastern' ) );
results.addRow( new Array( 'Western' ) );
results.addRow( new Array( 'Southern' ) );

firstrow = results.getValueAt(0,0);                      // returns '[Eastern]'
lastrow = results.getValueAt(results.getRowCount()-1,0); // returns '[Southern]'
columns = results.getColumnCount();                      // returns 1
rows = results.getRowCount();                            // returns 3


// Other useful methods 

var meta = result_set.getMetaData();      // Returns an IPentahoMetaData object
var colCount = meta.getColumnCount();
var colHeaders = meta.getColumnHeaders(); //getColumnHeaders() returns object[][]

var i = 0;
for ( i = 0 ; i < colCount ; i++ ){
    colName=colHeaders[0][i];
     // use colName for something
}

  • No labels

2 Comments

  1. user-b9c55

    // dump resultset to string (html table)
    var retVal = "";
    if (result == null || result.getMetaData().getColumnHeaders().length == 0)  {
        retVal += "Empty";
    } else {
        retVal += "<table><tr>";
        var colHeaders = result.getMetaData().getColumnHeaders()[0];
        for (var i = 0; i < colHeaders.length; i++) {
            retVal += "<th>" + colHeaders[i] + "</th>";
        }
        retVal += "</tr>";
        for (var i = 0; i < result.getRowCount(); i++) {
            retVal += "<tr>";
            for (var j = 0; j < colHeaders.length; j++) {
                retVal += "<td>" + result.getValueAt(i, j) + "</td>";
            }
            retVal += "</tr>";
        }
        retVal += "</table>";
    }
    
  2. user-1565f

    Use the Result Set with a Google API Visualization Object:

    See the WebEx here: http://wiki.pentaho.com/display/COM/Community+Technical+WebEx