Drill Down in Same Panel in Pentaho CDE

Drill Down in Same Panel in Pentaho CDE

First create dashboard having two three panels as in example shown below :-

B1

In this example we are going to learn how we can drill down from bar chart in second panel to the table component in third panel with the relevant data.
On clicking any of the bar representing the categories in the bar chart in second panel, the detailed report of that department should open in the third panel in table component.
Here are the instructions to achieve the drill down of bar chart to table component:

1) Set the clickable option = ‘True’ in the properties of bar chart.
2) In the click action of bar chart write the code:

b2

In this code we are passing value from bar chart to the table through parameter ‘department’ i.e on clicking the bar representing different department we will be getting the detailed data of that department in the table.

3) In the component layer add the component ‘simple parameter’ as department:

b3

4) In the properties of table component add listener and parameter department respectively:

b4

b5

5) In the SQL query of table component add parameter in the where clause or in the way you want data to get filter on the basis of parameter used:

b6

6) In same window, add the parameter ‘department’.

b7

7) Save the dashboard and click on the preview. Here you’ll be able to see the changes in table component as you click on different department in bar chart.

Thankyou
Nisha Sahu

Pentaho BA Server 6.0 : How to enable Pentaho Marketplace and Pentaho CDE(CTools) in the Enterprise Edition

Enable Pentaho Marketplace and Pentaho C-Tools in Pentaho BA Server 6.0 EE

Pentaho 6.0 is the first version of the Pentaho enterprise-class server with new features to automate, manage and enhance every stage of the data pipeline. Pentaho 6.0 also introduces inline analytic model editing for sharing user-created measures and metrics in a collaborative manner.

1. Installing Pentaho Marketplace Plugin:

The Pentaho BA Server Enterprise Edition provides us all those things that are limited in Community Edition. However, we can still add the community plugins of the Pentaho server using the Marketplace in Pentaho. The Marketplace is generally disabled is Pentaho 6.0 EE server. There is no need to download to the Marketplace all over again. It’s an OSGi plugin and you already have it, but it comes disabled by default. Here are the instructions on how to enable it:

    • Edit the file
      pentaho-solutions/system/karaf/etc/org.apache.karaf.features.cfg
    • Find the key “featuresBoot” and add the feature “pentaho-marketplace”. That line will then be something like:
      featuresBoot=config,management,kar,cxf,camel,camel-blueprint,camel-stream,pentahoserver,mongolap,pentaho- metaverse,pdi-dataservice-client,pentaho-yarn-obf,pentaho-marketplace

      .

    • Restart the server and its done. The marketplace will be visible. You find it in dropdown of the Home Button. Please see the below screenshot for your reference:

Marketplace

2. Installing Pentaho CDE (Community Dashboards):

In enterprise edition, Pentaho has its own Dashboard but it has limited functionality as we cannot perform the tasks that we can do on the CDE. Therefore, developers always opt for CDE which is available to download from Pentaho Marketplace. If you go into Marketplace, follow the below steps,

      • There you would find CDF(Community Dashboard Framework), CDA(Community Data Access) and CDE(Community Dashboard Editor).
      • Download all of them.
      • Restart the server and it’s done.

Please refer to the below screenshot:

CDE

Thank you,

Nitish Kumar Mishra

Table Expansion in Pentaho CDE

Hello,

we are now working on Table Expansion property of Pentaho CDE. Simple, expanding a Main Table on click and render relevant data in Any Chart / table.

Before stepping into CDE lets start preparing SQL. The below example is developed by using foodmart as Postgres Database. You can see my SQL once you download and import my export in CDE.

Step 1: Prepare your SQL queries for 3 tables.

Step 2: Datasources Panel.

1) Create datasource by giving driver class name, URL and username , Password. Dont forget to name your Datasource.
2) Put your SQL which is prepared in Step 1

Step 3: Layout Panel / Structure

1) Create 2 rows, “MainTable” as row1 and “TableExpansion” as row2.
2) Create 2 columns with in row2 (TableExpansion) , Name column1 & column1 as Table2 and Table3

First let us display main table data and then will continue to look at table expansion.

Step 4: Components Panel

1) Get a table component and provide datasource name and HTML Object.

In this case, row 1 “MainTable” is HTML Object for Main Table in Components Panel.

Believing now you should be able to preview your 1st table data.

Continuing…

Add 2 more table components. These 2 tables shows up the data when you click on “department id” column from Main table.

Be careful with naming convention when you duplicate the table component (provide / change datasource name, htmlobject name and give another name for table component).

