Adding dollar($) sign to X-axis lables(values) for bar charts in pentaho CDE

Hi Guys,
Using below java script one can easily add ‘$’ sign to the values of X-axis for any charts in pentaho CDE.

Example:
function f(v) { return “$” + sprintf(‘%d’, v/1000) + ‘k’; }

Write the java code in “orthoAxisTickFormatter” java script wizard.

Make sure to give orthoAxisTicks as “True”
Sample output:


NOTE:
This code works with 13.06 as well with 13.09 version of pentaho CDE.

Also note that in java script if one line is not executing then the remaining lines will not execute. i.e., for instance you have 10 lines of code and 4th line is not executed then the remaining lines will not execute. Check with an alert function.

Sadakar Pochampalli
BI developer

 

Bar chart with Target lines in pentaho CDE – making bars as lines using java script in pentaho CDE

Hi guys,
This post teach you how to make bars as lines in pentaho CDE.
Source for this post is : http://jsfiddle.net/duarteleao/7maGD/
and http://type-exit.org/adventures-with-open-source-bi/2011/06/creating-dashboards-with-cde/
Scenario : Some end users wants visualization of grouped bars as lines where one of the bar is as bar and remaining bars as targeted lines on the same bar.
Properties have to give:

Plot2 : True

Find the remaining properties in below image: version of CDE is : 13.06 but will work in higher versions also.

 

In “Extension points” for the chart component you need to give 3 properties from the first link.

They are

extensionPoints: {
plot2Dot_shape: ‘bar’,
plot2Dot_shapeSize: function() {
var diam = this.chart.plotPanels.bar.barWidth ||
this.chart.options.barSizeMax;

return this.finished(diam);
},
plot2Dot_shapeAngle: function() {
return this.chart.isOrientationHorizontal() ? 0 : -Math.PI/2;
}

NOTE: Find the image for how to give the above code as properties in Extension points of chart  component.

 Sample output of the chart on dashboard after giving the above properties.

Sadakar
BI developer
(“Learning never exhausts the mind”)

 

 

Reading data from single data source(Single SQL Query) for two charts in pentaho CDE

Hi guys…!!!

Community Dashboard Editor is the best reporting/dash boarding  tool that I have worked. It’s smart functionality made me love to work with it and exploring the things time by time.

In this post you’ll learn how to fetch different columns from result set of a single query and use them in different analysis purpose in dash boarding.

Recently I needed to work with a single query data source(SQL) of having 3 columns result set …
Lets say there are 3 columns A,B and C where as A column is having some category names and B and C are having some values

A  B   C
————–
abc 2   4
pqr  6   8
xyz 10 5
and etc.

From the result set A&B are on first chart and A&C are on other chart..
Now how ?????? This question leads me to check the “Data sources” section of CDE.

Follow the steps below.

1) Click on the “sqloverjndi” which  you created for your SQL query.
2)  In the properties section you can find an option called “Output options”. Just click on  it.
3) Let’s say you have 3 columns in your result set and these 3 columns takes index values starting from 0 to n.. i.e., A column index is 0 , B column index is 1 and for the C  value 2 is the index.

  NOTE: if you have more numbers of columns you can give as many indexes by clicking “Add” button as many times.
4)For Chart 1:
i) Click on the chart component where you want show A& B columns ( A is category B is value)
ii) In the properties(Click on Advanced properties) click on “Pre Execution”
iii) Write this below code
function f() {
this.chartDefinition.readers = [
{names:’category’, indexes: 0},
{names: ‘value’, indexes: 1},
{indexs: 2}
];
}
Why this {indexs:2} ? if you omit this the values of index2 append to the category names… to eliminate that problem you need to write it.

 5) For Chart2 :
Repeat the steps in in point 4)
Slight changes in code …

function f() {
this.chartDefinition.readers = [
{names:’category’, indexes: 0},
{names: ‘value’, indexes: 2},
{indexs: 1}
];
}

You are done with reading data from single query of 3 columns where 1&2 for one chart and 1&3 for another chart…

Save your dashboard and see the preview.

Sadakar
BI developer
( “Learning never exhausts the mind” )

Extract year,quarter,month & day from date input control in pentaho CDE using java script – MDX Query Scenario

Hello guys…!!

Some times you need to extract the parts(year,month,day) of Date for some specific use..
For example:
Assume you are creating a report with MDX query which has dimension called “Date” having levels “Year”, “Quarter”,”Month” & “Day”.

(Note : Assume your schema is having
Year: yyyy Quarter: 1 or 2 or 3 or 4 Month : 1,2,3,4…….12  Day : 1,2,3…. 31)

