Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Pentaho BI Server - CDE variable in SQL question

  1. #1
    Join Date
    Oct 2016

    Default Pentaho BI Server - CDE variable in SQL question


    Im using CDE in Pentaho Community version in which I have created
    a simple dashboard which contains a CCC Pie Chart. My datasource is Postgresql 9.3
    which works well as a data source.

    My CCC Pie chart is called myPie1 and I have set clickable to true and have
    created a click action which seems to work.

    function(s, c, v){
      var chart1 = render_myPie1.chart;
      var currTitle = new String( chart1.options.title );
      var timeVal = new String( "None" );
      var varDays = Number("1") ;
         case currTitle.indexOf("Day") != -1 :
         timeVal = "Week" ;   
         varDays = Number("7");
         case currTitle.indexOf("Week") != -1 :
         timeVal = "Month" ;   
         varDays = Number("31");
         case currTitle.indexOf("Month") != -1 :
         timeVal = "Year" ;   
         varDays = Number("365");
         case currTitle.indexOf("Year") != -1 :
         timeVal = "Day" ;  
         varDays = Number("1");
         timeVal = "Day" ;    
         varDays = Number("1");
      chart1.options.title = "(Clickable) ASB Apc Camera Totals - Last : " + timeVal ;
      Dashboards.fireChange('gVarDays', varDays);
        // alert("days "+ Dashboards.getParameterValue("gVarDays"));
      chart1.render(/* bypassAnimation */true, /* recreate */true); 

    I have also created a parameter variable in the data source ( which is sql over jdbc ) and
    in the pie chart called gVarDays. In my data source I have some sql as follows

      source::text as source,
      sum(sum_in_0::integer)::integer as  sum_in
      source like '%ASB%' and
      to_timestamp(date_val||' '||time_val,'YYYY-MM-DD HH24:MI:SS') > 
          (current_timestamp - interval '1  days')
    group by 
    which selects totals to display in the pie chart. What I want the pie chat to do is change
    it's totals value by clicking i.e. day. week, month, year, day. In order to do that I need
    the sql above to accept a variable. i.e.

      to_timestamp(date_val||' '||time_val,'YYYY-MM-DD HH24:MI:SS') > 
          (current_timestamp - interval '${gVarDays}  days')
    I know that the dashboard variable gVarDays is set because I have working alerts that
    display the value when I click the pie chart. However, I cant make the variable work in the
    sql of the data source.

    I have seen people mention chart based listeners but if I select the listeners option in the
    pie chart I see that the only option is "Select All" and this option is already set.

    If I use this variable in the data source then the pie chart does not display and the
    rendered template contains the error "Error processing component (myPie1)".

    I would appreciate any advice that can be given .. thanks

  2. #2


    Try something like:

      to_timestamp(date_val||' '||time_val,'YYYY-MM-DD HH24:MI:SS') > 
          (current_timestamp - ${gVarDays}::INTEGER * '1 day'::INTERVAL)

    Because the variable doesn't work fine inside single quotes.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.