Change below values from Advanced Properties of Main Table Component:

  • Expand Container Object = TableExpansion (Row2 name from layout Structure)
  • Expand on Click = true

NO worries, when you preview it seems something odd behaviour like on first view below 2 tables are appearing and when i click on main table the same 2 tables are not showing. Basically those 2 tables should be seen when you click on main table.

Here, we need to apply small CSS code.

.hidden{
display:none;
}

Give this CSS class name on row 2 properties.

Here is the Source Code to download Table Expansion.

Thanks to All.

Using MDX Query in Pentaho CDE

This blog will talk about Using MDX Query in Pentaho CDE

In Layout Section:

Design the layout of your Dashboard, from scratch or from a template – while defining the layout you can apply styles and add HTML elements as text or images.

Pentaho CDE MDX Query

In Component Section:

In side Component layout we are using different types of chart and table or other things. According to component we will provide Data source name and Html Object and some other properties you can also use if you needed.

Pentaho CDE MDX Query

In Data source Panel:

In side data source panel we are we are using driver like jdbc or jndl according to requirement. In My example I am using Jndl data source and Mondrian Schema like Sample data and query like:

Select NON EMPTY {[Measures].[Actual]} ON COLUMNS,

NON EMPTY Crossjoin(Hierarchize(Union(Union(Crossjoin({[Region].[All Regions]},
{[Department].[All Departments]}), Crossjoin({[Region].[All Regions]},
[Department].[All Departments].Children)), Crossjoin([Region].[All Regions].Children,
{[Department].[All Departments]}))), {[Positions].[All Positions]}) ON ROWS

from [Quadrant Analysis]

Note: Below Image you can see the Process.

Pentaho CDE MDX Query

Preview Panel:

After Applying all those thing what I mentioned in above information the output will show like these. This output is same as OLAP creation output in MDX query. You can compare both output.

Pentaho CDE MDX Query

Pentaho CDE MDX Query

Note: When you are start working in MDX Query in Pentaho CDE. You have to first know how to use OLAP Kettle -MDX OLAP. After that you can use that MDX query inside Pentaho CDE (Pentaho Community Dashboard).

Nikhilesh

Helical IT Solutions

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

 

 

Pentaho CDE Basics

Pentaho Community Dashboard(CDE) basics – Creating a Simple Dashboard

Pentaho Community Dashboard Editor (CDE)
What is CDE ?
Where you can get the plug-in in Pentaho Community Server ?
How to download the plug-in & what are the dependencies?
Where you can find the sample examples after installing?
Designing a simple DashboardCTools Website – Community Tools
http://www.webdetails.pt/ctools.html

 

What is CDE ?
CDE is one of the plugins to the Pentaho BI Server, contributed and maintained by Pentaho Partner webdetails.
We create dashboards using this tool.
Community Dashboard Editor (CDE) was born to simplify the creation, edition and rendering processes of the CTools Dashboards.
CDE is a very powerful and complete tool, combining front end with data sources and custom components in a seamless way.

Where you can get the plug-in in Pentaho Community Server ?
How to download the plug-in & what are the dependencies?
You can find the symbol “Pentaho Market place” on the menu bar..
Click on it you can see the Pentaho Marketplace editor as shown in below figure.
Find Community Dashboard Editor and Click on install.
As I have already installed it is showing up Up to Date.
These all plug-ins are updatable so every time it’s better you need to upgrade.
NOTE:
You must install the dependencies plug-ins before you start working with CDE.

The dependencies for CDE are:
CDF(Community Dashboard Framework)
CDA(Community Data Access)
You must restart your server to take the effect of installation.Where you can find the sample examples after installing?
* In the left side panel you can find plugin-samples folder.
* Expand it and then click on CDE
* In the Files you can find sample example CDE Sample Dashboard.
* Right Click on CDE_Sample Dashboard àThen click on Edit.
CDE has 3 major components
They are.
* Layout
* Componets
* Data Sources.CDE has developed based on MVC-2 architecture of Advanced Java Technologies.
* Layout – View
* Components – Controller
* Data sources – Model.
Find the images below to understanding the basic of CDE.

* Scroll down the dashboard. Find “About” on the left side and find the version number of CDE installed.

* Close the version window and Just click on Preview. I’ll let you know how to work out with all the components in CDE with a good working example.
* Now, just click on Preview. You can find the output of the dashboard as shown in below figures.

* Scroll down.

