Pentaho CE仪表板中的MySQL查询参数 [英] MySQL query parameters in a Pentaho CE dashboard

问题描述

首先，我使用的是 Pentaho CE 8.0.，而我不是 Pentaho 的专家.

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

这个问题似乎很简单，但我无法解决.我正在尝试使仪表板在 MySQL查询中针对WHERE条件使用简单的参数. Bootstrap布局有3列，每个列对应一个组件(过滤器，文本，表格).

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).

简单参数:

- Name: salesrep_selection  
- Property value: mike 

过滤器组件(用于选择销售代表):

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

文本组件:(仅用于检查是否已设置参数):

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

表格组件(按销售代表列出的客户列表):

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

数据源(MySQL查询):

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

启动仪表板时，我会得到带有销售代表姓名的选择列表.当我选择一个时，该ID将显示在文本组件中，但表仍为空表中无可用数据".

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".

如果我对条件"WHERE salesrep='mike'"进行硬编码，则会得到客户列表.

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

使用参数"WHERE salesrep=${salesrep_selection}"，MySQL查询日志向我显示它获取了"WHERE salesrep=null".因此，该参数不会发送到查询.

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.

我还尝试使用[["salesrep_selection","salesrep_selection"]],设置数据源参数，但是表仍然为空表中没有可用数据"，并且MySQL查询日志显示"WHERE salesrep='salesrep_selection'".

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.

Pentaho CE 8.0中处理的参数是否有所不同?查询参数也是吗?

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

推荐答案

您需要将参数从组件传递到数据源.

You need to pass parameter from component to datasource.

1. 将名为salesrep_selection且值为salesrep_selection的参数添加到表组件的属性Parameters中.这将在组件中设置参数，并将其值传递给数据源.
2. 在数据源的属性Parameters中添加相同的参数.这将在数​​据源中设置参数，并将其值传递给查询.

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.

表格组件(按销售代表列出的客户):

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

数据源(MySQL查询):

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

这篇关于Pentaho CE仪表板中的MySQL查询参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！