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

Thursday, 2 January 2014

Drill down from one dashboard to another dashboard - custom parameter example in pentaho CDE

This post teach  you how to drill down from one dashboard to another dashboard by passing parameters.

Example developed on :
1) BA server - 4.8 version
2) C-Tools 13.09 version
3) Web browser : Mozilla firefox(Always preferable for pentaho C-Tools)
4) Database : foodmart (postgreSQL - jasper server default db).

Before learning this post I suggest you to have a glance at the previous post on the same drill down with out any parameters concept. 

Aim of the concept :
1) Click on state slice in dashboard 1 
2) As soon as you click on particular state slice, you will be navigating to another dashboard displaying all the details of that state in another dashboard in the form a table. 

i.e, Drill down from Pie Chart to Tabular display of data by passing parameter. 

Dashboard 1 : 
Create your chart(s) as shown in below figure you 

1) Create a Simple parameter let's say param_custom_state ( No need of giving any default value )
2) Go to chart component properties 
     -> Give the data source(query created in data source section) that will fit for the above chart .
     -> Give the above created parameter for "Listeners" as well for "Parameters".
3)  In the clickAction write below code

function sendParameter(scene){
       var url='http://localhost:8085/pentaho/content/pentaho-cdf-dd/Render?solution=CDEExploring&path=%2FTest&file=test_dev_drill.wcdf';
       var vars = scene.vars;
       var c = vars.category.value;
       var v = vars.value.value;
       alert("category: " + c + "\nvalue: " + v);

NOTE : also look at this thread for URL passing.

1) URL = 2nd dashboard URL   
2) url+parameter(which you are passing)
3) On pie chart, slice will take the category value( here the category value is "state" and represented by c)
4) This "state" will be the custom parameter for the 2nd dashboard. 
5) Give alert function whether the code is working fine or not (This is a testing methodology).
6) Note that drill down code slightly vary for other charts in pentaho CDE b'z of protovis scripting changes as well as per new things in CCC2.(i.e., For bar chart drill down the given code may be differ from the code written here).

5) Save the dashboard  and see the preview.. Once you click on slice it'll alert saying that the selected state along with the value and then you can see the drill down to another dashboard. 

Dashboard 2:

NOTE that you have to  create 2nd dashboard separately and have to know how to get the URL of it.(Open In New Window)  
Create a table component and suitable query for it where your query for the tabular display should take the input parameter "state" which is your custom parameter and on the table component give it as parameter also.
For instance : in where clause of your query .
   s.store_state like ${param_custom_state}

1) Go to "Generic" section and click on custom parameter
2) Give the name for it (it is not mandatory to give the same name but preferable to give the same parameter name that you are receiving from 1st dashboard).
3) In the Java script code write below code
param_custom_state = Dashboards.getQueryParameter("param_custom_state");

Once you click on WA state in the above image , you will navigate to another dashboard which displays that particular state details. Sample image is shown below.

 This is the way how you can work custom parameter for drill down functionality. 

For tricks and tips navigate through my blog post and discover some thing interest in pentaho..