Display Single Value of query result on Pentaho CDE dashboard - Query Component Example in CDE

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

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.

Step 2: Data sources Panel Section

N

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

Tech Tips - Pentaho CDE,CDA empty file & CDA Cache Manger

CDE,CDA issue:

I was wondered while opening .cda file in pentaho Server. I didn't find any scripting.
Generally when we create "Database"connections  while designing the dashboard , the scripting automatically generates in .cda file.  

When I saved as  the CDE with some other name with "Spaces" for example "CDE Dashboard Demo" (there are white spaces among the words) the .cda file also takes the same name with .cda extension.

It'll allow the names with spaces but  it can not contain any  database connections scripting. So avoid giving spaces in Naming the dashboard also when you save as avoid spacing. 

This is an issue in CDA creation.
Thank you pedro for letting me know the tip in C-Tools forum.

Issue link:
http://redmine.webdetails.org/issues/1367

CDA Cache Manager:

In the pentaho browser , go to Tools->CDA Cache Manager

Here you can find Scheduled queries and cached queries.

Sadakar
BI developer