Pentaho Tools :

Pentaho C-Tools(CDE,CDF,CDA),Pentaho CE & EE Server,OLAP-Cubes,Analysis using Pivot4J, Saiku Analytics, Saiku Reporting, Ad-hoc Reporting using Interactive Reporting Tool,Dashboards,Reports using PRD, PDD,Data Integration using Kettle ETL,Data Mining usign WEKA,Integration of Servers with Databases,Mobile/iPad compatible Dashboards using Bootstrap Css,Drilldown dashboards,Interactive Dashboards

Tuesday, 21 July 2015

Bar chart drill down to another dashboard example in Pentaho CDE

In this post, you will find the quick steps and best practices to develop bar chart drill down to other dashboard.

Soft ware setup for this example
1) Pentaho CE 5.4 BA server
2) Pentaho C-Tools 15.06.30
3) Foodmart database running on PostgreSQL server.
4) IE or Mozilla firefox.

Problem Statement : Drill down bar chart
1) Create 2 dashboards
2) Dashboard 1 :
contains bar chart - In this example lets say quarters(Q1,Q2,Q3 and Q4) on X-axis and UnitSales and StoreSales on Y axis.
3) Dashboard 2 :
A line chart - Months of quarter on X axis and UnitSales and Store Sales on Y axis.
4) Functionality :
When you click on a bar from dashboard 1 the quarter has to be passed as parameter value to the 2nd dashboard.

Steps :
1) Dashboard -1 
a) Go to bar chart click action and write below code. (Don't forget to make clickable = true). :-)

    function f(scene) {
    var vars = scene.vars;
    var s = vars.series.value;
    var c = vars.category.value;
    var v = vars.value.value;
    alert("series: " + s + "\ncategory: " + c + "\nvalue: " + v);  
    

url='http://localhost:8080/pentaho/api/repos/%3Apublic%3ABar%20Chart%20Drill%20down%20Dashboard%20Example%3ADashboard2%20drilldown%20detailed.wcdf/generatedContent';
    window.location =url+'?param_Quarter='+c;
}



b) In above code, c - represents category, the X-axis value, in this example the quarters Q1, Q2, Q3 and Q4.

c) param_Quarter is a string written in code, it would be the parameter(or custom parameter) in 2nd dashboard.

d) Combine URL and 1st parameter with ? (question mark). If you use & you will get error message like "Sorry, we really did it" .

e) When you pass multiple parameter separate parameters using &  but note that URL and 1st parameter should be separated by ?

f) Use alert functions to test the categories,values, series and etc.


Dashboard 2 :

1. Develop dashboard with custom parameters.(In this example , I have created  a custom parameter  that is :
param_Quarter  and the java code (you can say default value(s) expression editor) )
param_Quarter=Dashboards.getQueryParameter("param_Quarter"); 

2. Don't directly test the drill down from 1st dashboard by clicking bar chart.(keep it on hold for some time).

3. View the 2nd dashboard output in browser though you will not get output.  (This is to take the URL)

4. Now, add the custom parameter created to the URL and test dashboard 2 individually, if you get the result then your 2nd dashboard is dynamic with parameters. Importantly, provide listeners and parameters to the components where ever required in your 2nd dashboard.  

Example : 
http://localhost:8080/pentaho/api/repos/%3Apublic%3ABar%20Chart%20Drill%20down%20Dashboard%20Example%3ADashboard2%20drilldown%20detailed.wcdf/generatedContent?param_Quarter=Q1

That's all, you are done.!!! 

Download Example :  Bar chart drill down example 
and have at the sample dashboards.

Note that to run this example you need to have foodmart database running on PostgreSQL database .
(I used jasperserver example foodmart database).

Here are quick snapshots of the output.

Dashboard 1 : 

Dashboard 2 : 

 
Thanks for reading this small article. Hope it helped you a bit : -)

Cheers..!!!