Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Pentaho BI Server 6.1.0.1.196 - CDE variable in SQL question

  1. #1
    Join Date
    Oct 2016
    Posts
    2

    Default Pentaho BI Server 6.1.0.1.196 - CDE variable in SQL question

    Hi

    Im using CDE in Pentaho Community version 6.1.0.1.196 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.

    Code:
    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") ;
      
      switch(true)
      {
         case currTitle.indexOf("Day") != -1 :
         timeVal = "Week" ;   
         varDays = Number("7");
         break;
         
         case currTitle.indexOf("Week") != -1 :
         timeVal = "Month" ;   
         varDays = Number("31");
         break;
         
         case currTitle.indexOf("Month") != -1 :
         timeVal = "Year" ;   
         varDays = Number("365");
         break;
         
         case currTitle.indexOf("Year") != -1 :
         timeVal = "Day" ;  
         varDays = Number("1");
         break;
         
         default:
         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

    Code:
    select
      source::text as source,
      sum(sum_in_0::integer)::integer as  sum_in
    from
      apccamera
    where
      source like '%ASB%' and
      to_timestamp(date_val||' '||time_val,'YYYY-MM-DD HH24:MI:SS') > 
          (current_timestamp - interval '1  days')
    group by 
      source
    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.

    Code:
      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

    Default

    Try something like:

    Code:
      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.