RICH Bar Chart ( Stacked Bar + Moving Average Trend Lines + Line Chart) Example in Pentaho CDE - Submitt Button Component Explained on CDE dashboard

Hi Guys,

Trend Lines: WIKI
# A trend line is formed when a diagonal line can be drawn between two or more price pivot points.

# They are commonly used to judge entry and exit investment timing when trading securities.It can also be referred to as  a dutch line as it was first used in Holland.

# More info at : http://en.wikipedia.org/wiki/Trend_line_%28technical_analysis%29

I have tested 3 kinds of trend lines support with Community Dashboard Editor.They are
i) linear :
ii) moving-average and
iii) weighted-moving-average.

For more info about types refere this : http://www.ehow.com/list_7255661_types-trend-lines.html

Example Developed on :
C-Tools of 14.06.18 version, foodmart database of postgresql(A jasper server sample database which follows star schema model).

Focused on Core part of the Example in this post.


Sample Query:

SELECT
    to_char(t.the_date,'mm-DD-YYYY') Date,
    SUM(sf7.unit_sales) UnitSales,
    SUM(sf7.store_sales) StoreSales,
    SUM(sf7.store_cost) StoreCost
FROM time_by_day t INNER JOIN sales_fact_1997 sf7 ON t.time_id=sf7.time_id
WHERE
    to_char(t.the_date,'YYYY-MM-DD')>='2012-01-01'
    AND
    to_char(t.the_date,'YYYY-MM-DD')<='2012-01-07'
GROUP BY t.the_date
ORDER BY t.the_date


Sample output:

date           unitsales    storesales    storecost
01-01-2012    348.0000    706.3400    280.4990
01-02-2012    635.0000    1304.5300    525.8396
01-03-2012    589.0000    1294.1200    515.2609
01-04-2012    20.0000            42.8700            17.6873
01-05-2012    966.0000    1987.1900    809.6743
01-06-2012    993.0000    2162.3400    864.9502
01-07-2012    1265.0000    2696.6100    1078.2984

Note that column indexes start from 0 and ends with n-1 i.e., here it takes 0,1,2,3
Converting above resultsetinto below points on the chart(RICH BAR CHART)
X-axis : data values
Left Side Y-axis : Stacked bars with UnitSales & StoreCost
Right Side Y-axis : StoreSales Line Chart.
Moving average Trend lines for : Left Side Y-axis measures(i.e., to UnitSales & StoreCost).

On the chart component you need to give below properties.

For Line Chart :
Plot2 : True
Plot2ColorAxis:2
Plot2Series:storesales  (making 3rd column as line chart - i.e, 2nd indexed column)
PlotFrameVisible: false

For Stacked :
stacked :true

For TrendLines of unitsales & storecost:
trendType: moving-average
NOTE : other trendTypes are linear  and weighted-moving-average


(NOTE :
1) I've focused on major properties in this post.. download the example and debugg for other properties set. for instance, extension points and colors.
2) Also, I have not discussed about parameterisation of this example in this post.. you can find how from_date & to_date implemented on example by downloading.
)


How submit button works ?

NOTES:
1. For the 1st time when dashboard loads, all the components on the dashboard should take default values.

2. Next time, when you select other inputs on the dashobard, the components have to stop it's loading for each of the input selection.(the blinking).

3. After selecting new inputs & after clicking "Submit" button only you the components on the dashboard should load.

How one can accomplish this on CDE Dashboards....
1) Let's assume you have two date parameters (of Generic -> Date Parameters).
    param1_FromDate & param2_ToDate

2) Let's say the corresponding Date Picker Selects (of selects -> Date input components).
    select1_FromDate & select2_ToDate

3) Usually we set listeners for these Date selects.. Do not do this (i.e., don't make your Date selects to listen the param1_FromDate & param2_ToDate parameters). But give the Parameters to the Date selects.

4) Let us take two more simple parameters .. Let's say param1 & param2 ( Generic -> Simple parameter).
  
5) Now on your chart component
   Give parameters as : param1_FromDate & param2_ToDate
   Give Listenrs as : param1 and param2

6) Take a button component from Others of Components section and give place holder for it(i.e., htmlObject to keep this button).
   and give Label name as : Submit

7) Write below code in the Expression area of button component.  
   
  Code:
  function f(){
   
    Dashboards.fireChange('param1',param1_FromDate);
    Dashboards.fireChange('param2',param2_ToDate);
   
    alert("param1_FromDate="+param1_FromDate);
    alert("param1_ToDate="+param2_ToDate);
    alert("param1="+param1)
    alert("param2="+param2)
   
 
  }

8) Alerts for testing purpose. In the above code capturing the acual date parameters values into general parameters.

9) 3 & 5 points after this code will work (i.e., when previewing the dashboard)..

Download a sample Example here :

Click Me

Sample output

Sadakar Pochampalli
BI developer.