Multiple selection in Pentaho CDE Dashboard, part 1

There are many ways to create a selector with multiple selections în Pentaho 5 with CDE, but this one works well for what i want.

1. Create layout. 

For this demo in layout i create only 3 elements (2 rows and 1 javascript code snippet) with default settings:
- first row with name selector – htmlObject for selector
- second row with name table – htmlObject for tabel
- resource (javascript code snippet) with name javascript_snippet ( see point 4)

2. Create datasources. 

In this demo i choose SampleData as database.
We need 2 datasources: one for selector (sql over sqlJndi ) and one for table (mdx over mondrianJndi).

 a. Create a sql over sqlJndi datasource with this parameters:
- Name: sqlPositions
- Jndi: SampleData
- Query: SELECT DISTINCT POSITIONTITLE FROM QUADRANT_ACTUALS ORDER BY POSITIONTITLE ASC
This datasource return Positions values which populate the selector.

b. Create a mdx over mondrianJndi datasource with this parameters:
- Name: mdxTabel
- Jndi: SampleData
- Mondrian schema: SampleData
- Query: with set [Positions_Positions_Set] as '${mdxPosition}' set [Region_Region_Set] as '{[Region].[Region].Members}' set [Department_Department_Set] as '{[Department].[Department].Members}' set [Measures_Set] as '{[Measures].[Actual], [Measures].[Budget]}' select NON EMPTY [Measures_Set] on COLUMNS, NON EMPTY Crossjoin([Region_Region_Set], Crossjoin([Positions_Positions_Set], [Department_Department_Set])) on ROWS from [Quadrant Analysis]

- Parameters: mdxPosition

With this datasource we get the result of selection.

3. Create components. 

a. Create selector.

From Selects panel choose “SelectMulti Component” and set this values as parameters:
- Name: multiselect
- Parameters: mdxPosition
- Datasource: sqlPositions

b. Create Table.

From Others panel choose “Table Component” and set this values as parameters:
- Name: table
- Listeners: mdxPosition (parameter from selector)
- Parameters: Arg - mdxPosition and Value – mdxPosition

c. Create parameter.

From Generic panel choose “Custom Parameter” and set this values:
- Name: mdxPosition
- Javascript code: function(){return mdxPosition;}

4. javascript_snippet.

Here i'll take all the values and i'll construct the ${mdxPosition} parameter to be used in mdx over mondrianJndi (mdxTabel):

$(document).ready(function () {
    // set default value to start with
    var mdxPosition = "{[Positions].[Positions].Members}";
    Dashboards.setParameter("mdxPosition", mdxPosition);

    // function to get selected values
    function select_option_live(col) {
        var arr = [];
        $('#' + col).find('option:selected').each(function () {
            arr.push($(this).val());
        });
        return arr;
    }
    // what's happened on select
    $("#selector").on("change", function () {
        var val, mdxSel;
        // val is an array of selected values
        val = select_option_live("selector");

        // if all values are selected theres no need to send all, else construct filter
        if ($("#selector option:selected").length == $("#selector option").length) {
            Dashboards.fireChange("mdxPosition", mdxPosition);
        } else {
            mdxSel = 'Filter(' + mdxPosition + ',(';
            var arr = [],
                filterVal = '[Positions].[Positions].CurrentMember.Name = ';
            for (var i = 0; i < val.length; i++) {
                arr.push(filterVal + '"' + val[i] + '" OR ');
            }
            mdxSel += arr.join("").toString().substr(0, arr.join("").toString().length - 3) + '))';
            Dashboards.fireChange("mdxPosition", mdxSel);
            // console.log(mdxSel);
        }
    });
});

This is just a simple dashboard. Enjoy.