Also assume you do not have direct date dimension in your schema (i.e, you do not have a dimension which takes ‘yyyy-MMM-dd’ column.

But, you need to display date(yyyy-QQ-MMM-dd) or (yyyy-MMM-dd) on X-axis of chart.. Remember you are not having any direct date in your schema but have “Date” with year,quarter,month & day as levels.

From your start_date(or end_date) input control you can extract the individuals using the following java script for CDE and use them properly in your MDX date range place.

This should be done in “Pre Execution” section of Chart component

function extract_function(){

tmp_date = new Date(param_start_date);
var quarter = [‘Q1′,’Q2′,’Q3′,’Q4’];
var month = [‘JAN’,’FEB’,’MAR’,’APR’,’MAY’,’JUN’,’JUL’,’AUG’,’SEP’,’OCT’,’NOV’,’DEC’];

param_start_year = tmp_date.getFullYear();
param_start_quarter = q[Math.floor((tmp_date.getMonth()+ 3) / 3)-1];
param_start_month = m[tmp_date.getMonth()];
param_start_day = tmp_date.getDate();

tmp_date = new Date(param_end_date);
param_end_year = tmp_date.getFullYear();
param_end_quarter = q[Math.floor((tmp_date.getMonth()+ 3) / 3)-1];
param_end_month = m[tmp_date.getMonth()];
param_end_day = tmp_date.getDate();

}

NOTE: 
* quarter and month variables are taken as arrays with default values.
* You need to calculate the month and send it to array ..
When you calculate months
1 becomes JAN, 2 becomes FEB and etc as well
When you calculate quarters
1 becomes Q1, 2 becomes Q2, 3 becomes Q3 & 4 becomes Q4

Forget about your problems …!!!! and Meet us @   http://www.helicaltech.com/contact.php

Sadakar

BI developer

 

Sorting and limit the bars on bar charts in Pentaho CDE without ORDER BY and limit functions in SQL Query ( Descending order)

Link

Hi Guys…!!!
This post tech 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

 

Tech Tips in Penaho Server – publishing password setting for server – Disable the folder names in Browser panel in the Server

Hi guys..

The below tips are very useful in Pentaho Server for BI developers.
1) publishing password setting for server
You need to publish the reports or schema(s)[developed in PSW] once you done with developing. For this you need set the publishing password for Pentaho server.
Go to this location 
<location of Pentaho>biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions\system\publisher-config.xml and edit the “publisher-config.xml”  file.

Give your publish password b/w  these tags  <publisher-password> </publisher-password>

Example:
<publisher-password>password</publisher-password>    ( password can be any name that you wish to give)

Snapshot is a worth more than text. find the image below.

Save the file. stop the server and start the server.

2) Disable the folder names in Browser panel of Pentaho Server
* What ever the folder you create in Browser panel of pentaho server, it directly stores   under pentaho-soulutions
i.,e in this location
<Pentaho location>Pentaho\biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions
* You can disable all of your folders that comes with installation & the later created ones  which you don’t want to appear in the browser panel.
*   Inside every folder(pentaho default or the folders created by you) you can find “index.xml” file. You need to edit this file. You need to change visable tag as “false”.
Find the sanpshot.

You need not restart the server. Just you need to refresh the browser panel in the server.
You can observer the invisibility of the folder.

Sadakar Pochampalli.

 

 

Date Input Controls/ Components in Pentaho CDE with a working example

Hello guys…!!!

In this post you will learn about how to create date input controls in CDE.
The dashboard images which I am going to show below are having lots of .css code. I’m not covering all the things but concentrating only on Date input controls in CDE.

My Environment :
BI Server: Pentaho 4.5
CTools: Not found the version numbers of  C-Tools(Unable to check them)– perhaps..! lesser than 13
Database : hsqldb (Comes along with Pentaho installation — A java based small db for servers like pentaho)
NOTE: Images are taken in this post after creating the dashboard, please find the yellow rectangular boxes to understand.

You need to work out @ 3 places in “Components” section.
1. Generic
2. Selects
3. Charts
Explained from step 1 to step 3
You need to work out  @ 1 places in “Data Sources” section. i.e, all in “Properties”
I’m not concentrating on Layout part for this example. So wherever the HtmlObject you find in this example replace them with your working HtmlObjects.

Step 1: Creating parameters
* Design your dashboard as per your requirement ( In the images you can find the sample designs).
 In the “Components” section:
1. Click on components
2. Click on Generic
3. Click on Date parameter
4. Go to properties give the name
eg: param_start_date
5. Click + symbol  per another date parameter
6. Go to properties give the name
eg: param_end_date

Find the  image

Step 2: Creating Date input Components
In the “Components” section
1. Click on Components
2. Click on Selects
3. Click on “Date input Component”
4. Go to Properties give name. eg: start_date in  my example
5. In the properties give HtmlObject. eg: start_date_select
* HtmlObject is the name of the column where you are putting
your dateinput control.
6. Give  Parameter.[It will appear once you try to type]
eg: In step 1, created parameters. So here give “param_start_date”
7. Give Listeners[click on it, you will find the list of Listeners. Click OK]
8. Repeat 3-7 for “end_date” Date input
component.

Step 3:
Find the steps in image.

 
HtmlObject is the place where you are putting your chart.

Step 4: Applying parameters for Chart & Query
            In the “DataSources”
Find the steps in the image
Find the sub sequent image for  Parameters applying for chart & Query.

do same as shown in images.

Step 5:
Save the dashboard and then Click on “Preview” button.
See the images below for sample out put with date input controls.
Preview of the dashboard with out any input controls selection

Preview of the dashboard after selecting date input controls.. find the images below one by one.

Find the image for observing whether the chart becomes dynamic or not with different date input controls.

That’s it.. You are done with date input controls on dashboard.
Thank you for reading this small post.

Sadakar
[email protected]
Helical IT Solutions Pvt. Ltd,
Hyderabad.