Search code examples
mysqlpentahopentaho-cde

MySQL query parameters in a Pentaho CE dashboard

First, I'm on Pentaho CE 8.0. And I'm not an expert on Pentaho.

The question seems simple but I cannot get it working. I'm trying for a dashboard to use a simple parameter for a WHERE condition in a MySQL query. The Bootstrap layout has 3 columns, one for each component (filter, text, table).

Simple parameter: 

- Name: salesrep_selection  
- Property value: mike

Filter component (to select the sales rep): 

- Name: salesrep_selection_filter  
- Parameter: salesrep_selection  
- Values Array: [["mike","Mike"],["paul","Paul"],["peter","Peter"]]  
- Value as Id: false

Text component: (just to check that the parameter is set up): 

- Name: selection_show_text  
- Listeners: ["salesrep_selection"]  
- Expression: function() {return this.dashboard.getParameterValue("salesrep_selection");}

Table component (customers list by sales rep): 

- Name: customers_list_table  
- Listeners: ["salesrep_selection"]  
- Datasource: customers_list_data

Datasource (MySQL query): 

- Name: companies_list_data  
- Jndi: business_datawarehouse  
- Query: SELECT customerid,customername FROM customers WHERE salesrep=${salesrep_selection}  
- Parameters: [] (empty)

When I start the dashboard, I get my selection list with the sales reps names. When I choose one, the id is displayed in the text component, but the table remains empty "No data available in table".

If I hard code the condition "WHERE salesrep='mike'", I get the list of customers.

With the parameter "WHERE salesrep=${salesrep_selection}", the MySQL query log shows me that it gets "WHERE salesrep=null". So the parameter is not sent to the query.

I also tried to set up the datasource parameters with [["salesrep_selection","salesrep_selection"]], but the table remains empty "No data available in table", and the MySQL query log shows "WHERE salesrep='salesrep_selection'".

I searched a lot on internet, not a lot of articles about this, but anyway, what I found is exactly what I implemented. And still I don't get anything working.

Are the parameters handled differently in Pentaho CE 8.0? The queries parameters too?

Solution

  • You need to pass parameter from component to datasource.

    1. Add parameter called salesrep_selection with value salesrep_selection to Table component's property Parameters. This will set up the parameter in the component and it will pass its value to the datasource.
    2. Add the same parameter in the Datasource's property Parameters. This will set up the parameter in the datasource and it will pass its value to the query.

    Table component (customers list by sales rep):

    - Name: customers_list_table  
- Listeners: ["salesrep_selection"]  
- Datasource: customers_list_data  
- Parameters: [["salesrep_selection", "salesrep_selection"]]

    Datasource (MySQL query):

    - Name: companies_list_data  
- Jndi: business_datawarehouse  
- Query: SELECT customerid,customername FROM customers WHERE salesrep=${salesrep_selection}  
- Parameters: [["salesrep_selection", "salesrep_selection"]]