AnsweredAssumed Answered

MySQL query with simple parameter

Question asked by Sylvain LE MAT on May 19, 2018
Latest reply on Oct 22, 2018 by Juanjo Ortilles

Hi Guys,

 

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 work. 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 I don't get anything working.

 

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

 

Thank you in advance for your advices.

Outcomes