* Read the points that were in boxes. You will get the basic idea of CDE dash boarding.Example:Aim : To design a simple chart(on dashboard)

Environment:
* Pentaho BI Server community 4.8 stable version.
* CDA,CDF & CDE installed in the server from Market place.
Database: SampleData (It comes along with pentaho installation.. for this artical I’m not connecting to any external database. In the upcoming artical you can find working with databases)

* This example was based upon CCC-Version1. The current Example which I’m going to explain     is based upon CCC2-Version2.
* There is a lots of changes made from CCC-V1 to CCC-V2.
* Make sure your environment of CDE supports CCC-V2 for the example.Step 1: Creating New CDE Dashboard
There are two ways to create a new CDE dashboard.
i) From the menu bar
ii) By clicking CDE icon as shown in figure
Save your dashboard
Click on Save -> Save your dashboard in your fav folder with your fav name.

After saving your dashboard  “Refresh your Repository” and then you can find the file as shown below figure.

Step 2:  Working with Layout, Componets and Data sources.
CDE is mainly works on scripting such as css, javascript.
Click on + sign as shown in below figure. (We are going to add Cascading Style Sheet code(css) code to our dashboard).
Resource type : css
External file

Resource file : click on   ^  that is highlighted using arrow in the below image.

·         * Give Styles2(do not give Styles2.css i.e, only give name but not extension).
·         * It automatically cmes with .css extension and with $ and flower braces.
·         * Save the css file externally to a folder as shown in below figure.
·         * To see the Styles2.css appearing in the folder your file must contain some css code.
·         * You must refresh your Repository to see the name of the file(Styles2.css) in the folder.
Giving Title to Dashboard:
·         CDE layout mainly works on Rows and Columns.
·         Click on Add row symbol as shown in below figure.

·         Give name of the Row and give back ground colour as shown in below figure.

·         And give Corners: Round( it is below the back ground , not shown in the image-it is not visible here in the image)

·         Now add Column(find ||) symbol on Layout Structure and click on it.

·         Give all the necessary properties as shown in the below figure.
·         Name: title
·         HTML : <h1> Sales OverView YDT <h1> ( as show in figure at arrow symbol click on button.. You will be prompted to a new window there you have to write this HTML Code)
·         Font Size: 10
v See the preview:
The preview consists of only the title of the dashboard with its layout.
It is further going to be modified.
·*          Add your .css code here…
Css code
Body{
                background-color:#fffdf1;
}
h1{
                font-size:24px;
                color:#fff;
                margin-left:10px;
                margin-top:10px;
}
Now see the preview.

By the time you get some idea on it.

Do as follows, I’m reducing the images now as you might get some idea..
Add another row -> Column->Html  as shown in figure.
For html give Name as title and write HTML code <h3>Sales Performance</h3>  in HTML editor
Add the following code to Styles.css to editor
h3{
    font-size:18px;
                font-weight:bold;
                color:#b68c58;
                margin-left:10px;
                margin-top:10px;
                margin-bottom:8px;
}
.salesTitle{
    color:#666;
                height:2px;
    margin-top:10px;
                margin-left:10px;
                margin-bottom:2px;
                width:939px;
}
Find the images for doing the above and see the preview again

Add another column as shown in below figure

Give the following properties:

Name: sales_chart
Span size: 16
Now working with Data Sources and Query part:

·         Click on Data Sources

·         Find SQL Queries in Left panel.
·         Click on sql over sqlJndi
·         In left panel give Properties
o   Name : get_sales_performance
o   Access Level : public (default)
o   Jndi: SampleData(We are working with pre defined database that comes along with pentaho software to develop this dashboard)
o   Query:
(
    SELECT
        ‘Profit’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Profit’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2003
)
UNION ALL
(
    SELECT
        ‘Cost’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Cost’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2003
)
UNION ALL
(
    SELECT
        ‘Revenue’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE) AS MEASURE
    FROM
        ORDERFACT
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Revenue’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE) AS MEASURE
    FROM
        ORDERFACT
    WHERE
        YEAR_ID = 2003
)
Working with Component
·         Click on Components
·         On the left panel click on Charts
·         In properties give the following
o   Name: sales_chart
o   Datasource: get_sales_performance
o   Width: 610
o   Height:140
o   HtmlObject : sales_chart
o   SeriesInRows : False
·         I will show you how to work with Advanced Properties in the upcoming posts on CDE.
Now save your dashboard and Preview
The preview will look like as follows
That’s it we are done with the Dashboard.
Thank you for reading this post 🙂

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)

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