A requirement make you go in depth of subject... The same happened with me to work out this awesome functionality of Pentaho CDE...
Requirement :
Display aggregate value of some column on Dashboard.
Or
The usage of Query Component in Pentaho CDE.
Environment:
Pentaho BI server stable 4.8
Approach:
Step 1: Lay Out section
Design your dashboard as shown in below picture with rows and columns and with the addition of HTML code.
Step 2: Data sources Panel Section
In the properties :
Name: sampledata_count_value
Jndi: SampleData
Query: select count(customernumber) as count_cust from customers
NOTE: don't go for any Output Options and Columns until and unless if you have any requirement in your dashboard
Step 3: Components Panel Section
As shown in figure , from left side of the panel select " Query Component" and then give the properties
Name: value_of_count ( You can give your favorite name)
Result Var: define a variable of your choice(It's just giving a name)
Next, In the advanced properties of "Query Component" .write the below code in "Post Execution"
Code:
function fun() {
document.getElementById('value').innerHTML = myresult;
}
In the above code:
'value' is the name of the Name of the Column in the lay out section where you want to display the count value.
myresult is the name of the "Result Var" property.
OUTPUT:
Save the dashboard and see the preview.
The sample output is shown below.
122 is the value of the query.. A single value of the result set is displayed using the Query Component and Post Execution code of the component.
Thanks,
Sadakar
BI developer
Requirement :
Display aggregate value of some column on Dashboard.
Or
The usage of Query Component in Pentaho CDE.
Environment:
Pentaho BI server stable 4.8
C-Tools: CDE,CDA,CDF latest version : v13.09.10
Database : Sample data (HSQL db) - installed with pentaho directly. Approach:
Step 1: Lay Out section
Design your dashboard as shown in below picture with rows and columns and with the addition of HTML code.
In the properties :
Name: sampledata_count_value
Jndi: SampleData
Query: select count(customernumber) as count_cust from customers
NOTE: don't go for any Output Options and Columns until and unless if you have any requirement in your dashboard
Step 3: Components Panel Section
As shown in figure , from left side of the panel select " Query Component" and then give the properties
Name: value_of_count ( You can give your favorite name)
Result Var: define a variable of your choice(It's just giving a name)
Next, In the advanced properties of "Query Component" .write the below code in "Post Execution"
Code:
function fun() {
document.getElementById('value').innerHTML = myresult;
}
In the above code:
'value' is the name of the Name of the Column in the lay out section where you want to display the count value.
myresult is the name of the "Result Var" property.
OUTPUT:
Save the dashboard and see the preview.
The sample output is shown below.
Thanks,
Sadakar
BI developer
Hi This is not working in my case, the exact same steps I followed. I am using 8.2 commnuty version Has anything changed in recent updates? Amy clue??
ReplyDeletehi... this is not working in my case,i followed the same steps but it does not.
ReplyDeletei am using v8.1
ReplyDeleteI'm using 8.3, query component works with something like this.
ReplyDeletefunction fun() {
document.getElementById('value').innerHTML = dashboard.getParameterValue('myresult');
}
This comment has been removed by the author.
DeleteNão funcionou estou utilizando a versão 8.3
DeleteNão funciona estou utilizando a verção 8.3
ReplyDeleteMy case also not working. Anyone have idea about this?
ReplyDeleteInsira essa função no seu pos fetch, substitua "resultado" pelo seu Result Var
Deletefunction fun(resultado) {
document.getElementById('mapa_2').innerHTML =resultado.resultset;
}