Hi Guys…!!!
This post helps you how you can make your bar chart dynamic..
You can limit the number of bars on bar charts with out using the “limit” function in your query as well you can also sort the bars in ascending or descending order with out using ORDER by clause in SQL.
Some times you might have to deal with this type of functionality in your dashboard.
So here is a working example. Follow the steps…
Environment:
Pentaho BI server 4.8 stable with C-Tools(CDA,CDE,CDF 13.06) installed.
PostgreSQL
Aim :
1) Creating a dashboard with two horizontal bar charts.
2) How to use single query result set for two charts
3) Order by the bar on the dashboard with out writing ORDER BY clause in SQL.
4) limit the number of bars on charts with out writing “LIMIT” clause in SQL
Suppose your result set is like below
Query Eg :
SELECT ColumnA, columnB,ColumnC from table_Name GROUP BY ColumnA
Result set Assumption:
ColumnA ColumnB ColumnC
pentaho 45 75
jasper 23 34
pdi 90 22
and assume there are 30 rows in the result set …
You are using single query to plot the two charts on dashboard using ColumnA,ColumnB & ColumnA,ColumnC.
When you use ORDER BY clause in the query, you can only either order by columnB or ColumnC but you can not order by with ColumnB and ColumnC.
* In CDE, there is a functionality called “Indexs”. Indexs for columns in pentaho CDE starts from 0,1,2 and etc.
* So ColumnA index is 0, ColumnB index is 1 and vice versa.
* Prepare your environment for the dashboard and have a look at the “Data sources” now.
* Click on “Output Options” and give index values as 0,1,2 by clicking “add” button multiple times.
How to get ColumnA, ColumnB on first chart ? (B’z you are using single query result set)
* Now click on the first chart component
* In the Advanced properties click on “Pre Execution”
* Write the below code to fetch ColumnA, ColumnB on first bar chart.
function f() {
this.chartDefinition.readers = [
{names:’category’, indexes: 0},
{names: ‘value’, indexes: 1},
{indexs: 2}
];
}
NOTE:
ColumnA=category and is accessed with Index 0
ColumnB=value and is accessed with Index 1
index2 is written in the code b’z to ignore the value appending to the category showed on bars.
How to sort and limit the bars on first chart ?(B’z you are using single query result set)
* Click on the “Post Fetch” option from the Advanced properties.
* Write this code, this code will limit the number of bars on chart and sort the bars in descending order.
* In the code below “param_no_of_end_points” is the parameter created in the “Generic” section.
* And for the parameter create a select in the “Selects” section with “Text input” component.
function f1(cdaData) {
var categIndex = 0;
var valueIndex = 1;
var param_no_of_end_points = +Dashboards.getParameterValue(“param_no_of_end_points”);
if(isNaN(param_no_of_end_points))
{
param_no_of_end_points = 0;
}
var resultset = cdaData.resultset.slice();
var compareDesc = function(a, b)
{
return a === b ? 0 : a > b ? 1 : -1;
};
resultset.sort(
function(rowa, rowb)
{
return compareDesc(+rowa[valueIndex],+rowb[valueIndex]);
}
);
if(param_no_of_end_points > 0)
{
resultset.splice(0,resultset.length-param_no_of_end_points,0);
cdaData.resultset = resultset;
}
cdaData = {
metadata: cdaData.metadata,
resultset: resultset
};
return cdaData;
}
Write the same code for the second chart but make sure to use different function names and Index value. In an application we can not write two functions with same name.
Save the the dashboard and see the preview.
Meet us for more solutions @ http://www.helicaltech.com/contact.php
Sadakar
BI developer
Best Open Source Business Intelligence Software Helical Insight is Here
